All built-in .NET data providers support connection pooling, but each relies on a different implementation. For example, the SQL Server data provider (System.Data.SqlClient) and the Oracle data provider (System.Data.OracleClient) resort to a set of internal classes defined within the .NET Framework. On the other hand, the .NET data provider for OLE DB providers (System.Data.OleDb) delegates connection-pooling management to the OLE DB service infrastructure for session pooling. You use connection string arguments (e.g., “OLE DB Service”) to enable or disable various OLE DB services including pooling. Likewise, the data provider for ODBC benefits from the services supplied by the ODBC Driver Manager.
Both the .NET data providers for SQL Server and Oracle support local and distributed transactions. For distributed transactions, they automatically enlist in a transaction and obtain transaction details from Windows 2000 Component Services. If automatic enlistment is disabled through the connection string, the method EnlistDistributedTransaction allows you to accomplish it manually. Note that the method is only supported in the .NET Framework 1.1.
Some of the settings in the connection string directly affect the pooling mechanism. The parameters you can control to configure the environment are the following: Connection Lifetime, Connection Reset, Enlist, Max Pool Size, Min Pool Size, and Pooling.
The Connection Lifetime attribute sets the maximum duration in seconds of the connection object in the pool. This value is used when the object is returned to the pool. If the creation time plus the lifetime is earlier than the current time, the connection object is destroyed. Connection Reset determines whether the database connection is reset to default settings when being drawn from the pool. This attribute is true by default. Enlist indicates that the pooler automatically enlists the connection in the creation thread's current transaction context. You can control the maximum and minimum number of connections allowed in the pool through the Max Pool Size and the Min Pool Size attributes. Their default values are 100 and 0 respectively. Finally, Pooling is a Boolean attribute that indicates if connection pooling is enabled. To disable connection pooling, you just set Pooling to False. Note that Boolean values can also be set using Yes/No instead of True/False.
Each connection pool is associated with a distinct connection string and the transaction context. When a new connection is opened, if the connection string does not exactly match an existing pool, a new pool is created. Once created, connection pools are not destroyed until the process ends. This behavior does not affect the system performance because maintenance of inactive or empty pools requires only a minimal overhead.
When a pool is created, multiple connection objects are created and added so that the minimum size is reached. Next, connections are added to the pool on demand, up to the maximum pool size. When a connection object is requested, it is drawn from the pool as long as a usable connection is available. A usable connection must currently be unused, have a matching or null transaction context, and have a valid link to the server. If no usable connection is available, the pooler attempts to create a new connection object. When the maximum pool size is reached, the request is queued and served as soon as an existing connection object is released to the pool. Connections are released when you call methods like Close or Dispose. Connections that are not explicitly closed might not be returned to the pool unless the maximum pool size has been reached and the connection is still valid.
A connection object is removed from the pool if the lifetime has expired or if a severe error occurred. In this case, the connection is marked as invalid and subsequently removed from the pool.
Dino Esposito is Wintellect's ADO.NET and XML expert, and a trainer and consultant based in Rome, Italy. Dino is a contributing editor to Windows Developer Network and MSDN Magazine, and the author of several books for Microsoft Press including Building Web Solutions with ASP.NET and ADO.NET and Applied XML Programming for .NET. Contact Dino at [email protected].
