Improving Connection Pooling Performance with SqlConnection object in ASP.Net C#

Improving Performance with Connection Pooling Database connections are precious resources. If you want your ASP.NET application to scale to handle the demands of thousands of users, then you need to do everything in your power to prevent database connections from being wasted.
Opening a database connection is a slow operation. Rather than open a new database connection each time you need to connect to a database, you can create a pool of connections that can be reused for multiple database queries.

When connection pooling is enabled, closing a connection does not really close the connection to the database server. Instead, closing the connection releases the database connection back into the pool. That way, the next time a database query is performed, a new connection to the database does not need to be opened.

When you use the SqlConnection object, connection pooling is enabled by default. By default, the ADO.NET framework keeps a maximum of 100 connections open in a connection pool.

When taking advantage of connection pooling, it is still very important to close your connections by calling the SqlConnection.Close() method. If you don’t close a connection, the connection is not returned to the pool. It might take a very long time for an unclosed connection to be reclaimed by ADO.NET. Second, different connection pools are created for different connection strings. In particular, a different connection pool is created for each unique combination of connection string, process, application domain, and Windows identity.

You should always store your connection strings in the web configuration file.

The SqlConnection object supports two methods for clearing connection pools programmatically:

• ClearAllPools : Enables you to clear all database connections from all connection pools.
• ClearPool : Enables you to clear all database connections associated with a particular SqlConnection object.

These methods are useful when you are working with a cluster of database servers. For example, if you take a database server down, you can programmatically clear the connection pool to the database server that no longer exists.

You can control how connections are pooled by using the following attributes in a connection string:

• Connection Timeout : Enables you to specify the maximum lifetime of a connection in seconds. (The default value is 0, which indicates that connections are immortal.)
• Connection Reset : Enables you to reset connections automatically when retrieved from the connection pool (default value is True).
• Enlist : Enables you to enlist a connection in the current transaction context (default value is True).
• Load Balance Timeout : Same as Connection Timeout.
• Max Pool Size : Enables you to specify the maximum number of connections kept in the connection pool (default value is 100).
• Min Pool Size : Enables you to specify the minimum number of connections kept in the connection pool (default value is 0).
• Pooling : Enables you to turn on or off connection pooling (default value is True).