How to get number of rows using SqlDataReader in C#

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.

If you do not need to retrieve all the row and want to avoid to make a double query, you can probably try something like that:

using (var sqlCon = new SqlConnection("Server=127.0.0.1;Database=MyDb;User Id=Me;Password=glop;"))
      {
        sqlCon.Open();

        var com = sqlCon.CreateCommand();
        com.CommandText = "select * from BigTable";
        using (var reader = com.ExecuteReader())
        {
            //here you retrieve what you need
        }

        com.CommandText = "select @@ROWCOUNT";
        var totalRow = com.ExecuteScalar();

        sqlCon.Close();
      }

You may have to add a transaction not sure if reusing the same command will automatically add a transaction on it...

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.