Using Connection Pooling in ADO.NET

Introduction

Performance is an important consideration in modern software systems. Developers look for each and every opportunity to fine tune their applications for performance. To that end, ADO.NET connection pooling can significantly help you to write data access code that is performance oriented. Although enabled by default, one must know how to take control and fine tune the connection pooling to make the most of the feature.

What Is Connection Pooling?

Data-driven applications frequently access a database to query data. There are two broad ways to access the data:

1. Open a connection with the database as your application starts and keep it open throughout the life of your application. Fire all the queries through this open connection.
2. Open a connection just before executing a query and close it immediately once the query execution is over.

As you might have guessed, the former way is better as far as performance is concerned. However, it suffers from a serious limitation. Because one connection is held open for a large window, in terms of scalability it is extremely poor. In multiuser scenarios, such as web sites, this will restrict the total number of users who can access your application.

The second way is safe and follows the philosophy—”Open the connection as late as possible and close the connection as early as possible.” Although this approach is good in terms of scalability and multiuser scenarios, it has its own disadvantage. The frequent creation, opening, closing, and destroying of the database connections results in performance penalty. This is precisely where database connection pooling come handy.

A database connection pool is a set of database connections that are held open with the database and are kept ready to serve. This way, a set of connections is created once the pool comes into existence. When a user requests a connection, an already created connection is served from the pool. Similarly, when a user closes a connection, it is returned to the pool instead of being destroyed. This can significantly improve the performance of data access.

Connection Pooling in ADO.NET

In ADO.NET, database connection pooling is enabled by default. You can configure various aspects of connection pooling via a connection string. The following table lists some of the important parameters related to connection pooling:

A connection pool gets created when the first connection is opened with the database.

Some Rules

To get the most out of database connection pooling, it is important to remember the following rules:

• If you want to pool a set of connections, all of them must have the same connection string.
• All the connections must have the same security context. If you are using Windows integrated security, the Windows user responsible for starting the application process is used to determine the security context.
• All the connections must belong to the same process.

Testing Connection Pooling Behavior

Now that you have some idea about what connection pooling is, you can develop a simple application to verify the learned facts.

Begin by creating a new Windows Application and design its default form as shown below:

Figure 1: The default form

The form contains a checkbox control that governs whether or not connection pooling is enabled. If enabled, you also can specify the minimum pool size and maximum pool size. Clicking on the “Open a connection” button opens a database connection so that a connection pool is created. To test the behavior of MaxPoolSize attribute, you open multiple connections with the database at a time. The number of connections to open can be specified in the relevant textbox.

Now, go in the code window of the form class and add a helper method named GetConnectionString() as shown below:

private string GetConnectionString()
{
   SqlConnectionStringBuilder builder =
      new SqlConnectionStringBuilder();
   builder.DataSource = "localhost";
   builder.InitialCatalog = "northwind";
   if (checkBox1.Checked)
   {
      builder.Pooling = true;
      builder.MinPoolSize = int.Parse(textBox2.Text);
      builder.MaxPoolSize = int.Parse(textBox3.Text);
   }
   else
   {
      builder.Pooling = false;
   }
   builder.UserID = "sa";
   builder.Password = "pass@word";
   return builder.ConnectionString;
}

The GetConnectionString() method returns a string representing the database connection string. Inside, it uses the SqlConnectionStringBuilder built-in class to build the connection string. It is assumed that you have Northwind database installed on your SQL server. The code simply sets properties of SqlConnectionStringBuilder class. Notice the Pooling, MinPoolSize, and MaxPoolSize properties. These properties will add the appropriate connection string attributes.

Now, add the following code in the Click event handler of “Open a connection” button:

