Setting Connection Pooling Options

[ 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.
Nội dung trích xuất từ tài liệu:
Setting Connection Pooling Options[ Team LiB ]Recipe 1.16 Setting Connection Pooling OptionsProblemYou need to know the different connection pooling options and how you can controlthem.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: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 constantsusing 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 .NETdata provider are described in Table 1-6. Table 1-6. SQL Server connection string pooling attributes Attribute Description Length of time in seconds after creation after which a connection isConnection destroyed. The default is 0 indicating that connection will have theLifetime maximum time-out.Connection Specifies whether the connection is reset when removed from the pool.Reset The default is true. Specifies whether the connection is automatically enlisted in the currentEnlist transaction context of the creation thread if that transaction context exists. The default is true. Maximum number of connections allowed in the pool. The default isMax Pool Size 100. Minimum number of connections maintained in the pool. The default isMin Pool Size 0. Specifies whether the connection is drawn from a pool or whenPooling necessary created and added to a pool. The default is true.OracleThe connection string attributes t ...