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:
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
A Connection String in the Web.Config file with connection pooling option:
Security=True;Connection Timeout=15;Connection Lifetime=0;Min Pool Size=0;Max
Pool Size=100;Pooling=true;" />
SQL Server connection string
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
whether the connection is automatically enlisted in the current transaction
context of the creation thread if that transaction context exists. The default
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.