how to get number of rows using SqlDataReader in C#

Question

Hi, my question is about how to get number of rows using SqlDataReader in C#. I've seen some answers around the net about this but none were clearly defined except for one that states to do a while loop with Read() method and increment a counter.

My problem is that I am trying to fill a multi-dimensional array with the first row being the column header names and every row after that to the be the row data.

I know that I can just dump the stuff in a List control and not worry about it, but for my own personal edification and I would also like to pull the data in and out of the array as I choose and display it in different formats.

So I think I can't do the Read() and then increment ++ way because that means that I would have to open Read(); and then open Read(); again to get amount of rows and then column data.

Not sure, so I turn to thee!

Just a small example of what I'm talking about:

int counter = 0;    

while (sqlRead.Read())
{
    //get rows
    counter++
}

and then a for loop to run through the columns and pop

something.Read();

int dbFields = sqlRead.FieldCount;

for (int i = 0; i < dbFields; i++)
{
   // do stuff to array
}

Answer 1

There are only two options:

• Find out by reading all rows (and you might as well store them)

• run a specialized SELECT COUNT(*) query beforehand.

Going twice through the DataReader loop is really expensive, you have to re-execute the query.

And (thanks to Pete OHanlon) the second option is only concurrency-safe when you use a transaction with a Snapshot|RepeatableRead isolation level.

Since you want to end up storing all rows in memory anyway the only sensible option is to read all rows in a flexible storage (List<>) and then copy the data to any format you want. The memory operation will always be much more efficient.

Answer 2

You can't get a count of rows directly from a data reader because it's what is known as a firehose cursor - which means that the data is read on a row by row basis based on the read being performed. I'd advise against doing 2 reads on the data because there's the potential that the data has changed between doing the 2 reads, and thus you'd get different results.

What you could do is read the data into a temporary structure, and use that in place of the second read. Alternatively, you'll need to change the mechanism by which you retrieve the data and use something like a DataTable instead.

Answer 3

Per above, a dataset or typed dataset might be a good temorary structure which you could use to do your filtering. A SqlDataReader is meant to read the data very quickly. While you are in the while() loop you are still connected to the DB and it is waiting for you to do whatever you are doing in order to read/process the next result before it moves on. In this case you might get better performance if you pull in all of the data, close the connection to the DB and process the results "offline".

People seem to hate datasets, so the above could be done wiht a collection of strongly typed objects as well.

Answer 4

Relational classes/DataSet is the suitable option.

using (DataTable dt = new DataTable())
 {
  dt.Load(sqlRead);
  Console.WriteLine(dt.Rows.Count);
}