'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.

Solution 1

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

Solution 2

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