Connection Pooling in ASP.NET

• Download ContextFactoryDAO - 1,021 B
• Download sample - 2.44 KB

Introduction

Now a days, more and more people are using the internet. People use websites for knowing all the details from various service sectors like railway, telecommunication, air travel, tourism, courier, cargo, etc..., oh God, I cannot say it will keep on going. So if we create a website for some service, definitely we look forward to having more customers. So if more customers log into the database, web servers perform slowly because of more multiple connections getting opened. To avoid this problem, we have to use the best feature named "connection pooling" in our website or Windows application in the connection string.

ConnectionString

I think you know about connectionstring, still I will tell you what it is. It is something like an address for connecting an application to the database wherever the DB is located. It looks like:

SqlConnection Objsqlcon=new SqlConnection("intialcatalog=db_test;DataSource=DBSERVER ;")

Connection Pooling

Connection polling is a very simple thing for implementing, but it makes a huge difference in the performance when more users are logged in. By default, a connection string is enabled with connection pooling.

By default, the maximum number of pools is 100, minimum is 0.

Working of Connection Pooling

Let's see how it works?

We said that by default we have 100 pools. When the first user enters our webpage and tries to open one connection, then the first connection pool is created. Now 99 pools are remaining. At the same time, one more user tries to open a connection with the same string then our pooler checks whether any opened pools with same connection string are free or available. If the first user closes the connection, that pool will be free and this user starts using the same pool. So this second user also uses the same pool and we can get more performance and resources. Suppose if the pool is not freed and the first user is still using it, what the pooler will do from the remaining 99 pools is that it will create one more pool and use it. Like this, when n number of users try to establish a connection if the pools are free it will reuse the same pool or if 100 pools are engaged, it will wait for a pool to be free. If the pool is getting free in a particular time, it will use it otherwise the waiting connection will expire. So for huge customer websites, 100 pools will not be enough. So we have to allow more pools.

Remember

If the connection string is different, it will create a new pool.

Using the Code

Below we can see the connection string created, here I am using a separate class for connection establishment and connection opening. If our website is huge, we don't want to write the connection string everywhere. Just create a connection in a class.

Here the class name is:

public class ContextFactoryDAO

We are establishing the connection string in the constructor of the same class. So by the time we create the object for the class, the connection string is ready. We are using a string builder for creating the connection string, so if there is any change we can alter it later very easily.

SqlConnectionStringBuilder Obj_sqnbuild = new SqlConnectionStringBuilder(); 

In this code below, you can see the pooling details I have given:

public class ContextFactoryDAO
{
        //For switching between access and MSSQL two channel is instantiated
        SqlConnection Obj_sqlcon = 
		new SqlConnection();//making the instance of sqlconnection
        OleDbConnection Obj_olecon = 
		new OleDbConnection();//making the instance of oledbconnection
        SqlConnectionStringBuilder Obj_sqnbuild = 
		new SqlConnectionStringBuilder();//making the instance for the 
						//sqlConnection String builder
        OleDbConnectionStringBuilder  Obj_odnbuild = 
		new OleDbConnectionStringBuilder();//making the instance for 
					//oledbconnection string builder
       
        public  ContextFactoryDAO()//Connection in constructor so as to
					//instantiate at an early stage
        {
            ///* connection string building for sql and oledb from 1st line to 5th line*/
             SqlConnectionStringBuilder Obj_sqnbuild = 
		new SqlConnectionStringBuilder();//making the instance for 
					//the sqlConnection String builder
             Obj_sqnbuild.InitialCatalog = "dB_test";
             Obj_sqnbuild.DataSource = "DBSERVER";
             Obj_sqnbuild.UserID = "sa";
             Obj_sqnbuild.Password = "db_Ser3er_2009";
             Obj_sqnbuild .Add ("Max pool size",1500);
             Obj_sqnbuild .Add ("Min pool size",20);
             Obj_sqnbuild.Add("Pooling", true);
             Obj_sqlcon.ConnectionString = Obj_sqnbuild.ConnectionString;      
        }
     
        public void opensqlcon(SqlConnection connect)//function for opening 
	//the SQL channel which will close the function if it is opened already
        {
            if (connect.State == ConnectionState.Open)
            {
                connect.Close();
            }            
                connect.Open();
        }
       
        public string sConNme//Property for retrieving the sql connection string
        {
            get
            {
                return Obj_sqlcon.ConnectionString;
                //return Obj_odnbuild.ConnectionString;
            }
            set
            {
                sConNme = value;
            }
        }       
    }
}

Now in this last line of code, I have assigned the connection string to a sqlconnection object from a string builder.

Now I have one method for opening the connection and property for getting the connection string.

In our presentation layer, if have to open the connection, I will create the object of this class and access the opensqlcon( connectionstring).

The property is accessing the connection string.

I request you to download the sample source code and try the demo for it.

History

• 5^th December, 2009: Initial post