[ Team LiB ]
Recipe 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: