Connecting to the database is resource intensive and a relatively slow operation in an application but the most crucial of them all. A Connection Pool is a container of open and reusable connections. A Connection Pool is released from the memory when the last connection to the database is closed. The basic advantage of using Connection Pooling is an improvement of performance and scalability while the main disadvantage is that one or more database connections, even if they are currently not being used, are kept open. The Data Providers in ADO.NET have Connection Pooling turned on by default; if you need to turn it off, specify Pooling = false in the connection string being used. Connection Pooling gives you an idle, open, reusable connection instead of opening a new one every time a connection request to the database is made. When the connection is closed or disposed, it is returned to the pool and remains idle until a request for a new connection comes in. If we use Connection Pooling efficiently, opening and closing of connections to the database becomes less resource expensive. This article discusses what Connection Pooling is all about and how Connection Pooling can be used efficiently to boost the performance and scalability of applications.
Connection pooling is enabled for both OleDb and SqlClient connections by default.
To take advantage of connection pooling, you must be careful to do two things in your ASP.NET pages. First, you must be careful to use the same exact connection string whenever you open a database connection. Only those connections opened with the same connection string can be placed in the same connection pool. For this reason you should place your connection string in the web.config file and retrieve it from this file whenever you need to open a connection
To take advantage of connection pooling in your ASP.NET pages, you also must be careful to explicitly close whatever connection you open as quickly as possible. If you do not explicitly close a connection with the Close() method, the connection is never added back to the connection pool.
connection pooling options that you can add to the SQL Server connection string:
- Connection Lifetime— destroys a connection after a certain number of seconds. The default value is 0, which indicates that connections should never be destroyed.
- Connection Reset— indicates whether connections should be reset when they are returned to the pool. The default value is true.
- Enlist— indicates whether a connection should be automatically enlisted in the current transaction context. The default value is true.
- Max Pool Size— the maximum number of connections allowed in a single connection pool. The default value is 100.
- Min Pool Size— the minimum number of connections allowed in a single connection pool. The default value is 0.
- Pooling— determines whether connection pooling is enabled or disabled. The default value is true.
Sample
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
string connectionString = @"Min Pool Size=10;• Connection Lifetime=0;• Max Pool Size=2000;Pooling=true;Data Source=.\SQLExpress;Integrated Security=True;AttachDbFileName=|DataDirectory|MyDatabase.mdf;User Instance=True";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("SELECT * FROM master..sysprocesses WHERE hostname<>''", con);
using (con)
{
con.Open();
grdStats.DataSource = cmd.ExecuteReader();
grdStats.DataBind();
}
}
}
Html
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
<title>Show User Connections</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<h1>User Connections</h1>
<asp:GridView
id="grdStats"
Runat="server" />
</div>
</form>
</body>
</html>
Stay Tune...
Have a nice day... 'N happy Coding :)
