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

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
        }