Using Connection Objects ado.net tutorials

Tags: VB.NET, VB 2008, VB 2010, VB 2012, VB 2013

This lesson describes how to use ADO.NET Connection

You typically interact with your database using connection objects, command objects, and data reader objects.

In order to do anything with a data, you need to establish a session with the database with an object called a connection. To connect to a specific data source, you use this data connection object which also provides access to a related transaction object.

First of all you will need to write a very simple program just to open and check a connection.

    Try
      connection.Open()
      If connection.State = ConnectionState.Open Then
      ' ToDo: something
      End If
      '...
  

When you are finished with the database, you should call Close() to terminate the session, because database sessions are relatively expensive as they use resources on the client and the server side.

    ' prefered method of closing any open connection
    connection.Close()
  

TIP: Too many open connections slow a server down and prevent new connections to be made. So, leaving open connections when they are no longer needed is a bad practice.

ADO.NET comes with numerous data providers that allow you to communicate with a particular database management system so you have numerous connection objects to choose among:

  • SqlConnection
  • OracleConnection
  • OleDbConnection
  • OdbcConnection
  • etc.

You usually store the connection as shared function in a separate class:

Examples:

    Public Shared Function SqlServerConnection() As SqlConnection
      Return New SqlConnection("Data Source=SqlServerNameOrIP;Initial Catalog=DBName;Integrated Security=True");
    End Function
  
    Public Shared Function OracleConnection() As OracleConnection
      Return New OracleConnection("Data Source=MyOracleDB;User Id=myUsername;Password=myPassword;");
    End Function
  
    Public Shared Function MsAccessConnection() As OleDbConnection
      Return New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=DBName.mdb;User Id=admin; Password=;");
    End Function
  

Usage:

    Dim connection As SqlConnection = MyClass.SqlServerConnection
  

Note: Regardless of the fact that each connection object has a unique name, all connection objects implement a common interface named IDbConnection which defines a set of members that are common to all ADO.NET connection objects so you are guaranteed that every connection object supports members like Open(), Close(), CreateCommand(), etc.


Share This

Home | About | Contact | Privacy Policy

Copyright visual-basic-tutorials.com 2017 - All Rights Reserved.