| [ Team LiB ] |
|
Recipe 1.16 Setting Connection Pooling OptionsProblemYou need to know the different connection pooling options and how you can control them. SolutionUse 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:
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( );
}
DiscussionThe following subsections describe how to control connection pooling for SQL Server, Oracle, OLE DB, and ODBC .NET data providers. SQL ServerThe connection string attributes that control connection pooling for the SQL Server .NET data provider are described in Table 1-6.
OracleThe connection string attributes that control connection pooling for the Oracle .NET data provider are described in Table 1-7.
OLE DBThe 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:
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:
ODBCThe 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:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\<Driver_Name>\CPTimeout
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Connection Pooling You can control ODBC connection pooling in three ways:
|
| [ Team LiB ] |
|