Invalid attempt to call Read when reader is closed in database layer

Question

I wrote method in my database layer to be consumed by other upper layers. This method returns IDataReader as shown below:

public IDataReader GetDataReader(string commandText, CommandType commandType, IDbDataParameter[] parameters)
{
     IDataReader reader = null;

     using (var connection = _database.GetConnection())   //either opens new or uses existing connection if exist
     {
           using (var command = _database.GetCommand(commandText, connection, commandType))
           {
               {
                   if (parameters != null)
                   {
                        foreach (var parameter in parameters)
                        {
                            command.Parameters.Add(parameter);
                        }
                   }
                   reader = command.ExecuteReader();
                   return reader;
                }
           }
     }
}

Now when i want to consume that method somewhere else and when it reaches while (reader.Read()) it gave me an error.

private void GetResult()
{
    var query = "SELECT * FROM MyTable";
    var reader = Context.GetDataReader(query, CommandType.Text, null);

    while (reader.Read())
    {
         //...
    }
}

Error:

'Invalid attempt to call Read when reader is closed.'

What could i do to fix that?

don't return your reader. you should project that to something else first. — Daniel A. White, May 04 '20 at 19:46
You're disposing the connection in `GetDataReader`, ergo, your database interaction is finished. — rfmodulator, May 04 '20 at 19:47
@rfmodulator that's what i suspect, nevertheless what could i do to overcome that and get my common method to return IDataReader? — Arie, May 04 '20 at 19:51
@DanielA.White I wanted to create common method "GetDataReader" to reduce the code in many places. I have lot of places like GetResult when i need datareader to get records and for each one of them. For instance in GetResult in while reader.read i want to loop through all records and do something there. — Arie, May 04 '20 at 20:00
@Arie You may consider returning a `DataTable` instead, https://stackoverflow.com/a/6073545/1048799 — rfmodulator, May 04 '20 at 20:17
@rfmodulator cannot datatable as it's too much data comming in therefore i pick up streamreader — Arie, May 05 '20 at 11:07

Invalid attempt to call Read when reader is closed in database layer

0 Answers0