c# - The connection was not closed, The connection's current state is open

Question

I'm writing an ASP.NET application. In my datalayer an sql connection is being opened and closed before and after querying. The SqlConnection is being kept as a private field of a single class. Every database call in the class uses the same structure:

        conn.Open();

        try
        {
            // database querying here
        }
        finally
        {
            conn.Close();
        }

Yet, on very rare occasions I get the exception 'The connection was not closed. The connection's current state is open'. It's not possible to reproduce the problem since it originates very rarely from different parts of the code. There is some threading involved in my application but new threads also make new data layer classes and thus new connection objects.

I do not understand how it's possible to have a connection lingering around open using the code above. Shouldn't the connection always be closed after opening, making it impossible for the above exception to occur?

Answer 1

It's likely that an exception is being thrown in the try block that you aren't handling. See this note in MSDN for try-finally:

Within a handled exception, the associated finally block is guaranteed to be run. However, if the exception is unhandled, execution of the finally block is dependent on how the exception unwind operation is triggered.

I would recommend wrapping the connection in a using block anyway:

using (SqlConnection connection = new SqlConnection(connectionString))
{
     //etc...
}

Alternatively, add a catch block to the try-finally:

    conn.Open();

    try
    {

    }
    catch
    {

    }
    finally
    {
        conn.Close();
    }