Connecting to the Database in ADO.NET

This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".

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).



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

OleDbConnection conn1 = new OleDbConnection();

// Create a connection object
string ConnectionString = "provider =Microsoft.Jet.OLEDB.4.0;" + " Data source= c:\\ Northwind.mdb";
OleDbConnection conn2 = 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)
conn.Open();

// 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)
conn.Close();

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



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
string ConnectionString = "Provider= SQLOLEDB.1; "+ "Integrated Security = SSPI; " + "Persist Security Info = false:" + "Initial Catelog = Northwind; " + " Data Source = G61LS; ";
OleDbConnection conn = 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.

This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET.