What is Connection Pooling in ADO.NET

When we want a front-end to communicate with database, we need to establish connections between them.

For this:

1. Database server has to create a socket and wait for the request to come.
2. Client will have to create a socket in its own process.
3. Client using its socket has to submit a request to server socket.
4. Sever socket should receive the request on existing client and shift’s the client to another new socket which has to be created freshly. This is done so that server main socket is free to receive request from another client(s).
5. Server will have to parse the connection string and authenticate the client
6. Server will then send an acknowledgement to the client that its connection is accepted.
7. Now both client and server are connected and they are ready for data exchange.

I am sure from the above you have understood that it is time taking process and to avoid this developers used to write code to Open the database connection in the beginning of the application and keep it open till the end. This has obvious drawback of keeping the resources blocked though are not in use, what I mean is if the application is running for let say one hour it doesn’t mean that all the time continuously it would keep fetching or updating data from the database though the connection to the database might remain open. At the same time it’s not recommended to Open the connection use it and then immediately close it because opening every time has the above mentioned cost (7 steps) and would also degrade the performance of the application.

In Windows based application, this is not really a very big concern because every client will be running its own copy of the application and thus a global variable can manage the single connection and same can shared by all the code running in that instance of the application.

Big concern is in Web Based applications (which are stateless) where on a single Web Server receives the requests from different browser based clients it will be executing the same code and will be using the same connection string to connect to the same database. Basically what I mean is many identical connections will have to be repeatedly opened and closed for every request and this would be a costly affair.

To minimize the overhead of Opening the connection every time it is required, ADO.NET uses the technique of Connection Pooling.

What is Connection Pool?
Connection Pool is a buffer in memory where the Managed Provider is going to keep all the physical connections to the database having the same connection string.

Note: For every different value of ConnectionString and for every Windows Identity (if Integrated Security is used) a new Connection Pool is created. Only when two Connection objects use the same connection string, they would use the same pool.

Customizing Connection Pooling:
It can be done by setting following key value pairs in ConnectionString property of connection object.

a. Pooling: True to enabling and False to disable connection pooling. Default is True.
b. Max Pool Size: Max number of connections allowed in the pool. Default value of this is 100
c. Min Pool Size: Number of connections which must be created when the Pool is created. Default is 0.

What happens if Pooling is enabled?
When connection pooling is enabled in the connection string and we try to open the connection, first the connection object will search the connection in the pool. If it finds a free connection in the pool, it uses the same but if either the pool is empty or all the connections in the pool are already in use then a new physical connection is established. This way the time required for opening the connection to the database is reduced and this will improve the performance of the application.

The care the developer has to take is, after opening the connection and performing the database operation the Close method of the connection object must be called immediately. Close method actually doesn’t really close the physical socket connection between client and database but will return the connection to the pool so that it’s then available to another connection object with in the same application/process

When are connections removed from the PoolConnection in the pool are actually closed/removed only when they are not used for a long period of time or for some reason the socket breaks the connection with the server (its marked as invalid by ADO.NET)

SqlConnection.ClearPool: Empties the connection pool associated with the specified connection.

Let’s understand connection pooling with an example. – Please read the comments provided with every statement.

class Program
{

static voidMain(string[] args)

{

SqlConnection c1,c2,c3,c4,c5;

c1 = new SqlConnection("server=.\\sqlexpress;database=db1;integrated security=true");

c2 = new SqlConnection("server=.\\sqlexpress;database=db2;integrated security=true");

c3 = new SqlConnection("server=.\\sqlexpress;database=db1;integrated security=true");

c4 = new SqlConnection("server=.\\sqlexpress;database=db1;integrated security=true");

c5 = new SqlConnection("server=.\\sqlexpress;database=db1;uid=sa;pwd=ss");

c1.Open(); //Pool 1 is created, a new connection is established

c2.Open(); //Pool2 is created, a new connection is established because database name is different

c3.Open(); //Pool1 is used but a new connection is established because c1 is not yet closed

c1.Close(); //Connection is returned to the pool and marked as free.

c4.Open(); //Pool1 is used and the Free connection released in previous statement is used.

c5.Open(); //Pool3 is created because authentication information is different.

c2.Close();

c3.Close();

c4.Close();

c5.Close();

}

}

Scenario 1:
Following code will create only one connection in the Pool and same connection is used by all the 100 connection objects.
SqlConnection [] connections = new SqlConnection[100];
for (int i = 0; i < connections.Length ; i++)
{
connections[i] = new SqlConnection("server=.\\sqlexpress;database=db1;integrated security=true");
connections[i].Open();
//...
connections[i].Close();
} 

Scenario 2:

Following code will create only 100 connection objects and 100 connections will be created in the Pool because we are not closing the connections
SqlConnection [] connections = new SqlConnection[100];
for (int i = 0; i < connections.Length ; i++)
{
connections[i] = new SqlConnection("server=.\\sqlexpress;database=db1;integrated Security=true");
connections[i].Open();
//...
}

Scenario 3:
Following code will throw runtime exception in 101^th iteration because 100 connections are opened and not closed in for loop.
SqlConnection [] connections = new SqlConnection[101];
for (int i = 0; i < connections.Length ; i++)
{
connections[i] = new SqlConnection("server=.\\sqlexpress;database=db1;integrated security=true");
connections[i].Open();
//...
}

Thankyou.

Please don't forget to leave your comment if this has helped you to understand Connection Pooling in simplest language.