CODEWITHSUNDEEP
c#sql
Muhammad Tauseen
Muhammad Tauseen

Reputation: 113

Invalid attempt to call HasRows when reader is closed

I have this problem: Invalid attempt to call HasRows when reader is closed. I have tried alot; removing connection close line, closing the connection in the end. but having same issue. I can't get whats wrong with my code.

try
{
    con = new SqlConnection(ConfigurationManager.ConnectionStrings["TextItConnectionString"].ConnectionString);
    using (con)
    {
        con.Open();
        Library.writeErrorLog("connection build and open");
        SqlCommand cmd = con.CreateCommand();
        using (cmd)
        {
            cmd.CommandText = "Select [name] From [dbo].[Users]";
            SqlDataReader reader = cmd.ExecuteReader();
            using (reader)
            {
                user.dt.Load(reader);
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Library.writeErrorLog(reader.GetString(0));
                    }
                }
                else
                    Library.writeErrorLog("no rows");
                reader.Close();
                con.Close();
            }
        }
    }
    //SqlDataAdapter adap = new SqlDataAdapter("Select [name] From [dbo].[Users]", con);
    //adap.Fill(user.dt);
}
catch (Exception ex)
{
    Library.writeErrorLog(ex);
}

Thanks for the help!

Upvotes: 1

Views: 6487

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460018

I assume that user.dt returns a DataTable. You know that DataTable.Load(reader) will consume all records of the resultset and advances the reader to the next set? I'm asking because you are using HasRows after you've already used DataTable.Load.

As Steve has commented

Looking at the reference source of DataTable.Load you could clearly see that the DataReader is closed before exiting from the method.

So if there is no other resultset(f.e. SELECT * FROM T1;SELECT* from T2) the reader will be closed at the end of Load which will cause the exception if you try to use SqlDataReader.HasRows.

I'd call this a lack of documentation since it's mentioned nowhere on MSDN.

So either use

  1. reader.Read and reader.GetString in a loop and add it to the DataTable manually,
  2. use DataTable.Load and loop the table afterwards or
  3. use SqlDataAdapter.Fill(table):

1) while loop and manually filling the table

using (SqlDataReader reader = cmd.ExecuteReader())
{
    if(reader.HasRows)
    {
        while (reader.Read())
        {
            string name = reader.GetString(0);
            user.dt.Rows.Add(name);
            Library.writeErrorLog(name);
        }
    }
    else
        Library.writeErrorLog("no rows");
}

2) requires two loops, one in DataTable.Load and the foreach

using (SqlDataReader reader = cmd.ExecuteReader())
{
    if(reader.HasRows)
    {
        user.dt.Load(reader); // all records added
        foreach(DataRow row in user.dt.Rows)
        {
            string name = row.Field<string>(0);
            Library.writeErrorLog(name);
        }
    }
    else
        Library.writeErrorLog("no rows");
}

3) another option is to use a SqlDataAdapter and it's Fill(dataTable) method:

using (var da = new SqlDataAdapter(cmd))
{
    da.Fill(user.dt);
    if (user.dt.Rows.Count > 0)
    {
        foreach (DataRow row in user.dt.Rows)
        {
            string name = row.Field<string>(0);
            Library.writeErrorLog(name);
        }
    }
    else
        Library.writeErrorLog("no rows");
}

Side-note: you don't need to use reader.Close or con.Close if you use the using-statement.

Upvotes: 3

Related Questions