"ExecuteReader requires an open and accessible connection. The current state of the connection is open.

Question

A fairly large web application written in C # contains two errors:

'ExecuteReader requires an open and accessible connection. The status of the current connection is open. as well as 'Invalid attempt to call Read when the reader is closed.'

These errors were sporadic - the pages used to download the fine in about 95% of cases, but recently they have become endemic, they happen all the time and basically destroy the functionality of the application.

The web application is heavily dependent on the MS SQL database, and errors are not limited to just one page, but to almost all pages that connect to the database.

Requests are executed 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 investigated these errors on the Internet, and I saw several potential solutions that I tried to no avail:

Insert various pieces of code into the using () block. Specifying CommandBehaviour.CloseConnection to read. Check for inclusion of MARS. Ensuring that a new connection object is created each time.

I spent a lot of time finding solutions, not to mention a long time, trying to get it to work, and I'm almost on the verge of pulling hair now!

Please, help!

EDIT - Fixed a problem, see comments section.

Answer 1

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

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

Answer 2

It seems to me that Database is a type, not an instance.

Now you are facing multithreading issues.

You have 2 options:

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

or

• Make Database.Open() return a new instance of the connection object and use it when creating the command