Understanding Connection Pooling in ADO .NET

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

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:

Dim ConnString As [String] = "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
Dim ConnectionString1 As String = "Integrated Security = SSPI;" & "Initial Cataog = Northwind; " & "Data Source "localhost;"
        Dim conn1 As New SqlConnection(ConnectionString1)
 
        ' create a conenction object
        Dim ConnectionString2 As String = "Integrated security = SSPI;" & "Initial catelog= pubs;" & " Data source = localhost;"
        Dim conn2 As 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
 

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
        Dim ConnectionString As String = " Provider= Microsoft.Jet.OLEDB.4.0; " & "Data source = c:\ Northwind.mdb "
        Dim SQL As String = "SELECT OrderID, Customer, CustomerID FROM Orders"

        ' create connection object
        Dim conn As 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.