ADO.NET Connection Pooling with C# Code Examples Tutorial

Connecting to a database is the single slowest operation performed by a data-centric application. This document describes how reusing pooled connections, instead of creating new connections, can improve .NET application performance.

You can control connection pooling behavior by using the connection string options set for your ADO.NET data provider. For example, connection string options can define the following settings for the DataDirect Connect® for ADO.NET data providers:

• Number of connection pools (Max Number of Pools)
• Maximum connection pool size (Max Pool Size)
• Minimum number of connections in a connection pool (Min Pool Size)
• Number of seconds to keep connections in a connection pool (Connection Lifetime)
• Whether to enable Windows Integration authentication for the SQL Server data provider (Integrated Security)

This document also describes the performance advantages of DataDirect's technique of handling dead connections in a connection pool, as well as tips on opening and closing connections. In addition, C# code examples illustrate how to create connection pools and how to handle distributed transactions when using a connection pool.

NOTE: Code examples in this document use the ADO.NET 2.0 Common Programming Model and MetaData capabilities introduced in the Microsoft .NET 2.0 Framework. If you are using the .NET Framework 1.x or DataDirect Connect for .NET 2.2 data providers, refer to Connection Pooling in .NET Applications.

Connecting to a database is the single slowest operation performed by a data-centric application. This document describes how reusing pooled connections, instead of creating new connections, can improve .NET application performance.

You can control connection pooling behavior by using the connection string options set for your ADO.NET data provider. For example, connection string options can define the following settings for the DataDirect Connect® for ADO.NET data providers:

• Number of connection pools (Max Number of Pools)
• Maximum connection pool size (Max Pool Size)
• Minimum number of connections in a connection pool (Min Pool Size)
• Number of seconds to keep connections in a connection pool (Connection Lifetime)
• Whether to enable Windows Integration authentication for the SQL Server data provider (Integrated Security)

This document also describes the performance advantages of DataDirect's technique of handling dead connections in a connection pool, as well as tips on opening and closing connections. In addition, C# code examples illustrate how to create connection pools and how to handle distributed transactions when using a connection pool.

Connecting to a database is the single slowest operation inside a data-centric application. With connection pooling, you deliver the optimal environment for database-driven applications. Connection pooling allows you to reuse connections rather than create a new one every time the ADO.NET data provider needs to establish a connection to the underlying database.

Connection pooling behavior can be controlled by using connection string options (see the documentation for your data provider). For example, for most ADO.NET data providers, connection string options can define the number of connection pools, the number of connections in a pool, and the lifetime of pooled connections used by each process.

Connection pooling in ADO.NET is not provided by the core components of the Microsoft .NET Framework. It must be implemented in the ADO.NET data provider itself. All DataDirect ADO.NET data providers provide the same connection pooling functionality. By thoughtfully planning for connection management before implementation, you can improve application performance and maintainability.

Each connection pool is associated with a specific connection string. By default, the connection pool is created when the first connection with a unique connection string connects to the database. The pool is populated with connections up to the minimum pool size. Additional connections can be added until the pool reaches the maximum pool size.

The pool remains active as long as any connections remain open, either in the pool or used by an application with a reference to a Connection object that has an open connection.

If a new connection is opened and the connection string does not exactly match an existing pool, a new pool must be created. By using the same connection string, you can enhance the performance and scalability of your application.

In the following C# code fragment, three new DbConnection objects are created, but only two connection pools are required to manage them. Note that the connection strings for conn1 and conn2 differ by the values assigned for User ID, Password, and Min Pool Size connection string options.

Once created, connection pools are not destroyed until the active process ends or the connection lifetime is exceeded. Maintenance of inactive or empty pools involves minimal system overhead.
  

The DataDirect ADO.NET data provider for Microsoft SQL Server supports Integrated Windows (NTLM) authentication. If the connection string used by a DbConnection object sets both the Integrated Security and Pooling connection options to true, the Domain and User ID information is included with the connection pooling qualification information. In this case, a unique connection string is not the only requirement for creating a pool - instead, a pool is created for each connection string passed by a particular user. This prevents impersonation of one SQL Server user by another through the connection pool.

Because new connection pools are created for each user who connects, consider carefully the values that you assign for the Min Pool Size and Load Balance Timeout connection string options. For example, suppose you have defined a minimum of 100 connections in each pool with a connection lifetime of 60 seconds for each connection. If you have 200 individual users connecting, system resources would be tied up.

A connection pool is created in the process of creating each unique connection string that an application uses. When a pool is created, it is populated with enough connections to satisfy the minimum pool size requirement, which is set by the Min Pool Size connection string option. If an application is using more connections than Min Pool Size, the data provider allocates additional connections to the pool up to the value of the Max Pool Size connection string option, which sets the maximum number of connections in the pool.

When a DbConnection object is requested by the application calling the DbConnection.Open() method, the connection is obtained from the pool, if a usable connection is available. A usable connection is defined as a connection not currently in use by another valid DbConnection object that has a matching distributed transaction context (if applicable).

If the maximum pool size has been reached and no usable connection is available, the request is queued in the data provider. The data provider waits for the value of the Connection Timeout connection string option for a usable connection to return to the application. If this time period expires and no connection becomes available, the data provider returns an error to the application.

IMPORTANT: Closing the connection using the Close() method of the DbConnection object adds or returns the connection to the pool.

