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

Question

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.

Hiya, Database is a static class to handle the connection: `public static class Database { private static string connString = "(removed conn string)"; public static SqlConnection Conn = new SqlConnection(connString); public static void Open() { if (Conn.State == System.Data.ConnectionState.Closed) { Conn.Open(); return; } } }` — shauneba, Oct 24 '11 at 15:45
Do you get the expected behavior when you don't use `Database`? By having that `Database` class _and_ having code like this, you're decoupling things (SqlDataReader & SqlConnection) that really should be coupled. — Austin Salonen, Oct 24 '11 at 15:46
I'll try using `SqlConnection` without the Database class and get back to you in a minute :) — shauneba, Oct 24 '11 at 15:47
@Surf, just a fyi, I implemented the code above as well as the method for the Database class and it seemed to work without a problem. I'll continue to troubleshoot, but its worth knowing that the code is sound. — KreepN, Oct 24 '11 at 15:55
I replaced `Database.Open();` with `SqlConnection connection = new SqlConnection("myconnstring")` and changed `new SqlCommand(query, Database.Conn)` to `new SqlCommand(query, connection)`, and now it's thrown up the error in a different place (there are tonnes of queries across the application). Do you think it's worth going through the entire application and changing them to not use `Database`? PS -- thanks for the help so far everybody — shauneba, Oct 24 '11 at 15:57
@surfitscrollit: I already gave you the answer. Try the other option too, much less code to change. — leppie, Oct 24 '11 at 16:02
Will do, thanks -- I'm a bit new to C# as you can probably tell! — shauneba, Oct 24 '11 at 16:04
We finally got it fixed! Essentially, there were a few 'rogue queries' in our application that were affecting the entire thing from working correctly, so we just had to go through every class (all 88 of them!) and check that they were written correctly. Thanks for all your help everybody :) — shauneba, Oct 25 '11 at 11:05
possible duplicate of [ExecuteReader requires an open and available Connection. The connection's current state is Connecting](http://stackoverflow.com/questions/9705637/executereader-requires-an-open-and-available-connection-the-connections-curren) — Kevin Panko, Mar 14 '14 at 19:24

score 7 · Answer 1 · answered Oct 24 '11 at 15:45

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

score 5 · Accepted Answer · edited May 23 '17 at 10:30

In addition to leppie's answer, you should also be Dispose()ing of any IDisposable types:

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

            string query = "SELECT * FROM table"; // (dummy query)

            using (SqlCommand command = new SqlCommand(query, Database.Conn))
            using (SqlDataReader reader = command.ExecuteReader(CommandBehaviour.CloseConnection))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        // Do something with the data.
                    }
                }
            }
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }

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

2 Answers2

Linked