Connection pooling

Connection pooling is a technique to maintain database connection in cache .This reduce overhead on connection open and close again and again .In an application where large number of user is there , connection open for one user do not closed or released , instead it is being pooled for next user or request, where next user ask for connection , pooled connection is offered .This reduced overhead on connection open and close and also speed of performance of the application. In case all connection is in use , and new connection is asked , a new connection is opened and put into the pool again. When a connection is open , what actually happen ? the answer is , a named pipe or socket established with handshake mechanism .But in connection pooling is created based on an extra machine algorithm , that associated the pool with connection string when a new pool called and does not match the existing pool , a new pool created .Here is example of connection pooling.

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data;

using System.Data.SqlClient;

public partial class Default2 : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

          string sqlConnectString = "Data Source= xxx.xxx.xxx.xxx; initial catalog=MyDatabaseName;Connect Timeout=6000; User ID=MyUserID;Password=MyPassword";

          SqlConnection connection = new SqlConnection();

          connection.ConnectionString = sqlConnectString + "Connection Timeout=30;Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;";

          connection.Open();

          //for command executing

          SqlCommand cmd = new SqlCommand("select * from tbl_myTable");

          cmd.Connection = connection;

          SqlDataReader dr = cmd.ExecuteReader();

         

          //for data set

          SqlDataAdapter da = new SqlDataAdapter("select * from tbl_myTable", connection);

        

    }

}

There are several property of connection pooling , we can set maximum pooling size or Minimum pooling size for a connection pool.We can run sql query to identify how many active connection is currently active

SELECT 

    DB_NAME(dbid) as Data_Base_Name, 

    COUNT(dbid) as Number_Of_Connections,

    loginame as Login_uSER_Name

FROM

    sys.sysprocesses

WHERE  dbid

GROUP BY  dbid, loginame

Result
----------------------------------------
database1    3            user1

database2    4            user1