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";

Please note `HasRows` is useful for those of us who just want to distinguish between 1 or more rows (`HasRows==true`) and 0 zero rows (`HasRows == false`), [more here](https://stackoverflow.com/a/12609979/1175496) `HasRows | Type: System.Boolean true if the SqlDataReader contains one or more rows; otherwise false.` — Nate Anderson, Jan 19 '22 at 23:30

score 35 · Answer 1 · answered Jan 31 '13 at 15:59

35

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

answered Jan 31 '13 at 15:59

Roozi

683
6
12

score 32 · Accepted Answer · answered Mar 31 '11 at 16:14

32

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.

answered Mar 31 '11 at 16:14

p.campbell

98,673
67
256
322

15

`rep` what type of variable is? – Giancarlo Benítez May 24 '18 at 16:48
1

I think rep is a GridView – Tony Rush Sep 08 '20 at 15:06

score 13 · Answer 3 · answered Mar 05 '14 at 22:34

13

This will get you the row count, but will leave the data reader at the end.

dataReader.Cast<object>().Count();

answered Mar 05 '14 at 22:34

Gary

161
1
4

score -9 · Answer 4 · edited Nov 17 '17 at 15:44

-9

Maybe you can try this: though please note - This pulls the column count, not the row count

 using (SqlDataReader reader = command.ExecuteReader())
 {
     while (reader.Read())
     {
         int count = reader.VisibleFieldCount;
         Console.WriteLine(count);
     }
 }

edited Nov 17 '17 at 15:44

Stuart.Sklinar

3,683
4
35
89

answered May 04 '17 at 12:17

pjiaquan

87
1
2

SQLDataReader Row Count

4 Answers4

Linked