C# Connection Pooling

Connection pooling significantly reduces the overhead associated with creating and destroying database connections. Connection pooling also improves the scalability of solutions by reducing the number of concurrent connections a database must maintain-many of which sit idle for a significant portion of their lifetimes. With connection pooling, instead of creating and opening a new connection object whenever you need one, you take an already open connection from the connection pool. When you have finished using the connection, instead of closing it, you return it to the pool and allow other code to use it.

The SQL Server and Oracle data providers encapsulate connection- pooling functionality that they enable by default. One connection pool exists for each unique connection string you specify when you open a new connection. Each time you open a new connection with a connection string that you have used previously, the connection is taken from the existing pool. Only if you specify a different connection string will the data provider create a new connection pool. You can control some characteristics of your pool using the connection string settings described in below table. Note that Once created, a pool exists until your process terminates.

Table 10.2: Connection String Settings That Control Connection Pooling

Setting	Description
Connection Lifetime	Specifies the maximum time in seconds that a connection is allowed to live in the pool before it's closed. The age of a connection is tested only when the connection is returned to the pool. This setting is useful for minimizing pool size if the pool isn't heavily used and also ensures optimal load balancing is achieved in clustered database environments. The default value is 0, which means connections exist for the life of the current process.
Connection Reset	Supported only by the SQL Server data provider. Specifies whether connections are reset as they are taken from the pool. A value of "True" ensures a connection's state is reset but requires an additional communication with the database. The default value is "True".
Max Pool Size	Specifies the maximum number of connections that should be in the pool. Connections are created and added to the pool as required until this figure is reached. If a request for a connection is made but there are no free connections, the caller will block until a connection becomes available. The default value is 100.
Min Pool Size	Specifies the minimum number of connections that should be in the pool. On pool creation, this number of connections are created and added to the pool. During periodic maintenance, or when a connection is requested, connections are added to the pool to ensure the minimum number of connections is available. The default value is 0.
Pooling	Set to "False" to obtain a non-pooled connection. The default value is "True".

This code excerpt from the sample code for this post demonstrates the configuration of a connection pool that contains a minimum of 5 and a maximum of 15 connections. Connections expire after 10 minutes (600 seconds) and are reset each time a connection is obtained from the pool.

// Obtain a pooled connection.
using (SqlConnection con = new SqlConnection()) {

    // Configure the SqlConnection object's connection string.
    con.ConnectionString = 
        "Data Source = localhost;" +    // local SQL Server instance
        "Database = Northwind;" +       // the sample Northwind DB
        "Integrated Security = SSPI;" + // integrated Windows security
        "Min Pool Size = 5;" +          // configure minimum pool size
        "Max Pool Size = 15;" +         // configure maximum pool size
        "Connection Reset = True;" +    // reset connections each use
        "Connection Lifetime = 600";    // set maximum connection lifetime

    // Open the Database connection.
    con.Open();

    // Access the database...
    §

    // At the end of the using block, the Dispose calls Close, which
    // returns the connection to the pool for reuse.
}

This code excerpt demonstrates how to use the Pooling setting to obtain a connection object that isn't from a pool. This is useful if your application uses a single long-lived connection to a database.

// Obtain a non-pooled connection.
using (SqlConnection con = new SqlConnection()) {

    // Configure the SqlConnection object's connection string.
    con.ConnectionString = 
        "Data Source = localhost;" +    // local SQL Server instance
        "Database = Northwind;" +       // the sample Northwind DB
        "Integrated Security = SSPI;" + // integrated Windows security
        "Pooling = False";              // specify non-pooled connection

    // Open the Database connection.
    con.Open();

    // Access the database...
    

    // At the end of the using block, the Dispose calls Close, which
    // closes the non-pooled connection.
}

The ODBC and OLE DB data providers also support connection pooling, but they don't implement connection pooling within managed .NET classes and you don't configure the pool in the same way as for the SQL Server or Oracle data providers. ODBC connection pooling is managed by the ODBC Driver Manager and configured using the ODBC Data Source Administrator tool in the Control Panel. OLE DB connection pooling is managed by the native OLE DB implementation; the most you can do is disable pooling by including the setting "OLE DB Services=-4;" in your connection string. The SQL Server CE data provider doesn't support connection pooling because SQL Server CE supports only a single concurrent connection.