ADO.NET Cookbook

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.

The sample code contains a method and four event handlers:

Form.Load

Creates a Connection, attaches an event handler to its StateChange event, and sets default properties for controls on the form that are used to specify connection properties. The UpdateConnection( ) method is called to dynamically construct a connection string from the specified properties.

UpdateConnectionString( )

This method dynamically constructs a connection string from the connection string properties specified by the user in text boxes on the form. This method is called to update the connection string when the user changes the value of any of the controls used to specify connection string properties.

Open Button.Click

Opens the Connection that is based on the connection string constructed in the UpdateConnectionString( ) method.

Close Button.Click

Closes the connection string.

Connection.StateChange

Displays original and current state information about the connection when its state changes.

The C# code is shown in Example 1-11.

Example 1-11. File: ConnectionPoolingOptionsForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;

private SqlConnection conn;

//  . . . 

private void ConnectionPoolingOptionsForm_Load(object sender,
    System.EventArgs e)
{    
    conn = new SqlConnection( );
    conn.StateChange += new StateChangeEventHandler(conn_StateChange);

    connectionStringTextBox.Text =
        ConfigurationSettings.AppSettings["Sql_ConnectString"];
    connectTimeoutTextBox.Text = "15";
    connectLifetimeTextBox.Text = "0";
    minPoolSizeTextBox.Text = "0";
    maxPoolSizeTextBox.Text = "100";
    poolCheckBox.Checked = true;

    UpdateConnectionString( );
}

private void UpdateConnectionString( )
{
    connectionStringTextBox.Text =
        ConfigurationSettings.AppSettings["Sql_ConnectString"] +
        "Connection Timeout = " + connectTimeoutTextBox.Text + ";" +
        "Connection Lifetime = " + connectLifetimeTextBox.Text + ";" +
        "Min Pool Size = " + minPoolSizeTextBox.Text + ";" +
        "Max Pool Size = " + maxPoolSizeTextBox.Text + ";" +
        "Pooling = " + poolCheckBox.Checked.ToString( );
}

private void openButton_Click(object sender, System.EventArgs e)
{
    try
    {
        conn.ConnectionString = connectionStringTextBox.Text;
        conn.Open( );
    }
    catch(SqlException ex)
    {
        MessageBox.Show("ERROR: " + ex.ToString( ), "Open Connection",
            MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    catch(InvalidOperationException ex)
    {
        MessageBox.Show("ERROR: " + ex.ToString( ), "Open Connection",
            MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

private void closeButton_Click(object sender, System.EventArgs e)
{
    conn.Close( );
}

private void conn_StateChange(object sender, StateChangeEventArgs e)
{
    connectionStateTextBox.Text =
        "Connection.StateChange event occurred" +
        Environment.NewLine +
        "OriginalState = " + e.OriginalState.ToString( ) +
        Environment.NewLine +
        "CurrentState = " + e.CurrentState.ToString( );
}

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

Table 1-6. 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 time-out.
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.
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-7.

Table 1-7. 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 time-out.
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.15.

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's CLSID>. 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}

Some OLE DB provider configuration options set by registry entries are:

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

• Editing the registry settings described above.

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