ADO.NET Connection Pooling

ADO.NET Connection Pooling

In this article, I am going to discuss ADO.NET Connection Pooling with Examples. Please read our previous article where we discussed Transactions in ADO.NET.

Connection Pooling in ADO.NET:

When we use ADO.NET in our C# applications, what we do is, first we will create the connection object, then open the connection, then perform some database operations and finally close the connection as shown in the below image.

Connection Pooling in ADO.NET

Now the creation of a connection object and opening the connection object is quite intensive. In other words, when we say, open the connection, opens the socket, some kind of handshaking is happening, the connection string is parsed to check whether the connection string format is proper or not, the Authentication mechanism is executed, and lots of other series of steps have happened internally before the connection object gets connected to the underlying database. For a better understanding, please have a look at the below image.

ADO.NET Connection Pooling

Once the connection object is open, you can perform the database CRUD operation and once the DB Operations performed, you can close the connection. This creation of a connection object is quite intensive and you would like to avoid the above things again and again when you need to create the connection object.

So, in other words, we would like to say that, open the connection object, do all the series of steps (socket, handshake, connection string parsed, authenticate, etc.), do the operations, and close the connection object. But when we say close, don’t make this connection object and go for garbage collector rather than cache it in a pool. So that, the next time when someone says open connection, then get the connection object from the pool rather than going and executing the series of intensive steps. For a better understanding, please have a look at the following image.

ADO.NET Connection Pooling with Examples

So, connection pooling means, once the connection object is open, rather than going and recreating the connection object again and again, what ADO.NET does, it takes the connection object and puts it into a thing called a pooler. In the pooler, the object will be cached, and later if somebody says connection.open then rather than executing the series of steps, it takes the connection object from the pool and start executing.

Connection Pooling Example in ADO.NET:

Let us first create a console application with the name ConnectionPooling. The most important point that you need to remember is by default connection pooling is enabled in ADO.NET. Please have a look at the below example. Here, we are using a big for loop and in each iteration, we are creating the connection object, opening the connection, doing some operation (intentionally using thread sleep to check the connection pooling), and closing the connection object.

Example: Using Connection Pooling

By default, the connection pooling is enabled in ADO.NET. If you want then you can Pooling=true; in the connection string which will enable the connection pooling in ADO.NET. In the following example, we set the pooling value true and then creating 1000 connection objects. Please execute the below code and see the time taken by ADO.NET when connection pooling is enabled.

using System;
using System.Data.SqlClient;
using System.Diagnostics;
namespace ConnectionPooling
{
    class Program
    {
        static void Main(string[] args)
        {
            var stopwatch = new Stopwatch();

            string ConnectionString = "data source=LAPTOP-ICA2LCQL\\SQLEXPRESS; initial catalog=ADODB; integrated security=True; Pooling=true;";
            stopwatch.Start();

            for (int i = 0; i < 1000; i++)
            {
                SqlConnection connection = new SqlConnection(ConnectionString);
                connection.Open();
                connection.Close();
            }

            stopwatch.Stop();
            Console.WriteLine($"Pooling=true, Time : {stopwatch.ElapsedMilliseconds} ms");
            Console.ReadKey();
        }
    }
}

Output: Pooling=true, Time : 163 ms

Even though the loop is going to be executed 1000 times, we should not see too many connection objects get created rather the connection objects are going to be fetched from the connection pool. It will use the same connection object from the pool again and again. And hence you can see, it simply taking 163 ms. If you remove the Pooling=true; from the connection string, then also it is going to fetch the connection object from the pool as by default connection pooling is enabled in ADO.NET.

Example: Without Connection Pooling

If you don’t want to use connection pooling, then you need to set Pooling=false; in the connection string to disabled the connection pooling in ADO.NET. The following is the same example as the previous one, except here we are setting the pooling value to false. Please execute the below code and see the time taken by ADO.NET when connection pooling is disabled.

using System;
using System.Data.SqlClient;
using System.Diagnostics;
namespace ConnectionPooling
{
    class Program
    {
        static void Main(string[] args)
        {
            var stopwatch = new Stopwatch();

            string ConnectionString = "data source=LAPTOP-ICA2LCQL\\SQLEXPRESS; initial catalog=ADODB; integrated security=True; Pooling=false;";
            stopwatch.Start();

            for (int i = 0; i < 1000; i++)
            {
                SqlConnection connection = new SqlConnection(ConnectionString);
                connection.Open();
                connection.Close();
            }

            stopwatch.Stop();
            Console.WriteLine($"Pooling=false, Time : {stopwatch.ElapsedMilliseconds} ms");
            Console.ReadKey();
        }
    }
}

Output: Pooling=false, Time : 3976 ms

As you can see, it is taking 3976 ms as compared to 163 ms when connection pooling is disabled.

How to Verify Connection Pooling is Used in ADO.NET?

In order to check whether the connection objects are fetching from the connection pooled or not, we are going to use a tool called Performance Monitor (perfmon) which is available in Windows machines. Open the Performance Monitor (perfmon) tool and then click on the Performance Monitor button as shown in the below image.

How to Verify Connection Pooling is Used in ADO.NET?

Once you click on the Performance Monitor button, it will open the below window. Here, click on the Change Graph Type menu, and from the drop-down list select the graph option as shown in the below image.

Performance Monitor

Once you click on the Report option, it will open the below window. Here, right-click on the blank surface and click on the Remove All Counters from the context menu as shown in the below image.

Remove All Counters

Once you click on the Remove All Counters option, one popup will be opened, simply click on the OK button as shown below.

Remove All Counters