private void button3_Click(object sender, EventArgs e)
{
   string connstr = GetConnectionString();
   SqlConnection cnn = null;
   try
   {
      cnn = new SqlConnection(connstr);
      cnn.Open();
      MessageBox.Show("Connection Opened!r
                       nConnection String:rn" + connstr);
   }
   catch (Exception ex)
   {
      MessageBox.Show(ex.Message);
   }
   finally
   {
      cnn.Close();
   }
}

Here, you retrieve the database connection string by using your helper method GetConnectionString(). Then, you open a connection with the Northwind database. Just to see how the connection string has been built, you display it in a message box. Note the use of try-catch-finally block. This guarantees that the connection is always closed (even if there is any exception) and returned to the pool.

Then, handle the Click event of the “Open” button as shown below:

private void button1_Click(object sender, EventArgs e)
{
   SqlConnection[] conn = null;
   try
   {
      int count = int.Parse(textBox1.Text);
      conn = new SqlConnection[count];
      string connstr = GetConnectionString();
      for (int i = 0; i < count; i++)
      {
         conn[i] = new SqlConnection(connstr);
         conn[i].Open();
      }
      MessageBox.Show("Connection Opened!r
                       nConnection String:rn" + connstr);
   }
   catch (Exception ex)
   {
      MessageBox.Show(ex.Message);
   }
   finally
   {
      for (int i = 0; i < count; i++)
      {
         if (conn[i] != null)
         {
            conn[i].Close();
         }
      }
   }
}

Here, you declare an array of SqlConnection objects. Depending on the number of connections to open (as specified in the textbox), a for loop instantiates and opens the connections. The finally block simply closes them again.

Now, it’s time to run the application and observe the connection pooling behavior. Run the application and uncheck the “Enable connection pooling” checkbox to disable the connection pooling. Then, click the “Open a connection” button. If the connection is opened successfully, you should see a message box as shown below:

Figure 2: The connection has opened

Notice how the Pooling attribute of the connection string is set to False. Keeping the message box open, run SQL Server Management Studio and execute the SP_WHO stored procedure.

EXEC SP_WHO

The results of SP_WHO stored procedure are shown below:

Figure 3: The results of the SP_WHO stored procedure

Notice the highlighted row. This indicates your open database connection. Because the connection pooling is disabled, only one database connection got created. Now, click the OK button of the message box so that the connection is closed. Run the SP_WHO stored procedure again. This time, you will find that the connection has vanished; this indicates that closing the connection also closes the database connection.

Now, repeat the same process by keeping the “Enable connection pooling” checkbox checked. Here are the results of SP_WHO:

Figure 4: The results of SP_WHO

This time, although you open just one connection, five connections are opened with the database! This is because you have set the MinPoolSize attribute to 5. Moreover, these connections are held open even if you click the OK button of the message box. That means even if you close the connections from your code, they are returned to the pool without actually closing the database connections.

Now, run a second instance of your application and repeat the above process. The SP_WHO now reports 10 open connections. This is because a connection pool is maintained for a process.

At last, the final test. Enter 20 in the “No. of connections” textbox (recollect that you have set MaxPoolSize to 10) and click the Open button. After 15 seconds, you get an exception as shown below:

Figure 5: The connection was not established

This indicates that, because your number of connections exceed the maximum pool size, there was no connection available to serve the request. The data provider waited for the default timeout period of 15 seconds and then threw an exception.

The Using Block

In the preceding example, you used the finally block to close the database connection. A more elegant way, however, is to use a using block. The following code shows how:

using(SqlConnection cnn=new SqlConnection(connstr))
{
   cnn.Open();
   //some other work
}

Here, you need not call the Close() method at all because the using block automatically calls the Dispose() method on the connection object once the block is complete. The Dispose() method, in turn, does the appropriate cleanup job for you.

Summary

Database connection pooling can significantly improve performance. In ADO.NET, it is enabled by default. You can fine tune it using various connection string parameters. The Pooling, MinPoolSize, and MaxPoolSize parameters allow you to configure connection pooling behavior. It is recommend tthat you play with these attributes to arrive at an exact optimized value for your specific application.

Download the Code

You can download the code that accompanies the article here.

About the Author

Bipin Joshi is the proprietor of BinaryIntellect Consulting, where he conducts premier training programs on .NET technologies. He wears many hats, including software consultant, mentor, prolific author, webmaster, Microsoft MVP, and member of ASPInsiders. Having adopted the Yoga way of life, Bipin also teaches Kriya Yoga to interested individuals. His detailed profile can be read on his blog. He also can be reached there.