Establishing a Connection to a Data Store

The Connection class provides two constructors, allowing you to create an instance of the Connection class in two ways. The first constructor takes no parameters. In this case, you’ll need to define the connection string separately, by specifying it in the ConnectionString property, as shown in the following code. The connection string includes the source database names and other parameters to establish the connection. The default value is an empty string.

SqlConnection conn = new SqlConnection();
string sConnString = "data source=(local);initial catalog=pubs;" +
                     "user id=sa;password=;";
conn.ConnectionString = sConnString;

The second constructor will accept the connection string as its parameter, as shown here:

string sConnString = "data source=(local);initial catalog=pubs;" +
                     "user id=sa;password=;";
SqlConnection conn = new SqlConnection(sConnString);

The first step in accessing the data store to retrieve data is to establish a connection to the database. After you instantiate a Connection object, you pass it the connection string that defines the data store to which you’ll be connecting as a part of the initialization. You open the connection using the Open method of the object, as shown here:

string sConnString = "data source=(local);initial catalog=pubs;" +
                     "user id=sa;password=;";
SqlConnection conn = new SqlConnection(sConnString);
conn.Open();

Alternatively, you could instantiate the Connection object without the connection string and then add the connection string to the ConnectionString property of the object, as shown here:

string sConnString = "data source=(local);initial catalog=pubs;" +
                     "user id=sa;password=;";
SqlConnection conn = new SqlConnection();
conn.ConnectionString = sConnString;
conn.Open();

To provide greater scalability in building data access components, ADO.NET data providers support connection pooling to the database. Connection pooling means that connections to the database are held and managed by ADO.NET. When a connection is opened, it will be retrieved from the connection pool, if connections are available, or a new connection will be opened, as shown in Figure 18-1.

Figure 18-1.

The connection pooling process.

As you can see in Figure 18-1, when a connection is opened to the data store, ADO.NET checks the connection string. It then looks to see whether there are any idle connections to the same data store with the identical connection string. If this is the first time a connection is being made to the data store with this connection string, a connection pool will be created and multiple database connections will be created to meet the minimum size requirements for the connection pool.

If the connection pool already exists and an idle connection is available, that connection will be used. If no idle connections are available and the pool hasn’t yet reached the maximum number of connections, another connection will be created and used. This new connection will be added to the pool when it’s released. If the connection pool has reached its maximum size, the connection request will be queued and serviced when a connection becomes available.

A connection is released and returned to the connection pool when the Close method is called. You’ll learn how to close a connection later in this chapter, in the section “Closing the Connection.”

You can control the way connection pools are created and maintained by passing parameters as a part of the connection string. By default, the SQL Server .NET Data Provider uses an implicit pooling model to provide connection pooling. The connection pool parameters for the ConnectionString property of the SQLConnection object are listed in Table 18-2.

For example, the following connection strings will result in two separate connection pools being created because the two connection strings are not identical:

string sConnString = "data source=(local);initial catalog=pubs;" +
                     "user id=pubAdmin;password=AdminPassword;";
string sConnString = "data source=(local);initial catalog=pubs;" +
                     "user id=pubUser;password=mypassword;";

In this case, the connection strings make use of two different users.

By default, the OLE DB .NET Data Provider makes use of OLE DB session pooling to provide data access scalability. More details are provided on OLE DB session pooling in the OLE DB Programmer’s Reference in the Microsoft Developer Network (MSDN) Library (http://msdn.microsoft.com/library).

The Connection object is also used to initiate transactions within the data store. These transactions are then managed and ended using the appropriate Transaction class (either SqlTransaction or OleDbTransaction). The Connection object initiates the transaction by calling the BeginTransaction method, as shown in the following code. This method returns the transaction object that will be used to commit or abort the completed transaction.

SqlTransaction trans = conn.BeginTransaction();

If you’re using the SQL Server .NET Data Provider, the transaction will make use of SQL Server’s BEGIN TRANSACTION implementation in Transact-SQL. If you’re using the OLE DB .NET Data Provider, the OLE DB layer manages the transaction.

The BeginTransaction method is overloaded so that it can be initialized in several ways, depending on which data provider you’re using. With both the SQL Server and the OLE DB data providers, you can initialize the object with no parameters or pass a single isolation level parameter. The transaction IsolationLevel enumeration values are listed in Table 18-3.

With the SQL Server .NET Data Provider, you can also provide a name for the transaction as a string, specify the isolation level of the transaction, or both in the BeginTransaction method, as shown here:

trans = conn.BeginTransaction(IsolationLevel.ReadCommitted,
    "MyTransaction");

The BeginTransaction method returns a transaction object that will be used to manage and complete the transaction. Once the transaction is initiated, you’ll need to either commit or roll back the transaction.

In practice, you’ll typically provide a routine that will execute one or more SQL commands as part of the transaction, as shown in the code below. If an error is raised by the database, the transaction will be rolled back and no changes will be recorded. However, if no error is reported, the transaction can be committed and the updates to the data will take place.

//Create a connection and open the connection to the database.
SqlConnection conn = new SqlConnection(sConnString);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();

try
{
    //Execute SQL commands or other database transactions.
    
    

    trans.Commit();
}
catch(Exception e)
{
    //Error handling, logging, reporting, and so on
    
    

    trans.Rollback();
}
finally
{
    //Always close the connection!
    conn.Close();
}

As mentioned, when you’ve retrieved all the data you need, you should immediately release the connection to the database. A general rule of thumb is to open the connection as late as possible and close it as early as possible. Two methods are provided to release the connection.

• Close

• Dispose

The Close method, shown in the following code, is the recommended way to release the connection. Close closes the database connection and returns the connection to the connection pool so that it can be reused by the application.

SqlConnection conn = new SqlConnection(sConnString);
conn.Open();
//Do some work here.
conn.Close();

The Dispose method, shown in the following code, releases all the resources associated with the connection. Dispose releases all the unmanaged resources that are being used by the connection, and optionally it can release the managed resources.

SqlConnection conn = new SqlConnection(sConnString);
conn.Open();
//Do some work here.
conn.Dispose();