A connection is removed from a connection pool and the connection to the database server is terminated when it remains idle (unused) for too long, or when a new connection that has a matching connection string is initiated by the application (DbConnection.Open() is called). The length of time that a connection can remain idle in a connection pool before being removed is determined by the Load Balance Timeout connection string option.

Before returning a connection from the connection pool to an application, the Pool Manager checks to see if the connection has been closed at the server. If the connection is no longer valid, the Pool Manager discards it and returns another connection from the pool, if one is available and valid.

The Clear Pool and Clear All Pools methods of the Connection object, introduced in the .NET Framework 2.0, clear connection pools. ClearPool clears the connection pool associated with a specific connection. In contrast, ClearAllPools clears all of the connection pools used by the data provider. Connections that are in use when the method is called are discarded when they are closed.

Note, however, that the data provider always retains the number of connections specified by the Min Pool Size connection option in a connection pool. So, setting the Min Pool Size connection option greater than 0 means that many connections in a pool effectively will ignore the Load Balance Timeout connection option.

So, what happens when an idle connection loses its physical connection to the database? For example, suppose the database server is rebooted or the network experiences a temporary interruption. When the application attempts to connect using an existing DbConnection object from a pool, it could receive errors because the physical connection to the database has been lost.

DataDirect ADO.NET data providers handle this situation transparently to the user. The application does not receive any errors on the DbConnection.Open() attempt because the data provider simply returns a connection from a connection pool. The first time the DbConnection object is used to execute a SQL statement (for example, through one of the DataReader execution methods or the DataAdapter.Fill method), the data provider detects that the physical connection to the server has been lost and attempts to reconnect to the server before executing the SQL statement. If the data provider can reconnect to the server, the result of the SQL execution is returned to the application; no errors are returned to the application. The data provider uses the connection failover options, if enabled, when attempting this seamless reconnection. For more information about how DataDirect ADO.NET data providers implement connection failover, refer to "Failover Support in DataDirect Connect for ADO.NET Data Providers."

NOTE: Because the data providers can attempt to reconnect to the database server when executing SQL statements, connection errors can be returned to the application on a statement execution. If the data providers cannot reconnect to the server (for example, the server is still down), the execution method throws an error indicating that the reconnect attempt failed, along with specifics about the reason the connection failed.

DataDirect's method of handling dead connections in connection pools allows for the maximum performance of the connection pooling mechanism. In contrast, some data providers periodically ping the server with a dummy SQL statement while the connections remain idle. Others ping the server when the application requests the use of the connection from the connection pool. Both of these approaches add roundtrips to the database server and ultimately slow down the normal operation of the application.

The Pool Manager groups the connections according to the requirement for transactions. If the requesting thread requires a specific transaction context, it must be matched to a connection with the same transaction context, for example, a connection that has been enlisted in distributed transactions.

Because closed connections are returned to the appropriate connection pool, you can close a connection even though a distributed transaction is pending. This means that you can still commit or rollback the distributed transaction until the connection is closed at the server.

Open connections just before they are needed. Opening them earlier than necessary decreases the number of connections available to other users and can increase the demand for resources.

To keep resources available, explicitly close the connection as soon as it is no longer needed. If you wait for the garbage collector to implicitly clean up connections that go out of scope, the connections are not returned to the connection pool immediately, tieing up resources that are not actually being used.

Close connections inside a finally block. Code in the finally block always runs, regardless of whether an exception occurs. This guarantees explicit closing of connections. For example:

If you are using connection pooling, opening and closing connections is not an expensive operation. Using the Close() method of the data provider's Connection object adds or returns the connection to the connection pool. Remember, however, that closing a connection automatically closes all DataReader objects associated with the connection.

All DataDirect Connect for ADO.NET data providers install a set of Performance Monitor (PerfMon) counters that return information that lets you tune your applications for performance.

The following table describes the PerfMon counters that you can use to tune pooling for your application.

Refer to the Microsoft .NET Framework documentation for information about using PerfMon counters.

Thoughtful connection management can improve the performance of .NET applications. Connection pooling, which is implemented in the ADO.NET data provider, allows you to reduce the number of roundtrips to the server, and allocate connections and system resources more effectively. Judiciously defining the number of connection pools, the maximum and minimum pool size, and the length of time the connection remains in the connection pool can help your .NET applications run more efficiently.

After defining the connection string options, you can tune the performance of the application using PerfMon counters. These PerfMon counters can return the current number of connection pools, the number of pooled and non-pooled connections, and the number of connection attempts that failed since the process started.

DataDirect Technologies offers the following ADO.NET data providers built with 100% managed code that support the .NET Framework Version 2.0:

• DataDirect Connect for ADO.NET 3.0 DB2 data provider
• DataDirect Connect for ADO.NET 3.0 Oracle data provider
• DataDirect Connect for ADO.NET 3.0 SQL Server data provider
• DataDirect Connect for ADO.NET 3.0 Sybase data provider

Existing code written for earlier versions of the .NET Framework and earlier versions of DataDirect Connect for .NET is compatible with the 3.0 version of the data providers. Note that the applications must be re-compiled using the .NET Framework 2.0.

However, if your applications require Windows 98 and Windows Me and/or the .NET Framework 1.x, you can use the DataDirect Connect for .NET 2.2 data providers, which DataDirect will continue to make available.