Configuring Connections and Connecting to Data in Microsoft .NET Framework 3.5

Lesson 3: Working with Connection Pools

This lesson explains what connection pooling is and how to control connection pooling options when creating and configuring connection objects.

What Is Connection Pooling?

Connection pooling allows you to reuse existing connections so you don’t have to continuously create and dispose of connections that have the same configuration. In other words, opening and closing connections that use the same connection string and credentials can reuse a connection that is available in the pool. Typical applications use the same connection objects to continuously fetch and update data from a database. Connection pooling provides a much higher level of performance by eliminating the need for the database to constantly create and dispose of connections.

Connection pools are separated by process, application domain, and connection string. For connection strings that use Integrated Security, a separate pool is created for each unique identity.

Controlling Connection Pooling Options

When you create ADO.NET connection objects, connection pooling is enabled by default. You can control connection pooling behavior (or disable pooling altogether) by setting connection string keywords specific to connection pooling. For example, to specifically disable connection pooling, you set Pooling=False in your connection string. Table 5-7 provides a list of connection string keywords that you can use to control how a specific connection interacts with the connection pool. Not all keywords are available for every provider. For example, the OLE DB provider controls connection pooling (also known as resource or session pooling) based on the value set for the OLE DB Services keyword in the connection string.

Table 5-7 Connection Pooling Connection String Keywords

Name	Default	Description
Connection Lifetime	0	When a connection is returned to the pool, if its creation time was longer than x seconds ago, with x being the value of this property, then the connection is destroyed. Values are in seconds, and a value of 0 indicates the maximum connection timeout.
Connection Reset	True	Determines whether the database connection is reset when being drawn from the pool. For SQL Server 7.0, setting to False avoids making an additional server round trip when obtaining a connection, but the connection state, such as database context, is not being reset.
Enlist	True	If you want to use a connection as part of a transaction, you can set this to True and the pooler will automatically enlist the connection in the creation thread’s current transaction context.
Load Balance Timeout	0	The minimum number of seconds for the connection to live in the connection pool before being destroyed.
Max Pool Size	100	The maximum number of connections allowed in the pool for this specific connection string. In other words, if your application continuously connects to the database, you might need to increase the Max Pool Size. For example, if your application has many users who all use the same connection string and you might need more than 100 connections, you would want to increase the Max Pool Size. This might happen when many users are accessing the database server using a common client or Web page.
Min Pool Size	0	The minimum number of connections allowed in the pool.
Pooling	True	When true, the SqlConnection object is drawn from the appropriate pool or, if it is required, is created and added to the appropriate pool. Recognized values are True, False, Yes, and No.

In addition to connection string properties that control connection pooling behavior, there are also methods available on connection objects that can affect the pool as well. You typically use the available methods when you are closing connections in your application and you know they will not be used again. This clears the connection pool by disposing of the connections instead of returning them to the pool when they are closed. Any connections that are already in the pool and open will be disposed of the next time they are closed. Table 5-8 lists the available methods for interacting with connection pools.

Table 5-8 Connection Pooling Specific Methods

Name	Object	Description
ClearAllPools	SqlConnection and OracleConnection	Empties all connection pools for a specific provider
ClearPool	SqlConnection and OracleConnection	Empties the connection pool associated with the specified connection
ReleaseObjectPool	OleDbConnection and OdbcConnection	Indicates that the object pool can be released when the last underlying connection is released

Configuring Connections to Use Connection Pooling

By default, all .NET Framework Data Providers available in ADO.NET have connection pooling turned on, but the level of control available for working with connection pooling varies based on the provider being used.

Configuring Connection Pooling with SQL Server Connections

By default, the SqlConnection object automatically uses connection pooling. Each time you call SqlConnection.Open with a unique connection string, a new pool is created. You control connection pooling behavior by setting the connection pool keywords in the connection string, as described earlier in Table 5-7. For example, consider a connection where you want to set the minimum pool size. By assigning a value greater than zero to the Min Pool Size keyword, you ensure that the pool is not destroyed until after the application ends. To set the minimum pool size to 5, use a connection string similar to the following:

Data Source=SqlServerName;Initial Catalog=DatabaseName;
    Integrated Security=True;Min Pool Size=5

The minimum pool size is 0 by default, which means that each connection needs to be created and initialized as it is requested. By increasing the minimum pool size in the connection string, the indicated number of connections are created immediately and are then ready to use, which can reduce the time it takes to establish the connection on those initial connections.

Configuring Connection Pooling with OLE DB Connections

