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:and then update the middle of the function like this:
How you could use it:
So this should work since the yield causes the Connection to keep alive: