What Is Connection Pooling In Asp.Net Using C# With Example

Introduction
In this article we will learn what is connection pooling. Why connection pooling useful , what is the max limit and minimum limit of Connection pooling. Important key points about connection pooling.

Previous Updates
In previous articles we have learnt Showing Bind multiple dropdown list using single method call using c#. Read Excel file in C#  And Display in Grid. Read Excel in Windows Application project. Chart With Database In Asp.Net Using C#.  Insert Only Numeric Values In Asp TextBox Using Regex . Get TextBox , Dropdown, CheckBox  control Values In Aspx.cs Page From User Control Using C#.

What is Connection Pooling -
When we use ADO.Net in c# application then first thing we made connection object and open it. Create command object , data reader, data set, ExecuteNonQuery, ExecuteReader  and all that.
If open a connection many things happens before that connection made. It will be a long process and take some time.
              In our applications mostly we use one or two connection configurations and repeatedly same connection configuration will be opened and closed, automatically huge time will be consumed to open and close same database connection.
To reduce the cost of opening and closing the same connection repeatedly, ADO.NET uses an optimization technique called connection pooling.

Connection Pooling means once the connection object is opened then rather then going and recreating the connection again and again Ado.Net stores or cache the connection object in Pooler.
          Later if somebody going to opens the connection then it will not go the series of steps it already did , now it simply pic the connection from connection pool where it cached   already.

Once we finished operations on database we need to Close the connection then that connection will be returned to the pool and its ready to be reused on the next Open call.

If the connectionstring uses its slightest value even though you are connecting with same database or same localhost a new connection pool will be created . Means every unique connectionString a pool will be created.

  
Create Connection Pooling In Asp.Net C#
By default connection pooling is opened in Ado.Net , we did not to do anything for this. But we can disable the connection pooling manually by using  pooling = false in connection string.

Connection pooling Practice With C#

using (SqlConnection con = new SqlConnection("Data Source=GurujiPoint;                Initial Catalog=TestDB;Integrated Security=True; Pooling=false")) {     con.Open();     // Connection Pool will not created. we have set the pooling = false. } using (SqlConnection con = new SqlConnection("Data Source=GurujiPoint;                              Initial Catalog=TestDB;Integrated Security=True")) {     con.Open();     // Connection pool B will create. } using (SqlConnection con = new SqlConnection("Data Source=GurujiPoint;                            Initial Catalog=TestDB_1;Integrated Security=True")) {     con.Open();     //New pool C will created because connection string different.      //Both B and C database is different. } using (SqlConnection con = new SqlConnection("Data Source=GurujiPoint;                          Initial Catalog=TestDB_1;Integrated Security=True")) {     con.Open();     // No new Connectin Pool will created.      //Cause This connection string matches with Connection Pool C. }

Key Points

• The connections pooler will remove connections from the pool after it has been idle for approximately 4-8 minutes
• We can define maximum number of connections can be created in the pool. By default the maximum number of connection is 100 and we can add property like Max Pool Size=100.
• We can define minimum number of connections maintained in the pool. The default is 0. We can add property like Min Pool Size=0.
• Pooling allow us to set condition to add connection string to pool or not. By default its true. In case we don’t want to add it into pool then we need to define property like Pooling=false.