Best resource for Online free Education
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.
Now the creation of a connection object and opening the connection object is quite expensive. In other words, when we say, open the connection, it will internally do a lot of things i.e. 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.
Once the connection object is open, then you can perform the database CRUD operation and once the DB Operations are performed, you can close the connection. This creation of a connection object is quite expensive and you would like to avoid the above things again and again when you need to create the connection object in C#.
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, dont make this connection object 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 expensive steps. For a better understanding, please have a look at the following image.
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 place called 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 C# 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 in C#
By default, the connection pooling is enabled in C# 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.
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 in C# ADO.NET
If you dont want to use connection pooling, then you need to set Pooling=false; in the connection string to disabled the connection pooling in C# ADO.NET. The following is the same example as the previous one, except here we are setting the pooling value to false in the connection string. Please execute the below code and see the time taken by ADO.NET when connection pooling is disabled.
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.
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.
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.
Once you click on the Remove All Counters option, one popup will be opened, simply click on the OK button as shown below.
Now modify the code as shown below.
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.
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.
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.
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.
Then again Modify the Code as shown below. Here, we are setting the Pooling to false which will disable connection pooling.
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.
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.
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.
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.
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.
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.
