How to get number of rows using SqlDataReader in C#
c#, sqldatareader
Solution
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.
Problem
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 } ```