How to get number of rows using SqlDataReader in C#

Question

My question is how to get the number of rows returned by a query using SqlDataReader in C#. I've seen some answers 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.

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 then you might as well store them)

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

Going twice through the DataReader loop is really expensive, you would 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 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<> or DataTable) and then copy the data to any format you want. The in-memory operation will always be much more efficient.