ADO.NET Database Connecting in VB.NET

In this article I will explain about Connecting to the Database in ADO.NET.
  • 3379

As you're seen in figure 5-1, the first data provider that interacts with a data source is the Connection object. You can bypass a Connection object by using a DataProvider directly, but in that case a DataProvider uses the connection internally. (I'll discuss the DataAdapter later in this article.) The Connection class has a different name depending upon the Data Provider. As you can see from figure 5-21, the connection class for OleDb, Sql, and ODBC are OleDbConnection, SqlConnection, OdbcConnection, respectively. All data provider connection classes implement the IDbConnection interace, which represents a unique session with a data source (see figure 5-21).

IDbconnection.gif

Figure 5-21. The IDb connection and its derived classes

The Connection class has a connection string that opens a connection to the database. The connection string will vary depending upon the provider used. The connection strings typically contain a group of property-value pair to describe how to connect to a database. For an OleDbConnection, you have properties such as Provider and DataSource. Table 5-16 describes the Connection class properties. Based on the data provider, some of these properties may not be applicable.

Table 5-16. Connection object properties

PROPERTY  

DESCRIPTION

ConnectionString                           

Represent the connection string.

ConnectionTimeOut                      

Waiting time while establishing a connection.

DataBase                                        

Name of the current database.

DataSource                                    

Location of the file name of the data source.

Provider

Name of the OLE DB provider. This property is not available for Sql and ODBC data providers.

State Current state of the connection of type ConnectionState. (Table 5-17 describes the ConnectionState).

PacketSize                                                  

Size of network packets. Available to only Sql data providers.

ServerVersion                                               

SQL server version. Available to only Sql data providers.

WorkStationId                                      

Database client ID. Available to only Sql data providers.


The connection can have different states such as open, closed, connecting, and so on. The ConnectionType enumeration defines the members of the ConnectionState. Table 5-17 describes its members.

Table 5-17. The ConnectionType Enumeration members
 

MEMBER 

DESCRIPTION

Broken

Connection is broken after it was opened. May cause by network failure.

Closed

Connection is closed.

Connecting

Opening a new connection.

Executing

The connection is executing a command.

Fetching

Retrieving data from a data source.

Open

Connection is open and ready to use.


Table 5-18 describes the Connection object methods. You'll see some of these methods throughout this article. The purpose of this table is to give you an idea of available methods.

Table 5-18 the connection Class Members
 

METHOD 

DESCRIPTION

BeginTransaction                                          

Begins database transaction.

ChangeDatabase                                        

Changes databases for an open connection.

Close

Closes an opened connection.

CreateCommand Creates and return a Command object depends on the data providers. For example, OleDb Connection returns OleDbCommand, and SqlConnection returns SqlCommand.

Open

Open a new connection.

ReleaseObjectPool                                            

Represents that the connection pooling can be cleared when the provider is released. Available only for Ole Db data providers.


Opening and Closing a Connection

All the providers construct their connections in the same way. The thing that makes the connection construction different between the different providers is the Connectionstring. For example, the SqlClient doesn't need to specify a provider string because Sql Server is always the provider when using this class. Listing 5-18 shows how to create a Connection object using different constructors.

Listing 5-18. Creating a Connection object using different constructors

        Dim conn1 As New OleDbConnection()

        ' Create a connection object
        Dim ConnectionString As String = "provider =Microsoft.Jet.OLEDB.4.0;" & " Data source= c:\ Northwind.mdb"
        Dim conn2 As New OleDbConnection(ConnectionString)

Note: Before using data providers, you must include a namespace in your project. For example using System.Data.OleDb and using System.Data.SqlClient will add references of OleDb and Sql data providers to your class.

As you can see from listing 5-18, I created two SqlConnection objects: conn1 and conn2. I created conn1 with no connection string. If you create a connection object with no connection string, you will have to set its ConnectionString property before you call Open of the connection object. I created the conn2 object with a connection string as an argument. As you can see from the connection string, it consists of a provider name and data source.

After creating a connection object, you call its Open method to open a connection. The open method doesn't take any arguments. The following line of code opens a connection:

conn.Open()

When you're done with the connection, you call its Close method to release the connection. The Close method also doesn't take any arguments. The following line code closes a connection:

conn.Close()

Listing 5-19 opens a connection with the Access 2000 Northwind database that resides in the C:\dir. After opening the connection, you call its properties, and at the end you call the Close method to close the connection. As you can see from the code, you check the connection state to see if the connection is already opened (which is impossible in this code), closed or not.

Listing 5-19. Opening and closing an OleDbConnection


        ' open the connection
        If conn.State <> ConnectionState.Open Then
            conn.Open()
        End If

        ' show the connection properties
        MessageBox.Show((((("connection string : " & conn.ConnectionString & ", DataSource :") + conn.DataSource.ToString() & ", Provider : ") + conn.Provider.ToString() & ", ") + conn.ServerVersion.ToString() & ", ") + conn.ConnectionTimeout.ToString())

        ' close the connection
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If

The output of program listed in Listing 5-19 looks like figure 5-22.

Figure-5.22.gif

Figure 5-22. Output of listing 5-19

You can also call the Dispose method to dispose the connection. The Dispose method tells the garbage collector to free and destroy the connection reference.

You can also use the OleDb data provider to connect to SQL server or other databases if you have an OLE DB data provider installed for that database. Listing 5-20 shows the connection of a SQL server using the OleDb data provider.

TIP: You can also use oleDb data providers to connect to a SQL server database. The database. The following code snippet shows you a connection with a SQL server 2000 database using the OleDb data provider. You may need to access a SQL server from OleDb data provider when you're writing a generic class that can access multiple OLE DB data sources.

Listing 5-20. Connection to SQL Server using the OleDb data provider


        ' create a connection object
        Dim ConnectionString As String = "Provider= SQLOLEDB.1; " & "Integrated Security = SSPI; " & "Persist Security Info = false:" & "Initial Catelog = Northwind; " & " Data Source = G61LS; "
        Dim conn As New OleDbConnection(ConnectionString)

Conclusion

Hope this article would have helped you in understanding Connecting to the Database in ADO.NET. See my other articles on the website on ADO.NET.

Categories

More Articles

© 2020 DotNetHeaven. All rights reserved.