SQLDataReader Row Count

Question

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 1

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. 

Answer 2

 DataTable dt = new DataTable();  dt.Load(reader);  int numRows= dt.Rows.Count;