ADO .NET Connection Pooling in VB .NET

In this article I will explain Understanding Connection Pooling in ADO .NET
  • 3803

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:

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
 

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
        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.

 

Categories

More Articles

© 2019 DotNetHeaven. All rights reserved.