Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

'ExecuteReader requires an open and available Connection. The connection's current state is open'

A fairly large web application written in C# keeps throwing up 2 errors:

'ExecuteReader requires an open and available Connection. The connection's current state is open.' and 'Invalid attempt to call Read when reader is closed.'

These errors were sporadic -- the pages used to load fine about 95% of the time, but recently they've become endemic, they're occurring all the time and basically crippling the application's functionality.

The web app is highly reliant on an MS SQL database, and the errors appear to not be confined to just one page, but nearly all the pages that connect to the database.

The queries are performed as such:

Database.Open(); // Custom class that has our connection string hard coded.

string query = "SELECT * FROM table"; // (dummy query)
SqlCommand command = new SqlCommand(query, Database.Conn);

SqlDataReader reader = null;

try {
    reader = command.ExecuteReader(CommandBehaviour.CloseConnection);

    if (reader.HasRows) {

        while (reader.Read()) {
            // Do something with the data.
        }
   }
    reader.Close();
}
catch (Exception e) {
    throw new Exception(e.Message);
}
finally {
    if (reader != null) {
        reader.Close();
    }
}

I've researched these errors on the web and I've seen a few potential solutions that I've tried to no avail:

Putting various parts of the code in a using() block. Specifying CommandBehaviour.CloseConnection for the reader. Checking that MARS is enabled. Ensuring that a new connection object is created each time.

I've spent ages searching for solutions to this, not to mention a long long time trying to make it work, and I'm almost on the verge of pulling my hair out now!

Please help!

EDIT -- Fixed the problem, see the comments section.

like image 590
shauneba Avatar asked Oct 24 '11 15:10

shauneba


1 Answers

It looks to me that Database is a type and not an instance.

You are running into multithreading issues now.

You have 2 options:

  • Apply the [ThreadStatic] to the field containing the connection object created by Database.Open()

or

  • Make Database.Open() return a fresh instance of the connection object and use that when constructing the command
like image 117
leppie Avatar answered Sep 22 '22 13:09

leppie