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

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 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();
    }

Method 2

you should close connections as soon as you operations finished. Try to open connections for the shortest time possible.
However it is best to use using it will call Dispose method even in case of exceptions.

using (SqlConnection conn= new SqlConnection(conStr))
{
     //etc...
}

OR

1) Open the connection

2) Access the database

3) Close the connection

 //conn.Open();

        try
        {
          conn.Open();
          //Your Code

        }
        finally
        {
           conn.Close();   
           conn.Dispose();//Do not call this if you want to reuse the connection
        }

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0