ADO.NET 3.5 Cookbook, 2nd Edition

1.16. Setting Connection Pooling Options

Problem

You need to know the different connection pooling options and how you can control them.

Solution

Use the connection string to control connection pooling for the SQL Server, OLE DB .NET, Oracle, or ODBC .NET data provider. Note that connection pooling does not work in the debugger regardless of whether build configuration is set to Debug or Release.

The solution creates two connection strings, each with different connection pooling options. Each connection is opened and closed. In each case, an event handler is attached to the StateChange event to monitor changes in connection state.

The C# code in Program.cs in the project SetConnectionPoolingOptions is shown in Example 1-17.

Example 1-17. File: Program.cs for SetConnectionPoolingOptions solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace SetConnectionPoolingOptions
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            SqlConnection connection = new SqlConnection(  );

            // Set up the event handler to detect connection state change
            connection.StateChange +=
                new StateChangeEventHandler(connection_StateChange);

            // Set the connection string with pooling options
            connection.ConnectionString = sqlConnectString +
                "Connection Timeout=15;Connection Lifetime=0;" +
                "Min Pool Size=0;Max Pool Size=100;Pooling=true;";

            // Output the connection string and open/close the connection
            Console.WriteLine("Connection string = {0}",
                connection.ConnectionString);
            Console.WriteLine("-> Open connection.");
            connection.Open();
            Console.WriteLine("-> Close connection.");
            connection.Close();

            // Set the connection string with new pooling options
            connection.ConnectionString = sqlConnectString +
                "Connection Timeout=30;Connection Lifetime=0;" +
                "Min Pool Size=0;Max Pool Size=200;Pooling=true;";

            // Output the connection string and open/close the connection
            Console.WriteLine("\nConnection string = {0}",
                connection.ConnectionString);
            Console.WriteLine("-> Open connection.");
            connection.Open();
            Console.WriteLine("-> Close connection.");
            connection.Close();

            Console.WriteLine("\nPress any key to continue.");
            Console.ReadKey();
        }

        static void connection_StateChange(object sender, StateChangeEventArgs e)
        {
            Console.WriteLine("\tConnection.StateChange event occurred.");
            Console.WriteLine("\tOriginalState = {0}", e.OriginalState.ToString());
            Console.WriteLine("\tCurrentState = {0}", e.CurrentState.ToString());
        }
    }
}

The output is shown in Figure 1-18.

Discussion

The following subsections describe how to control connection pooling for SQL Server, Oracle, OLE DB, and ODBC .NET data providers.

SQL Server

The connection string attributes that control connection pooling for the SQL Server .NET data provider are described in Table 1-3.

Figure 1-18. Output for SetConnectionPoolingOptions solution

Table 1-3. SQL Server connection string pooling attributes

Attribute	Description
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	Specifies whether the connection is drawn from a pool or, when necessary, created and added to a pool. The default is true.

Oracle

The connection string attributes that control connection pooling for the Oracle .NET data provider are described in Table 1-4.

Table 1-4. Oracle connection string pooling attributes

Attribute	Description
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.
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.
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	Specifies whether the connection is drawn from a pool or, when necessary, created and added to a pool. The default is true.

OLE DB

The OLE DB .NET data provider uses resource-pooling support provided by the OLE DB Service component. You can override the default OLE DB provider services by specifying a value for the OLE DB Services attribute in the connection string. For more information, see Recipe 1.17, next.

OLE DB Resource pooling configuration is controlled using registry entries. There is no user interface to configure these entries—the registry must be edited directly. The registry entries are identified by the <Provider'sCLSID>. CLSID values for some Microsoft OLE DB providers are:

• SQLOLEDB (SQL Server):

  	HKEY_CLASSES_ROOT\CLSID\{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}

• Microsoft.Jet.OLEDB.4.0 (Jet):

  	HKEY_CLASSES_ROOT\CLSID\{dee35070-506b-11cf-b1aa-00aa00b8de95}

• MSDAORA (Oracle):

  	HKEY_CLASSES_ROOT\CLSID\{e8cc4cbe-fdff-11d0-b865-00a0c9081c1d}

• MSDASQL (OLE DB Provider for ODBC):

  	HKEY_CLASSES_ROOT\CLSID\{c8b522cb-5cf3-11ce-ade5-00aa0044773d}

An OLE DB provider configuration option set by registry entries is:

	HKEY_CLASSES_ROOT\CLSID\<Provider's CLSID>\SPTimeout

The session pooling timeout is the number of seconds that an unused session remains in the pool before timing out and being closed. This is a DWORD value with a default of 60 if the registry entry is not specified.

The following registry entries are global to all providers:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess\Session Pooling\Retry Wait

The amount of time that the service component will wait until attempting to contact the server again in the event of a failed connection attempt. This is a DWORD value with a default of 64 if no registry value is present.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\DataAccess\Session Pooling\ExpBackOff

Determines the factor by which the service components will wait between reconnect attempts in the event of a failed connection attempt. This is a DWORD value with a default of 2 if no registry value is present.

HKEY_CLASSES_ROOT\CLSID\{2206CDB0-19C1-11D1-89E0-00C04FD7A829}

A DWORD value that specifies the maximum lifetime in seconds of a pooled connection. The default is 600. The CLSID is for the MSDAINITIALIZE component, which is the OLE DB service component manager that is used to parse OLE DB connection strings and initialize the appropriate provider.

ODBC

The ODBC .NET data provider uses the connection pooling support provided by the ODBC Driver Manager (DM). Connection pooling is supported by version 3.0 or later of the ODBC DM; the version of the ODBC driver does not matter.

The following two registry settings control ODBC connection pooling:

Wait Retry

The time in seconds that that the pool is blocked when the server is not responding. This setting affects all applications using the ODBC driver. The registry key specifies a REG_SZ value:

	HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\<Driver_Name>\CPTimeout

CPTimeout

The time in seconds that unused connections remain in the pool. This setting affects all ODBC drivers on the system. The registry key specifies a REG_SZ value:

	HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Connection Pooling

You can control ODBC connection pooling in three ways:

• Using the ODBC Data Source Administrator to enable or disable pooling for the entire driver, and to control the CPTimeout and Wait Retry settings.

• Using the ODBC API to control pooling options from an ODBC application. For more information about the ODBC API, see the ODBC Programmer's Reference in the MSDN Library.

• Editing the registry settings described previously.