Connection Pooling

When we want a front-end to communicate with backend, we need to establish connection 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 it is very clear 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 problem 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. At the same time it’s not recommended to Open the connection use it and then immediately close it because Opening requires lots of steps as mentioned above and would then 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 connection can be maintained and 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 requests from different clients will be executing the same code and will be using the same connection string to connect to the database. Basically what I mean is many identical connections will have to be repeatedly opened and closed.

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

What is Connection Pooling?

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

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 I becomes available to another connection object with in the same application.

Connection 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)

Note: For every different value of ConnectionString and for every Identity a new Connection Pool is created by the Managed Provider. Only when two objects use the same connection string, they would use the same pool.

For Example:

class Program

{

  static void Main(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();

      }

}

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.

Scenario A:

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 B:

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 C:

Following code will through runtime exception in 101th 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();

//...

}