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