Connection Pooling (ADO.NET)

Creating a database connection is somewhat time-consuming because it involves the overhead of network-level handshaking and security credentialing for each new connection request. ADO.NET uses a technique called connection pooling, which minimizes the cost of repeatedly opening and closing connections. Connection pooling reuses existing active connections with the same connection string instead of creating new connections when a request is made to the database. It involves the use of a connection manager that is responsible for maintaining a list, or pool, of available connections for a given connection string. Several pools exist if different connection strings ask for connection pooling.

You can turn off pooling for a specific connection by including the Pooling=false key-value pair in your connection string. The SqlConnection class also includes two methods ClearPool and ClearAllPools that let you clear its associated pool or all pools currently managed by the provider within your application respectively.

The following example shows a connection string with the connection pooling option:

using System.Data.SqlClient;

namespace ConnectionPooling
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=localhost;Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            SqlConnection connection = new SqlConnection();

            // Set the connection string with pooling option
            connection.ConnectionString = sqlConnectString + "Connection Timeout=30;Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;";
            //Open connection
            connection.Open();

            //Close connection
            connection.Close();
        }
    }
}

A Connection String in the Web.Config file with connection pooling option:

<connectionStrings>
    <clear />
    <add name="sqlConnectionString" connectionString="Data Source=mySQLServer;Initial Catalog=myDatabase;Integrated Security=True;Connection Timeout=15;Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;" />
  </connectionStrings>

SQL Server connection string pooling attributes

• Connection Lifetime: Length of time in seconds after creation after which a connection is destroyed. The default is 0, indicating that connection will have the maximum timeout.
  

• Connection Reset: Specifies whether the connection is reset when removed from the pool. The default is true.
  

• Enlist: Specifies whether the connection is automatically enlisted in the current transaction context of the creation thread if that transaction context exists. The default is true.
  

• Load Balance Timeout: Length of time in seconds that a connection can remain idle in a connection pool before being removed.
  

• Max Pool Size: Maximum number of connections allowed in the pool. The default is 100.
  

• Min Pool Size: Minimum number of connections maintained in the pool. The default is 0.
  

• Pooling: When true, the connection is drawn from the appropriate pool, or if necessary, created and added to the appropriate pool. The default is true.