C# – sqldatareader error “Invalid attempt to call Read when reader is closed.”

c

when I am reading data from sqldatareader I am getting error "Invalid attempt to call Read when reader is closed."
My code is below

obviously this is getting closed by the using statement in the ExecSqlDataReader

I would like to be able to call this method and then use the reader in another class, is there any way round this without having to loop through the SqlDataReader within the ExecSqlDataReader method ?

hope that makes sense

thanks

Code:

SqlDataReader reader = data.ExecSqlDataReader(1);
 while (reader.Read())
  {
   .....
  }


       public SqlDataReader ExecSqlDataReader(int queryType = 0)
       {
        using (var conn = new SqlConnection(this.ConnectionString))
        {
            try
            {
                PrepareCommandForExecution(conn, queryType);
                return _cmd.ExecuteReader();

            }
            finally
            {
                _cmd.Connection.Close();
            }
        }
    }

    private SqlCommand PrepareCommandForExecution(SqlConnection conn, int sqlType = 0)
    {

            _cmd.Connection = conn;
            switch (sqlType)
            {
                case 0:
                    _cmd.CommandType = CommandType.StoredProcedure;
                    break;
                case 1:
                    _cmd.CommandType = CommandType.Text;
                    _cmd.CommandText = _tSqltext;
                     break;
                case 2:
                    _cmd.CommandType = CommandType.TableDirect;
                    break;
            }

            _cmd.CommandTimeout = this.CommandTimeout;
            _cmd.Connection.Open();

            return _cmd;

    }

Best Answer

The problem is that leaving the function (via the return statement) kicks you out of the using blocks, and so the SqlDataReader and SqlConnections you are using are disposed. To get around the problem, try changing the function signature like this:

public static IEnumerable<IDataRecord> ExecSqlDataReader( int queryType )

and then update the middle of the function like this:

using ( var reader = cmd.ExecuteReader() )
{
    while ( reader.Read() )
    {
        yield return reader;
    }
}

How you could use it:

var first10 = ExecSqlDataReader(0).Take(10);
foreach (var rec in first10)
{
    int ID = rec.GetInt32(0);
}

So this should work since the yield causes the Connection to keep alive:

Related Topic