ADO.Net connection pooling

Connections are precious commodities, and writing code to minimize the stress on the server of having too many connections open concurrently will help with overall database performance. Fortunately, ADO.NET (like its predecessors) tries to help manage those connections with a facility called Connection Pooling. Connection Pooling is the process of managing connections as shared resources that can be doled out from a pool of recently used connections. Connection pooling takes advantage of the fact that many different parts of most applications require connections for a short amount of time as well as the fact that building and tearing down connections is an inherently expensive operation. Connection pooling is a method of reusing connections. The real magic occurs when connections are closed, because the pool hangs on to the connection for some short time (the pooling timeout) before actually closing the connection. If another connection is requested before that short amount of time has elapsed, it hands the open connection to the requestor. This saves the actual work of tearing down the connection and opening a new one. By utilizing connection pooling, you reduce the likelihood of making a round trip to the database only to find out that the database is out of connections. The connection pool reduces the time it takes to determine the out-of-connections state. In fact, with the connection pool, the additional requests can block to wait for a new connection to be available. This allows a machine to throttle its actual usage of the database so as not to swamp a particular database server with requests.

Each of the managed providers handles connection pooling differently. Although connection pooling is mostly transparent to the database developer, understanding how the different pooling mechanisms work allows you to write code that will take advantage of the connection pooling.

2.2.4.1 SQL Server Managed Provider's Connection Pooling

The SQL Server Managed Provider creates a pool of connections that have identical connection strings—these connection strings must be byte-for-byte identical. The managed provider simply matches identical connection strings in the pool. It uses the connection strings that a connection has after it has been set, not the connection strings you set. For example, if I create a connection with the connection string of "Server=localhost;Database=ADONET;", after I set it the connection will have all of the defaults in the connection string, including security information (unless you have specified not to persist it). What this means is that if you are trying to pool connections and you are using integrated security, your connections will not pool because the security information in the connection string will be different from that in the pool (because the different users each will have their own security identity and credentials embedded into the connection string, so they can't be identical to another user's connection string).

SQL Server's Managed Provider implements the pooling facilities down in the bowels of the System.Data namespace.^[4] If you want to watch the pooling, use the SQL Server Profiler to see the connections created and destroyed. The SQL Server Managed Provider gives us limited influence over how the pooling works. It exposes the pool settings through the connection string.

^[4] Note: The SQL Server Managed Provider's connection pooling is not enabled while running under Visual Studio .NET's debugger.

2.2.4.2 OLE DB Managed Provider's Connection Pooling

The OLE DB Managed Provider handles connection pooling much differently than the SQL Server Managed Provider. With the OleDbConnection class, the underlying OLE DB provider (not the ADO.NET managed provider) handles the connection pooling. This process is transparent to the ADO.NET developer with the exception of the OleDbConnection.ReleaseObjectPool() static method, which alerts the underlying provider that your code will not be using data access for some period of time and that it can destroy the object pool after all the connections are returned. This helps OLE DB shut down more effectively. Because the pool and the connections have a specific amount of time to live, these connections normally will not be destroyed for that period of time. By calling the ReleaseObjectPool() method, this destruction will be more timely.

An OLE DB provider enables a number of services including connection pooling (which it calls resource pooling), transaction auto-enlistment, and client-side cursors. These services are enabled or disabled based on the provider's OLEDB_SERVICES Registry key. Because changing this Registry key would cause all applications on that machine to be affected by that change, the preferred method is to use the OLE DB Services connection string attribute to modify the behavior.

For example, if I wanted to disable pooling and automatic transaction enlistment, I would do the following:

OleDbConnection conn = new OleDbConnection("Server=localhost;" +                                "OLE DB Services=-4;" +                                "Integrated Security=true");conn.Open();

Other than in the connection string, there is no way to control this behavior. The IDataInitialize::GetDataSource() OLE DB call can initialize it with specific values, but this is really only useful when you are calling OLE DB directly or within an OLE DB provider.

2.2.4.3 Oracle Managed Provider's Connection Pooling

The Oracle Managed Provider implements connection pooling much like the SQL Server Managed Provider. In other words, connections are pooled by identical connection strings within a single process. Several settings can be specified in the connection string to change the default behavior of Oracle Managed Provider's connection pooling.

2.2.4.4 ODBC Managed Provider's Connection Pooling

The ODBC Managed Provider has no native support for connection pooling; however, in ODBC 3.0 and above there is support for connection pooling. There are two methods for enabling ODBC connection pooling; neither of them has anything to do with ADO.NET. First, if you have the ODBC Data Source Administrator 3.5 or above, you can use the Connection Pooling tab Simply double-click the driver name and enable or disable the connection pooling . This will affect all software on the computer.

The other method for enabling ODBC connection pooling is to call the ODBC API to enable connection pooling during your process. You do this by importing the ODBC call with DLLImport. Listing 2.4 shows how I have wrapped the calls into a simple class.

Listing 2.4 Enabling ODBC Connection Pooling

/*  Example Usage:    ODBCPooling.Enable();*/public class ODBCPooling{  [System.Runtime.InteropServices.DllImport       ("odbc32.dll",       CharSet=System.Runtime.InteropServices.CharSet.Auto)]  private static extern int SQLSetEnvAttr(                                   long Environment,                                   long EnvAttribute,                                   long ValuePtr,                                   long StringLength);  const long SQL_ATTR_CONNECTION_POOLING = 201;  const long SQL_CP_ONE_PER_DRIVER = 1;  const long SQL_IS_INTEGER = -6;  const long SQL_CP_OFF = 0;  static int Enable()  {    return SQLSetEnvAttr( 0,                          SQL_ATTR_CONNECTION_POOLING,                          SQL_CP_ONE_PER_DRIVER,                          SQL_IS_INTEGER);  }  static int Disable()  {    return SQLSetEnvAttr( 0,                          SQL_ATTR_CONNECTION_POOLING,                          SQL_CP_OFF,                          SQL_IS_INTEGER);  }}

You only need to do this once per process to enable the pooling of connections. You can turn pooling for ODBC on and off by enabling or disabling it with this class. Unfortunately, there is no support for configuring the pooling beyond just enabling or disabling it.

So it seems that the different providers have different techniques for pooling. I also found an interesting article on how Sql provider does connection pooling internally and how you can force the pool to be flushed, you can read it at

http://www.sys-con.com/dotnet/article.cfm?id=483

The below article discusses connection pooling in Microsoft Data Access Components(MDAC) 

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmdac/html/pooling2.asp

The below article provides an in-depth look in general data access using .Net and has good coverage on connection pooling.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daag.asp