Using Connection Pooling in ADO.NET

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

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.