This article has been excerpted from book "A Programmer's Guide to ADO.NET in C#".
After a connection has been created and placed in a connection pool, client applications can reuse these connections without performing the complete connection process. The process of reusing connection resources from a connection pool is called connection pooling. The connection pooling process may increase the performance of an application because an application doesn't need to open close a connection repeatedly.
The Connection pooling mechanism works different for different data providers. The Connection class defines members that allow you to pool connection resources manually.
If you've used connection pooling in ADO or OLE DB, you must be familiar with the OLE DB services parameter. OLE DB providers automatic session pooling (also known as connection pooling). Which is handled by OLE DB core components though its providers.
The OLE DB Services parameter of connection string describes the services that are enabled for a connection. A typical connection string looks like this:
DSN=LOCALServer;UID= sa;PWD=;OLE DB Services = -1
Table 5-22 shows the value and their meaning for the OLE DB Services parameter.
Table 5-22. The OLE DB Service settings
|
SERVICES ENABLED |
VALUE |
|
All services (default) |
"OLE DB Services = -1;" |
|
All services except pooling |
"OLE DB Services = -2;" |
|
All services except pooling auto enlistment |
"OLE DB Services = -4;" |
|
All services except client cursor |
" OLE DB Service = -5;" |
|
All services except client cursor and pooling |
" OLE DB Services = -6;" |
|
No services |
"OLE DB Service = 0;" |
The OleDb data provider uses the OLE DB API internally so it supports automatic connection pooling. You can enable and disable connection pooling programmatically in the OleDb data providers through its connection string For example, the following string disable the connection pooling:
String ConnString = "Provider = SQLOLEDB;OLE DB Services = -2; Data Source=localhost;" + "Integrated Security = SSPI; ";
ADO.NET manages connection pooling when you use the Close or Dispose method of a Connection object. A connection pool reuses the resources allocated to connection. Once a pool is created, you can add connections to this pool until it reaches its maximum size. You can define the maximum size of a connection pool using the connection string. If a pool reaches its maximum size, the next added connection would go the queue wait until the pool releases one existing connection.
You create a pool when you call the Open method connection based on the connection string. If you're using the same database for two Connection objects, but the connection string is different (including spaces and single characters), both connections will be added to different pools. For example, Listing 5-28 creates two connections: conn1 and conn2. The ConnectionString1 and ConnectionString2 connection strings are different for both connections. Because both these connections have different connection strings, they will be added to two different pools.
Listing 5-28. Creating two connection with different strings
// create a connection object
string ConnectionString1 = "Integrated Security = SSPI;" + "Initial Cataog = Northwind; " + "Data Source – localhost;";
SqlConnection conn1 = new SqlConnection(ConnectionString1);
// create a conenction object
string ConnectionString2 = "Integrated security = SSPI;" + "Initial catelog= pubs;"+ " Data source = localhost;";
SqlConnection conn2 = new SqlConnection(ConnectionString2);
// open connections
conn1.Open();
conn2.Open();
// some code
conn1.Close();
conn2.Close();
Caution: You must call Close or Dispose method of Connection to close the connection. Connections that are not explicitly closed are not added or returned to the pool.
You can set the behavior of connection pooling SQL server data providers by setting the ConnectionString values. Some of the pooling settings are in the form of key- value pairs (see Table 5-23).
Table 5-23. Connection Pooling Settings
|
KEY |
DESCRIPTION |
|
Connection Life time |
Connection creation time is compared with the current time, span exceeds the Connection Lifetime value, and object pooler destroys the connection. The default value is 0, which will give a connection the maximum timeout. |
|
Connection Reset |
Determines whether a connection is reset after it was removed from the pool. The default value is true. |
|
Max pool size |
Maximum number of connections allowed in the pool. The default value is 100. |
|
Min pool size |
Minimum number of connections allowed in the pool. The default value is 0. |
|
Pooling |
When true, the connection is drawn from the pool or created if necessary. The default value is true. |
The OleDbConnection class providers a ReleaseObjectPool method that you can use to free resources reserved for connection. You call this method when this connection won't be used again. To call ReleaseObjectPool, first you call the Close method. Listing 5-29 shows how to use ReleaseObject pool.
Listing 5-29 Calling ReleaseObjectPool
// Connection and SQL strings
string ConnectionString = @" Provider= Microsoft.Jet.OLEDB.4.0; "+ "Data source = c:\\ Northwind.mdb ";
string SQL = "SELECT OrderID, Customer, CustomerID FROM Orders";
// create connection object
OleDbConnection conn = new OleDbConnection(ConnectionString );
conn.Open();
// do something
conn.Close();
OleDbConnection.ReleaseObjectPool();
Conclusion
Hope this article would have helped you in understanding Understanding Connection Pooling in ADO .NET. See my other articles on the website on ADO.NET.
 |
This essential guide to Microsoft's ADO.NET overviews C#, then leads you toward deeper understanding of ADO.NET. |