A connection pool is a set of database connections that are available for an application to use. Connection Pooling is the concept of using a connection pool for enhanced performance. As we know every application requires connection to database to perform operations like insert, update and delete and to fetch records from database to display. This requires a connection to be specified between the applications so that user can transfer data to and fro from the database server. Connection pooling is the concept that helps in maintaining or reusing the already open connection and hence improves site performance.

Connection pooling is used to enhance the performance of executing commands on a database. Before executing a command a connection to that database needs to be established. Sometimes creating and tearing down the connection is more costly then executing the command.

Concept behind Connection Pooling:

• When a user request a connection, it is returned from the pool rather than establishing new connection and, when a user releases a connection, it is returned to the pool rather than being released.


• Be sure than your connections use the same connection string each time. Connection pooling only works if the connection string is the same. If the connection string is different, then a new connection will be opened, and connection pooling won't be used.


• Connection pooling is extremely useful when used with applications that do not have a state. State is a presence between instances. Active Server pages are considered stateless since they do not share data between themselves. Stateless applications benefit from connection pooling since they can not hold a connection open by themselves.


• The main benefit of pooling is performance. Making a connection to a database can be very time-consuming, depending on the speed of the network as well as on the proximity of the database server. When pooling is enabled, the request for a database connection can be satisfied from the pool rather than by (re)connecting to the server, (re)authenticating the connection information, and returning (again) a validated connection to the application.


• Multiple connections, all of which may not be of use, to the database are open, this is the flaw in the connection pooling, and developer must address this first before implementation of connection pooling.

Managing Connection Pooling

Connection pooling allows you to reuse connections that are already opened, rather than creating a new one every time we need to send/retrieve the data from the database. Connection pooling behavior can be controlled by using connection string. For E.g.

In Ado.Net data providers, connection string options can defines:

• Number of connections pools


• Number of connections in a pool


• Lifetime of pooled connections used by each process.

Note:- Connection pooling in Ado.Net is not provided by the core components of .Net framework, this must be implemented in the Ado.Net data provider itself.

Creating a Connection Pool

The connection pool is associated with a specific connection string. By default, the connection pool is created when the first connection with a unique connection string connects to the database. The pool is populated with connections up to the minimum pool size. Additional connections can be added until the pool reaches the maximum pool size.

• Pool remains active as long as connection remains open


• If a new connection is open, the connection string doesn't match with the one which is used earlier to connect to the database, then new connection pool will be created.


• Connection pooling enhanced the performance and scalability of the applications.


• Once created connection pools are not destroyed until the active process ends or the connection lifetime is exceeded.

Example - Demonstrate Connection Pooling

In below code snipplet Connection Pool “A” is created and filled with connections upto the minimum pool size .

SqlConnection con = null;

string conStr = "";

con = new SqlConnection(conStr);

con.open();

Opening and Closing Connections

Open connections just before they are needed. Opening them earlier than necessary decreases the number of connections available to other users and can increase the demand for resources. One should explicitly close the connection as soon as it is no longer in use. If one waits for garbage collection to implicitly close connection that go out of scope, the connections are not returned to the connection pool immediately.

Always, close connection in the finally block, because code in the finally block always runs, regardless of whether an exception occurs. This guarantees explicitly closing of connection. For example:

Try {

    Con.open ();

} Catch (Exception ex) {

    // Handle Exception

} Finally {

    // Close the Connection

    If (Con! = null)

        Con.close ();

}