Troubleshooting – Error: Invalid attempt to call HasRows when reader is closed

Usually this error will be encountered while trying to access data from DataReader.

System.InvalidOperationException was unhandled
  Message=”Invalid attempt to call HasRows when reader is closed.”
       at System.Data.SqlClient.SqlDataReader.get_HasRows()

Most of the times the reason will be – you use SqlCnnection object in using block – which will close the connection instance after executing the code in it.
For a DataReader to get data, the connection should be OPEN.

using(SqlConnection sqlConn = new SqlConnection(connectionString))
using (SqlCommand sqlCommand = new SqlCommand())
sqlCommand.Connection = sqlConn;
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = “MY_SP”;
sqlCommand.CommandTimeout = 600;
dataReader= sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

So, when you try to check HasRows property of the DataReader object  as follows, you will get an error.

if (dataReader !=null && dataReader.HasRows)
while (dataReader.Read())
// your code

The solution for this is

  • DO NOT use SqlConnection in using block
  • DO NOT close the SqlConnection object. Close it using DataReader object as objDataReader.Close();