In any software endeavor, the most expensive operation is that of connecting the application’s business logic and presentation code to the database. To streamline the overhead caused by these costly operations, the .NET framework natively supports connection pooling. By utilizing a specified set of connections and pooling them between multiple sessions or clients, developers can increase the scalability and extensibility of their applications. When applications or sessions share a single connection, the overhead of allocating a separate connection for each request to the database and then destroying it is eliminated.

Connection Pool Basics

When the first client connects using an application in the .NET framework, a connection pool is created and a database connection is instantiated. Connection pooling is enabled by default in the .NET framework. One important element to remember is that the .NET framework will automatically pool connections based on the distinct connection string of the connection object. This means that each client application requesting a database connection must have a connection object that uses the exact same database connection string. For example, the following code outlines two connection objects in different portions of the application.

‘Connection Attempt 1 

Dim cString As string = "Persist Security Info=False;Integrated
Security=SSPI;database=northwind;server=mySQLServer"
Dim cnNorthwind As SqlConnection = new SqlConnection(cString)

‘Connection Attempt 2

Dim cString As string = "Persist Security Info=False;Integrated
Security=SSPI;database= northwind;server=mySQLServer"
Dim cnNorthwind As SqlConnection = new SqlConnection(cString)

Connections that Won’t Pool

In this example, the .NET framework will not pool these connections. This is due to the fact that the connection strings in both attempts are different. Notice that this first connection attempt has no space after the database clause and the second attempt does (database=northwind Vs database= northwind). This example illustrates that this minor difference between otherwise identical connection strings will prohibit the framework from pooling the connections.

This is an important aspect of design to remember. And because of this, it is important to attempt to have the application store the connection string in a centralized location, then reuse it in multiple connection attempts (i.e., in WebConfig for ASP.NET). This will ensure correct connection pooling compatibility.

Once the framework creates the pool of connections, it instantiates the pool and places connection objects in the pool for immediate use. The number of connections initially created and placed into the pool is determined by the minimum number of connections setting for the application’s connection pool. If the application requires more connections than the pool has available concurrently, the framework will add connections to the pool, up to the maximum number of connections configured.

Conversely, connections are removed from the pool when the framework detects that the connection has become inactive. Inactivity is determined by whether or not the connection has outlived its specified lifetime or server connectivity is interrupted between the database server and application/application server.

From the SQL Server side, there are methods to indicate whether or not connection pooling is enabled and working properly. One of these methods is to analyze the output of the SQL Server profiler.

Connection Pool Behavior

When an application’s connection pool reaches its limits, the .NET framework will queue all new connection requests until a pooled connection becomes available. From a client perspective, this means that the application will wait (hang) until a pooled connection becomes available. It is important for application architects and developers to become aware of this fact and prepare for it. Essentially, the application must know what to do in the eventuality that there are no open pooled connections. Use of a Try, Catch Finally block will be instrumental in handling this circumstance. According to the MSDN documentation, a connection becomes usable in the pool when it is unused by any other process and has a compatible transaction context. The method to return a connection to the pool is to call either call the connection’s close or dispose methods.

Essential to developing an application that does not leak connections (i.e., leave connections open and consume a pool) is to gain an understanding about how to manage connections. The following section outlines application connection management.

Managing Connections in an Application

The correct management of application connections is vital to ensuring the optimum performance and scalability of a solution. In order to make correct use of the connection pool, developers must take care to clean up application objects correctly, in accordance with the way the new .NET framework deals with the connection object.

Unlike in Visual Basic 6.0, the connection object does not get destroyed once it falls out of scope. Developers must make sure that the connection object is specifically closed and disposed of in order to return the connection to the pool. Otherwise, application errors generated from the .NET framework will arise when a piece of code goes after a connection object that is still open and exclusively held by another object (i.e., a datareader).

Close Those Connections

Microsoft states that any connection that has not been explicitly closed may not return to the connection pool. Under certain conditions, this also pertains to connections that have fallen out of scope. As a result, developers must take care to manage their connections diligently. In many applications, developers often use a single connection object which is opened and closed by each method, class or sub that requires database operations.

A best practice is to enforce connection state in the data access block of the application. By having the data access class ensures that connection state is managed according to the rules of connection pooling, developers will no longer have to manually ensure that connections are closed across the entire application. The data access block should be aware of a singular connection string or connection object. When returning datasets or datareaders, the data access object should ensure that the connection is closed correctly.

This is specifically relevant with regard to the datareader object. Without having a data access block understand and mange the connection object, developers must call the SqlDataReader.Close method to ensure that the reader releases its connection. Also, the developer must explicitly call the Connection.Close method in order to explicitly release the connection back to the connection pool. This is due to the fact that datareaders need to have an active connection (open connection) to the data source to begin reading.

An alternative to this approach is to use a data access block to ensure that the CommandBehavior.CloseConnection option is set so that when the SQLDataReader.Close method is called, the connection is released automatically. By doing so, the code beyond the data access layer does not have to be concerned with connection management. Conversely, if developers are managing the connection external to a data access block, then they must set this behavior explicitly in their code.

Connection Fallout

In the event that the aforementioned best practices for managing connections are not followed, what the developer and users may notice is that the connection pool runs out of connections rather quickly. This means that while the connection pool is being effective, if the connections that the application is using are never placed back into the pool by calling the close connection method, the application will continue to hold connections alive, while fetching new ones from the pool.

In this happenstance, users will either experience connection timeout errors for the pool, or the application will appear to hang. The application cannot submit database requests until one of the connections that the application has are returned to the pool.

Connections and Application State

In an ASP.NET application, state is exclusively managed through a series of programmatically accessible interfaces (i.e., Application State, Session State, View State, Request Query String, etc.). ASP.NET developers traditionally place objects in session state such that the user of the application obtains a relatively uninterrupted and stateful experience. There are several aspects of state management that are beyond the scope of this subject. However, relating specifically to connections and session state, developers must understand that it is not good practice to contain an ADO.NET Connection object in application state. This act will assuredly short circuit the purpose and function of the connection pool.

While the idea of caching a single connection for reuse throughout the lifetime of the application might seem like a logical extension of connection pooling (i.e., the supposition that one connection for the whole application must be pooled) it will lead to connection contention problems throughout the entire application.

Essentially, storing a shared connection at the scope of the application is equivalent to ensuring that every client connected to the application is using only one connection. This means that if a client is in the middle of a data reader, another client in a separate session will likely receive an exception. This approach circumvents connection pooling as well because the .NET framework only sees one connection object and only allocates one connection from its pool for the entire scope of the application.

--

Eric Charran is currently working as a Technical Architect and Web Developer, in Horsham PA. Eric holds Microsoft Certifications in SQL Server and has significant experience in planning, designing, and modeling n-tier applications using SQL Server 2000, as well as architecting and implementing .NET framework based applications, solutions and web services. Eric's other professional skills encompass Database Administration, Data Warehousing and Application Architecture, Modeling and Design, as well as Data Warehouse Transformation and Population using DTS. Eric is also skilled in developing and implementing ASP.NET and Windows forms applications, using technologies such as COM+, the .NET framework, VB.NET and other .NET languages.