How to get number of rows using SqlDataReader in C#?

Question

How I cat to get number of rows using SqlDataReader in C# (C Sharp)?

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 or 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<> 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.