Wednesday, February 16, 2011

How to fix "Invalid attempt to call HasRows when reader is closed"

In DataAccess Component

i have a method to load data from database,

here is snippet of my code

using(SqlConnection sqlConn = new SqlConnection(connectionString))
{
                sqlConn.Open();
                using (SqlCommand sqlCommand = new SqlCommand())
                {
                    sqlCommand.Connection = sqlConn;
                    sqlCommand.CommandType = CommandType.StoredProcedure;
                    sqlCommand.CommandText = "MY_SP";
                    sqlCommand.CommandTimeout = 600;
                    dataReader= sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
                } 
}

in My Provider Component

I implement a method to manipulate all data and load them into a list

 using (SqlDataReader dataReader = customerDA.LoadCustomerMasterRecord())
            {
                if (dataReader !=null && dataReader.HasRows)
                {
                    while (dataReader.Read())
                    {
                        MasterCustomer MasterCustomerObj = new MasterCustomer();
                        for (int i = 0; i < dataReader.FieldCount; i++)
                        {
                            if (dataReader.GetValue(i) != DBNull.Value)
                            {
                                DataMapper.SetPropertyValue(MasterCustomerObj, dataReader.GetName(i), dataReader.GetValue(i));
                            }
                        }
                        allMasterRecordList.Add(MasterCustomerObj);
                    }
                    dataReader.Close();
                }
            }

when i step through the code, the following error had been captured in the Error log

 System.InvalidOperationException was unhandled
  Message="Invalid attempt to call HasRows when reader is closed."
  Source="System.Data"
  StackTrace:
       at System.Data.SqlClient.SqlDataReader.get_HasRows()
       at CustomerDataProvider.CustomerProvider.GetAllMasterRecordList() in C:\SCI\SQLToAS400DataPushBack\CustomerDataProvider\CustomerProvider.cs:line 23
       at SQLToAS400DataPushBack.Program.LoadMasterRecordData() in C:\SCI\SQLToAS400DataPushBack\SQLToAS400DataPushBack\Program.cs:line 22
       at SQLToAS400DataPushBack.Program.Main(String[] args) in C:\SCI\SQLToAS400DataPushBack\SQLToAS400DataPushBack\Program.cs:line 15
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException:


the Root cause of this issue is that in my DA component

using(SqlConnection sqlConn = new SqlConnection(connectionString))

will close the DB connection before the DataReader is processed in the Provider Component..

the solution is  to remove the using statement so that the connection will not be close.. and close the connection by calling data reader Close Method with setting the sql command behavior to be  CommandBehavior.CloseConnection

1 comment:

  1. Thank you, you fixed my problem. Removing the using statement!

    ReplyDelete