008 ADO.Net Connection Pooling

Connection pooling

                  An application connect to database with the help of connection. Creating a connection is  high resource consuming process .To establish a connection , a connection string is required , which is either from machine.config or web.config  or from hard coded string . To create a connection , a physical socket channel  or named pipe is established, handshaking between client and server occurred.

                      Ado.Net architecture is disconnected in nature, web application frequently fires sql query to the database for data manipulation. Each time a query is fire, physical connection is required to execute this query. To establish connection each time, performance of the web application degraded and make quality of the application poor.

                      To speed up the process , connection pool is used. Connection pool is a technique to  reuse existing connection. The pool maintain the ownership of the physical connection. When a connection is free,  it is keep in a connection pool and make the connection alive. When the next  connection is ask,  connection pool first check, if there is any available connection  in the pool . If there is available connection in the pool, connection is assign from the pool else a new connection is established.

                      Connection pooling  reduce the number of times new connection open, increase  performance and scalability of the application.

                        Connection pool remove when ISS  reset or application domain unloaded.
Another option is  ClearAllPools and ClearPool  to clear the connection for a given provider and for a specific connection respectively.

 Here are some points related to the connection pooling
* If you want to pull a set of connection, the connection string must be same.
* Security context of each connection must be same.
* All connection must be comes under the same process.

Below is the example of connection pool

Example 

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);

        }

}

You can control the connection pool by the help of this property

MaxPoolSize : Maximum number of connection is allowed in the pool.Default value is 100
MinPoolSize : Minimum number of full allowed in the connection pool. Default value is 0.
Pooling: Boolean value true or false. when true  connection is drawn from pooling.
Connection Timeout:  Maximum number of second to wait  for a connection to be established. The default value is 15.
Incr Pool Size  : Number of connection to be established when all connection are in use. The default value is 5.

How a connection pool can be visible. Connection pool can be visible  in the SQL server.
Exec sp_who / sp_who2 stored procedure return  result that number of connection is there in the database . You can also monitor  connection pooling performance from SQL profiler also.


Example 1

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

Output

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

database2    4            user1