sqldatareader error "Invalid attempt to call Read when reader is closed."

Question

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;

    }

you are closing the connection. That's it. Period. The connection is closed. You can't close the connection AND then read from it. It's closed! — Mitch Wheat
– Mitch Wheat, Commented Nov 1, 2012 at 23:25

Tim Schmelter · Accepted Answer · 2012-11-02 00:01:52Z

5

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:

edited Nov 2, 2012 at 0:01

answered Nov 1, 2012 at 23:28

Tim Schmelter

462k79 gold badges721 silver badges981 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mitch Wheat · Accepted Answer · 2012-11-01 23:37:24Z

0

You should perhaps modify your design to pass back a (disconnected) DataTable rather than try to keep connections open (which then might 'leak').

edited Nov 1, 2012 at 23:37

answered Nov 1, 2012 at 23:29

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Collectives™ on Stack Overflow

sqldatareader error "Invalid attempt to call Read when reader is closed."

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related