Creating a database connection is somewhat time-consuming because it involves the overhead of network-level handshaking and security credentialing for each new connection request. ADO.NET uses a technique called connection pooling, which minimizes the cost of repeatedly opening and closing connections. Connection pooling reuses existing active connections with the same connection string instead of creating new connections when a request is made to the database. It involves the use of a connection manager that is responsible for maintaining a list, or pool, of available connections for a given connection string. Several pools exist if different connection strings ask for connection pooling.
You can turn off pooling for
a specific connection by including the Pooling=false key-value pair in your
connection string. The SqlConnection class also includes two methods ClearPool
and ClearAllPools that let you clear its associated pool or all pools currently
managed by the provider within your application respectively.
The following example shows a connection string with the connection pooling option:
using System.Data.SqlClient;
namespace ConnectionPooling { { class Program { static void Main(string[] args) { { string sqlConnectString= "Data Source=localhost;Integrated security=SSPI;Initial Catalog=AdventureWorks;";
SqlConnection connection = new SqlConnection();
// Set the connection string with pooling option connection.ConnectionString = sqlConnectString + "Connection Timeout=30;Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;"; //Open connection connection.Open();
//Close connection connection.Close(); } } }
A Connection String in the Web.Config file with connection pooling option:
<connectionStrings>
< <clear />
< <add name="sqlConnectionString"connectionString="Data Source=mySQLServer;Initial Catalog=myDatabase;Integrated Security=True;Connection Timeout=15;Connection Lifetime=0;Min Pool Size=0;Max Pool Size=100;Pooling=true;" />
</ </connectionStrings> <![if !supportLineBreakNewLine]> <![endif]>
SQL Server connection string pooling attributes
Connection Lifetime: Length of time in seconds after creation after which a connection is destroyed. The default is 0, indicating that connection will have the maximum timeout.
Connection Reset: Specifies
whether the connection is reset when removed from the pool. The default is
true.
Enlist: Specifies
whether the connection is automatically enlisted in the current transaction
context of the creation thread if that transaction context exists. The default
is true.
Load Balance Timeout: Length
of time in seconds that a connection can remain idle in a connection pool
before being removed.
Max Pool Size: Maximum
number of connections allowed in the pool. The default is 100.
Min Pool Size: Minimum
number of connections maintained in the pool. The default is 0.
Pooling: When true, the connection is drawn from the appropriate pool, or if necessary, created and added to the appropriate pool. The default is true.
Leave Comment
1 Comments