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  lt  dbFields  i            do stuff to array

User · Answer

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

User · Answer

to complete of Pit answer and for better perfromance   get all in one query and use NextResult method   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 select   ROWCOUNT        using  var reader   com ExecuteReader                  while reader read                   iterate code                   int totalRow   0           reader NextResult                if reader read                 totalRow    int reader 0                       sqlCon Close

User · Answer

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

User · Answer

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 lt  gt  or DataTable  and then copy the data to any format you want  The in-memory operation will always be much more efficient

User · Answer

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

User · Answer

I also face a situation when I needed to return a top result but also wanted to get the total rows that where matching the query  i finaly get to this solution      public string Format SelectQuery selectQuery              string result         if  string IsNullOrWhiteSpace selectQuery WherePart                   result   string Format     declare  maxResult  int  set  maxResult    0    WITH Total AS   SELECT count    as  Count  FROM  2    SELECT top   maxResult  Total  Count    1  FROM Total   2    m limit To  selectQuery SelectPart  selectQuery FromPart                 else                 result   string Format     declare  maxResult  int  set  maxResult    0    WITH Total AS   SELECT count    as  Count  FROM  2  WHERE  3    SELECT top   maxResult  Total  Count    1  FROM Total   2  WHERE  3    m limit To  selectQuery SelectPart  selectQuery FromPart  selectQuery WherePart                  if   string IsNullOrWhiteSpace selectQuery OrderPart           result   string Format   0  ORDER BY  1    result  selectQuery OrderPart          return result

[c#] How to get number of rows using SqlDataReader in C#

Examples related to c#

Examples related to sqldatareader