DbDataReader error: Invalid attempt to call Read when reader is closed

Question

Hi I am trying to get return the DataReader from a method but it returns a closed DbDataReader object there. Any Idea to sort out this issue. I am open to any suggestions to make the code better.

Thanks

UPDATE I don't want to leave the Database connection Open. is there any way to return the open DataReader after closing the connection.

internal DbDataReader ExecuteReader(SqlCommand command, CommandBehavior behavior, string connectionString)
  {
    DbDataReader dataReader = null;
    try
    {
      SqlConnection connection = GetConnection(connectionString);
      Open(connection);
      command.Connection = connection;
      command.CommandTimeout = 60;
      dataReader = command.ExecuteReader(behavior);
      Close(connection);
    }
    catch
    {
    }
    return dataReader;
}

If you put a breakpoint on `return dataReader;`, is it closed at this point? — Neil Knight, Feb 21 '12 at 16:39
You might also want to think about your empty catch block. You certainly wouldn't want to swallow all exceptions, would you? — Bryan Crosby, Feb 21 '12 at 16:55
Hi @BryanCrosby, its not empty. I am logging the errors. I just removed it while posting to make it short and readable. Thanks — Scorpion, Feb 21 '12 at 16:57
Fair enough. I would recommend leaving at least the type of exception you are trying to catch, e.g. catch (Exception exc) etc — Bryan Crosby, Feb 21 '12 at 17:01

score 6 · Accepted Answer · answered Feb 21 '12 at 16:42

6

It's closed because you closed the database connection. It can't read data from a closed SqlConnection. If you want to reuse the connection you may pass an OPEN connection to the method and the close the connection after you consumed data from the DbDataReader.

answered Feb 21 '12 at 16:42

Adriano Repetti

65,416
20
137
208

Hi @Adriano, will it be an issue if I leave the connection open? – Scorpion Feb 21 '12 at 16:49
2

You have to close the connection as soon as possible (server's connections are a finite resource) even if it'll be released by the garbage collector later. This does **not** mean you have to close the connection **immediately**, take the time to consume the data and then close the connection. – Adriano Repetti Feb 21 '12 at 17:03

DbDataReader error: Invalid attempt to call Read when reader is closed

1 Answers1