SQLDataReader Row Count

c# sql sqldatareader
atrljoe picture atrljoe · Mar 31, 2011 · Viewed 128.9k times · Source

I am trying to get the number of rows that were returned by iterating the reader. But I always get 1 when I run this code? Did I screw up something in this?

int count = 0;
if (reader.HasRows)
{
    while (reader.Read())
    {
        count++;
        rep.DataSource = reader;
        rep.DataBind();
    }
}
resultsnolabel.Text += " " + String.Format("{0}", count) + " Results";

Answer

p.campbell picture p.campbell · Mar 31, 2011

SQLDataReaders are forward-only. You're essentially doing this:

count++;  // initially 1
.DataBind(); //consuming all the records

//next iteration on
.Read()
//we've now come to end of resultset, thanks to the DataBind()
//count is still 1

You could do this instead:

if (reader.HasRows)
{
    rep.DataSource = reader;
    rep.DataBind();
}
int count = rep.Items.Count; //somehow count the num rows/items `rep` has.

Related questions

Should I call SqlDataReader.HasRows if I am calling SqlReader.Read

Trying to see if it is beneficial to add an if (dr.HasRows) before the while (dr.read()) function. I mean, technically if it doesn't have rows it isn't going to read, so would it matter if you checked this …

Read More
Is there anything faster than SqlDataReader in .NET?

I need to load one column of strings from table on SqlServer into Array in memory using C#. Is there a faster way than open SqlDataReader and loop through it. Table is large and time is critical. EDIT I am …

Read More
Cannot convert method group 'Read' to non-delegate type 'bool'

I am trying to use SqlDataReader to check if a entry exists. If it exists, it will return the ID, else it will return false. When I try to compile, I'm getting the error "Cannot convert method group 'Read' to …

Read More