Question

[Solved] 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?

Solution #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();
    }
Respondent: user2830395
Solution #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
        }
Respondent: DGibbs
The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .

Most Popular

To Top
India and Pakistan’s steroid-soaked rhetoric over Kashmir will come back to haunt them both clenbuterol australia bossier man pleads guilty for leadership role in anabolic steriod distribution conspiracy