The OLE DB connection object (OleDbConnection) automatically pools connections through the use of OLE DB session pooling. You control how OLE DB connections use pooling by adding an OLE DB Services keyword to the connection string and setting its value based on the combination of services you want to enable or disable for the connection.

The following connection strings explicitly enable connection pooling by setting the OLE DB Services keyword to -1.

OLE DB connection string for an Office Access database (assumes the Nwind.mdb file exists in the following path: C:\DataSources\Nwind.mdb):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DataSources\Nwind.mdb;
    OLE DB Services=-1

OLE DB Connection for a SQL Server database (replace ServerName and DatabaseName with valid values for your data source):

Provider=SQLOLEDB;Data Source=ServerName;OLE DB Services=-1;
    Integrated Security=SSPI;Initial Catalog=DatabaseName

The following connection strings disable connection pooling and automatic transaction enlistment by setting the OLE DB Services keyword to -4:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\DataSources\Nwind.mdb;OLE DB Services=-4

Table 5-9 lists the OLE DB Services values to set in an OLE DB connection string.

Table 5-9 OLE DB Connection String Settings for OLE DB Services

OLE DB Service	Connection String Keyword/Value
All services on	“OLE DB Services = -1;”
All services except Pooling and AutoEnlistment of transactions	“OLE DB Services = -4;”
All services except Client Cursor	“OLE DB Services = -5;”
All services except Pooling, AutoEnlistment, and Client Cursor	“OLE DB Services = -8;”
No services (all services disabled)	“OLE DB Services = 0;”

Configuring Connection Pooling with ODBC Connections

To enable or disable connection pooling for connections that use the ODBC connection object (OdbcConnection), you must use the ODBC Data Source Administrator dialog box in Windows.

Accessing the ODBC Data Source Administrator Dialog Box

Access the ODBC Data Source Administrator dialog box by performing the following steps:

1. In the Administrative Tools folder on your Start menu, open Data Sources (ODBC).

2. Click the Connection Pooling tab.

3. Double-click the driver from the list of available ODBC drivers that you want to set connection pooling options for.

4. In the Set Connection Pooling Attributes dialog box, select the option to either pool connections or not pool connections. If you select the option to pool connections, you can also set the number of seconds for unused connections to remain in the pool (the connection lifetime).

5. Click OK to save the settings and repeat for other drivers if desired.

Configuring Connection Pooling with Oracle Connections

Connections that use the .NET Framework Data Provider for Oracle automatically use connection pooling by default. You can control how the connection uses pooling by setting connection string keywords.

Table 5-10 describes the connection string keywords available for altering connection pooling activities.

Table 5-10 Oracle Connection String Settings for Connection Pooling

Name	Default	Description
Connection Lifetime	0	When a connection is returned to the pool, its creation time is compared with the current time and the connection is destroyed if that time span exceeds the value specified. Values are in seconds and a value of 0 indicates the maximum connection timeout.
Enlist	True	When true, the pooler automatically enlists the connection in the creation thread’s current transaction context. Recognized values are True, False, Yes, and No.
Max Pool Size	100	The maximum number of connections allowed in the pool.
Min Pool Size	0	The minimum number of connections allowed in the pool.
Pooling	True	When true, the OracleConnection object is drawn from the appropriate pool or, if it is required, is created and added to the appropriate pool.

Lesson Summary

• Connection pooling is enabled by default.

• Connection pooling options are set in the connection string except for the ODBC provider, which uses the ODBC Data Source Administrator dialog box in Windows.

Lesson Review

The following questions are intended to reinforce key information presented in this lesson. The questions are also available on the companion CD if you prefer to review them in electronic form.

1. What determines the connection pool that a connection should use? (Choose all that apply.)

   1. A connection string

   2. The identity or credentials of the user opening the connection

   3. The database being connected to

   4. The connection object used to connect to the database

2. What are the recommended techniques for enabling connection pooling on for a SQL Server 2000 or SQL Server 2005 database? (Choose all that apply.)

   1. Setting the OLE DB Services connection string keyword to -4

   2. Opening a connection and not explicitly disabling pooling

   3. Setting the connection string keyword Pooling = True in the connection string

   4. Using the Connection Pooling tab of the ODBC Data Source Administrator dialog box

3. How do I explicitly turn on connection pooling for an OLE DB data source?

   1. By setting the OLE DB Services connection string keyword to 0

   2. By setting the OLE DB Services connection string keyword to -4

   3. By setting the OLE DB Services connection string keyword to -1

   4. By setting the OLE DB Services connection string keyword to -7