Now modify the code as shown below.
using System;
using System.Data.SqlClient;
using System.Threading;
namespace ConnectionPooling
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnectionString = "data source=LAPTOP-ICA2LCQL\\SQLEXPRESS; initial catalog=ADODB; integrated security=True; Pooling=true;";
           
            for (int i = 0; i < 1000; i++)
            {
                SqlConnection connection = new SqlConnection(ConnectionString);
                connection.Open();
                Thread.Sleep(100);
                connection.Close();
            }

            Console.ReadKey();
        }
    }
}

With the above changes in place, now, run the application. Once your application starts executing, go to the performance monitor tool and add a performance counter. In order to Add Counter, simply right-click and then select the Add Counters option from the context menu as shown in the below image.

Add Counters

Once you click on the Add Counter option, it will open the below Add Counters window First, select the .NET Data Provider for SQL Server as we are using SQL Server database, and then select the console application which should be in running mode. Then click on the Add button which adds the performance monitor and finally click on the OK button as shown in the below image.

.NET Data Provider for SQL Server

Once you click on the OK button, it will open the following report. As you can see in the below image, the number of active connections is 1. This is because as the for loop is running at any given moment of time, there will be only one connection object is opened. Further, if you notice, the number of pooled connections is also 1. That means it is fetching the object from the connection pool. Also, the number of active connections in pools is showing 1. From this data we conclude, it is using connection pooling or the connection pooling is enabled.

Connection Pooling in ADO.NET

What happens in the Performance Monitor when connection pooling is Disabled?

The data in the performance tool is not going to be updated automatically. You need to remove and add a new counter every time. First Remove all the counter from the Performance monitor tool as shown below.

What happens in the Performance Monitor when connection pooling is Disabled?

Then again Modify the Code as shown below. Here, we are setting the Pooling to false which will disable connection pooling.

using System;
using System.Data.SqlClient;
using System.Threading;
namespace ConnectionPooling
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnectionString = "data source=LAPTOP-ICA2LCQL\\SQLEXPRESS; initial catalog=ADODB; integrated security=True; Pooling=false;";
           
            for (int i = 0; i < 1000; i++)
            {
                SqlConnection connection = new SqlConnection(ConnectionString);
                connection.Open();
                Thread.Sleep(100);
                connection.Close();
            }

            Console.ReadKey();
        }
    }
}

With the above changes in place, now, run the application. Once you run the application, go to the performance monitor tool and add a new performance counter. In order to Add Counter, simply right-click and then select the Add Counters option from the context menu as shown in the below image.

What happens in the Performance Monitor when connection pooling is Disabled?

Once you click on the Add Counter, it will open the below Add Counters window. First, select the .NET Data Provider for SQL Server and then select the console application which should be in running mode. Then click on the Add button which adds the performance counter and finally click on the OK button as shown in the below image.

What happens in the Performance Monitor when connection pooling is Disabled?

Once you click on the OK button, it will open the following report. As you can see in the below image, lots of hard connects and disconnects are happening. In other words, the ADO.NET open command is actually connecting to the SQL Server database. The second thing you can see there are no active connections in the pool. But you can see the Number of Non-Pooled Connections is 1. That means connection pooling is disabled.

ADO.NET Connection Pooling with Examples

Note: 1 connection pool is created for a unique connection string. A slight change in the connection string will create a new pool.

Example: two connection objects with the same connection string

Please have a look at the following example. As you can see both the connection strings are identical. So, whether we use ConnectionString1 or ConnectionString2, it will take the connection object from the pool.

using System;
using System.Data.SqlClient;
namespace ConnectionPooling
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnectionString1 = "data source=LAPTOP-ICA2LCQL\\SQLEXPRESS; initial catalog=ADODB; integrated security=True; Pooling=true;";
            string ConnectionString2 = "data source=LAPTOP-ICA2LCQL\\SQLEXPRESS; initial catalog=ADODB; integrated security=True; Pooling=true;";

            SqlConnection connection1 = new SqlConnection(ConnectionString1);
            connection1.Open();
            connection1.Close();

            SqlConnection connection2 = new SqlConnection(ConnectionString2);
            connection2.Open();
            connection2.Close();
        }
    }
}
Example: two connection objects with the different connection string

Please have a look at the following example. As you can see both the connection strings are communicated to the same database and to the same computer. But, in connectionstring1 we have specified the computer name and in connectionstring2 we have specified localhost, In this case, the ADO.NET will create one connection pool for ConnectionString1 and another connection pool for ConnectionString2.

using System;
using System.Data.SqlClient;
namespace ConnectionPooling
{
    class Program
    {
        static void Main(string[] args)
        {
            string ConnectionString1 = "data source=LAPTOP-ICA2LCQL\\SQLEXPRESS; initial catalog=ADODB; integrated security=True; Pooling=true;";
            string ConnectionString2 = "data source=localhost\\SQLEXPRESS; initial catalog=ADODB; integrated security=True; Pooling=true;";

            SqlConnection connection1 = new SqlConnection(ConnectionString1);
            connection1.Open();
            
            SqlConnection connection2 = new SqlConnection(ConnectionString2);
            connection2.Open();

            Console.ReadLine();

            connection1.Close();
            connection2.Close();
        }
    }
}

Run the application and open the performance monitor tool and create a new counter and you should see two connection pools are created as shown in the below image.

Connection Pooling in ADO.NET with Examples

In the next article, I am going to discuss ADO.NET Distributed Transactions with Examples. Here, in this article, I try to explain Connection Pooling in ADO.NET with Examples and I hope you enjoy this ADO.NET Connection Pooling article.

Leave a Reply

Your email address will not be published. Required fields are marked *