CODEWITHSUNDEEP
c#asp.net
user2830395
user2830395

Reputation: 123

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?

Upvotes: 12

Views: 54292

Answers (4)

A-Dubb
A-Dubb

Reputation: 1709

When I encountered this issue, it was because I took some code that was never meant to be run in a for loop and made it so.

The problem is that code called Open() on the connection which is fine on the first iteration of the loop as it's the first time connection is opened, but on second iteration, bam!!.

You should be able to reproduce this exception with the following code:

connection.Open();
connection.Open(); // some code somewhere did it again which is an issue

In this case, you should check the state of the connection first if you truly wish to use it and see if it's already open:

if (connection.State == ConnectionState.Closed)
{
    // open the **closed** connection and do something
    connection.Open();
}

Hope this helps others out.

  • War Ramses Jackson

Upvotes: 0

Ahmed Rabie
Ahmed Rabie

Reputation: 27

using (SqlConnection conn= new SqlConnection(conStr))
{

 // ETC

}

is the best solution since you maybe want to reuse the connection again. Just using & dipose won't help you, so checking on it before open is better.

Upvotes: -1

Suraj Singh
Suraj Singh

Reputation: 4059

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
        }

Upvotes: 4

DGibbs
DGibbs

Reputation: 14608

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

Upvotes: 15

Related Questions