Connection Pooling in .NET

Acquiring a connection takes a short, but definite amount of time. In web application in which requests are being handled efficiently,connections will be opened and closed endlessly as new requests are processed. In this environment, a small overhead required to establish a connection can become significant and limit the scalability of the system.

One solution is connection pooling. Connection pooling is the practise of keeping a permanent set of open database connections to be shared by the sessions that uses the same data sources. This avoids the need to create and destroy connection all the time.

When client requests the connection by calling Open(), it's served directly fro the available pool, rather than re-created. When the client release the connections by calling Close() or Dispose(), it's not discarded but returned to the pool to serve next request.

You can able to configure connection pool setting in connection strings. Various connection pool settings are as follows

a. Max Pool Size : The maximum number of connections allowed in the pool. If the maximum pool size size has been reached, any further attempts wait until a connection become available.(An error is raised if the connection timeout value elapses before a connection available.)

b. Min Pool Size : The minimum number of connection always retained in the pool(default as 0). This number of connections will be created when the first connection is opened, leading minor delay for the first request.

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

d. Connection Life Time : Connections is returned to the pool and its creation time is older than the specified lifetime.

Example of connection string that set a minimum pool size:

string Connectionstring = "Data Source = localHost; Initial Catalog = master; Integrated Security = SSPI; Min Pool Size = 10;Max Pool Size=60";

SqlConnection con = new SQlConnection(Connectionstring);

//Get the connection from the pool( if it exists)
//or create the pool with 10 connections(if it doesn't)
con.Open();

//Return connection to the pool.
con.Close();