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

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. 

using( SqlConnection conn = new SqlConnection("CONNECTION_STRING_HERE") )
{
	try
	{		
		conn.Open();

		//Perform DB operation here i.e. any CRUD operation 
	}
	catch (Exception ex)
	{
		//Handle exception, perhaps log it and do the needful
	}
}	//Connection will autmatically be closed here always

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.

class MyClass : IDisposable
{
    //The managed resource handle
    SqlConnection Conn = null;    

    public MyClass(string path)
    {
        SqlConnection conn = new SqlConnection("CONNECTION_STRING_HERE");
        conn.Open();         
    }    
            
    public void Dispose()
    {      
        Dispose(true);
        GC.SuppressFinalize(this);
    }

    protected virtual void Dispose(bool disposing)
    {       
        if (disposing == true)
        {
            conn.Close(); // call close here to close connection
        }
    }        

    ~MyClass()
    {        
        Dispose(false);
    }
}

With the above class implementing IDisposable, the connection will always be closed no matter how the user will use this class. This way no open connections will be left. For more details on implementing IDisposable and details of what is going in the above mentioned code refer the following article: Understanding and Implementing IDisposable Interface - A Beginner's Tutorial[^]

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. 

History

• 18 Aug 2012: First version