Ways to prevent connection/resource leaks - Part 1

You may use these techniques to handle any expensive resource leaks, not just for connection leaks. In Part 2, I am explaining how to fix resource leaks with structuremap. Also I am explaining evils of singleton object in a multi-threaded environment.

Preface:
This article describes ways to prevent connection leaks (i.e. SqlConnection leaks) when using DataReader.

In case of dataset you don’t have to open connection explicitly, to retreive data, the dataset automatically opens and closes the connection even if the connection's current state is closed. In most of the situation we tend to have our own application specific wrapper/API class for ADO.Net operations. Since dataset follows disconnected architecture, the connection can be easily closed in the wrapper/API class’s method itself. The problem lies in DataReader, here I am explaining two simple ways to prevent resource leaks for those situations

Fix for classic application



a. If your application is not using ORM like NHibernate.
b. If your application is not using DI/IOC tool like StructureMap.



Solution 1 - Traditional way


Use “using” statement to dispose connection/DataReader objects.
Code:

protected void PrintData()
{ 
    string connString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;";
    using (SqlConnection conn = new SqlConnection(connString))
    {
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandText = "SELECT ID, Name FROM Customers";

        conn.Open();

        using (SqlDataReader dr = cmd.ExecuteReader())
        {
            while (dr.Read())
            Console.WriteLine("{0}\t{1}", dr.GetString(0), dr.GetString(1));
        }//DataReader will be disposed
    } //Connection object will be disposed
}


Note:
Avoid try/catch/finally block to dispose your object, I feel “using” makes my code much cleaner. "using” also disposes the object in the case of exception.



Solution 2 – Disposing at Application_EndRequest


This solution will be optimal when
a.If you are using a wrapper class to operate objects
b.If your application is very old and missed to close the connection in many places. The refactor may take long time, here is the quick solution.
Code:

//Cache the connection object in the wrapper/API when they are getting created
public void CacheConnectionObject(SqlConnection connection)
{ 
    if (HttpContext.Current != null)
    {
        lock (_ConnectionSyncLock_) //Ensuring thread safe
        {
            IList<SqlConnection> connectionList;
            if (HttpContext.Current.Items[StringConstantKeys.SqlConnections] == null)
            {
                connectionList = new List<SqlConnection>();
            }
            else
            {
                connectionList = (IList<SqlConnection>)HttpContext.Current.Items[StringConstantKeys.SqlConnections];
            }

            connectionList.Add(connection);
            HttpContext.Current.Items[StringConstantKeys.SqlConnections] = connectionList;
        }
    }
}

Code:

//Call this method in the Application_EndRequest event
public void CloseSqlConnectionObjects()
{    
    lock (_ConnectionSyncLock_) //Ensuring thread safe
    {
        if (HttpContext.Current != null && HttpContext.Current.Items[StringConstantKeys.SqlConnections] != null)
        {
            foreach (SqlConnection connection in (IList<SqlConnection>)HttpContext.Current.Items[StringConstantKeys.SqlConnections])
            {
                try
                {
                    if (connection != null)
                    {
                        if (connection.State != ConnectionState.Closed)
                        {
                            connection.Close();
                            //connection.Dispose(); I am not disposing it, since connection pool may reuse the same connection object across the HttpContext.
                        }
                    }
                }
                catch//Intentionally suppressing the errors, because this method called at the end of the request.
                { }
            }
        }
    }
}

Fix for the enterprise application

If your application is fully dependent on ORM



Solution




You don’t have to dispose anything related to connection object. Generally the ORM softwares are well designed to handle connection leaks. In case of NHibernate, it operates database in the context of NHibernate Session. Once the operation is over, it disposes the NHibernate Session as well as the objects associated with it.

Next-Part-2