ASP.NET - How To Use(Open/Close) Connections Correctly

This small article discusses the right way of handling the Connections in an ASP.NET application.

Introduction

This small article discusses the right way of handling the Connections in an ASP.NET application. There are few times when not closing the connections properly would lead to following exception.

System.InvalidOperationException: 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.

Background

Some time back I had an opportunity to troubleshoot a problem. The problem was in an ASP.NET website. During some mundane data entry operation on a simple web page the above mentioned exception was coming.

I was fairly certain just after looking at this exception that the connection are not being closed properly in the code. So I asked the developer to show me the code and started the code review. Having done the review for 15 minutes the problem was staring right at me. The problem was a class in the APP_Code folder. this class was opening a new connection in its constructor and doing an update operation in some other method. Also, after the update operation the Connection was not being closed (Hoping that since this class will go out of scope the Connection will be closed). With this scenario, the number of Open connections were increasing and after every 15 minutes or so the above exception was being thrown.

Note: The similar problem could happen in Windows applications but it is less likely since only one active session will be there for a windows application and also the class opening the connection will possibly have a single instance and thus only one open connection. On the other hand in an ASP.NET website multiple users can access at the same time and also there could be multiple instances of the class being created with each request or even on every request. Thus causing this issue to have catastrophic effects.

This small article is meant for all such developers who are still following a similar practice. Here I will try to point out the recommended way of handling the Connection.

Using the code

Connections are very limited resources. They should always be handled very carefully and should not be kept open for long time. In fact the Connection should only be opened when it is needed and then should be closed instantly after the use. This means that we open the connection as late as possible and release it as quickly as possible.

The first way to ensure this is use exception handlers. We open a connection in the try block, use it inside the try block and close it in the finally block this will ensure that even if an unhandled error occurs, the connection will be closed in the finally block. If we don’t use this approach and simply close the connection inside try block itself after performing the operation and an unhandled exception occurs, the connection will remain open until the garbage collector disposes of the SqlConnection object. And the connection being a precious and limited resource we should never rely on the garbage collector to close it. It should always be closed deterministically.

SqlConnection conn = new SqlConnection("CONNECTION_STRING_HERE");
try
{
conn.Open();
//Perform DB operation here i.e. any CRUD operation
//Conn.Close(); //This should never be here
}
catch (Exception ex)
{
//Handle exception, perhaps log it and do the needful
}
finally
{
//Connection should always be closed here so that it will close always
conn.Close();
}

The second and even more recommended way of doing the same thing is by wrapping the data access code in a "using" block. The using statement explicitly declares that we are using a disposable object for some time i.e. only till the using block ends. Once the using block is over the CLR will immediately release the corresponding object immediately by calling its Dispose() method. The Dispose() will automatically close the Connection before disposing it.

But still due to some inexplicable reasons you need to open a connection in some class' constructor then the only way to circumvent the Open connections problem is by implementing the IDisposable Pattern. Following code snippet shows how to do that the right way.

Following all the above guidelines will ensure that the above mentioned exception will not come due to useless open connections.

Point of interest

This article contain a very basic concept and most developers might already know all this stuff. But for those developers who find such things still very confusing, perhaps this post will be a little helpful.

Share

About the Author

I Started my Programming career with C++. Later got a chance to develop Windows Form applications using C#. Currently using C#, ASP.NET & ASP.NET MVC to create Information Systems, e-commerce/e-governance Portals and Data driven websites.

My interests involves Programming, Website development and Learning/Teaching subjects related to Computer Science/Information Systems. IMO, C# is the best programming language and I love working with C# and other Microsoft Technologies.

Twenty years from now you will be more disappointed by the things that you didn't do than by the ones you did do. So throw off the bowlines. Sail away from the safe harbor. Catch the trade winds in your sails. Explore, Dream. Discover.