How to prevent connection pool problems between ASP.NET and SQL Server?

As you already know the connection pooling by ADO.NET reduces the number of new connections to the database. The pooling mechanism reuses the existing connection if available, when a new connection request is made. However, as a developer, if you are not taking precautions while using connection pooling, you may encounter several connection pooling issues and errors. Here I have listed some of the common connection pooling errors and the ways to prevent connection pool problems.

Error 1: Timeout expired error

One of the common error due to improper usage of connection pooling is:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

Reason

The root cause of this error is due to connection leaks. i.e. If you are opening a connection and not closing it properly, then there will be multiple open connections, causing this error.

Error 2: The connection’s current state is open error

This is another connection pool error you may experience:

The connection was not closed. The connection’s current state is open.

Reason

This error usually appears, when you are not closing the connection properly and trying to open it again.

Error 3: The connection’s current state is closed error

ExecuteReader requires an open and available Connection. The connection’s current state is closed.

Reason

This error usually appears when you try to use the ExecuteReader command even before opening a connection using the .Open() method.

Ways to prevent connection pool problems

To avoid all the above errors, you have to properly close the connection, every time you open it. Below are a couple of simple methods to prevent connection pool problems and errors.

Solution 1

Use try/catch/finally blocks whenever you open a connection and manually close the connection in the finally block. Here is an example:

var con = new SqlConnection(Connection_String);
try {
     con.Open();
     ...
     ...
}
catch {
    ...
}
finally {
     con.Close();                
}

Solution 2

Another way to make sure the connection is closed properly is by using a local variable inside a using block. This will make sure the connection object is disposed of automatically. Here is an example:

using(var con = new SqlConnection(Connection_String)) {
     con.Open();
     ...
     ...
}

This method is the best as you do not need to manually close the connection every time.

Reference

• Read more about ADO.NET Connection Pooling at Microsoft Docs.