How to get number of rows using SqlDataReader in C#

user_113329 0
Viewed 307464

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
}
sqldatareader c#
6 Answers

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_18315670
Related