C#, SQL - multiple rows handling

[Chris]

I use System.Data.SqlClient.SqlCommand object. I need to fetch data from
database by using SELECT command. the problem is that after calling such
statement in SQL I will get many rows. how to do in my application to get
many rows but acces its content row by row? is it possible to count how many
rows SELECT statement returns?

[Marc Gravell]

Depends on which method you call; example using ExecuteReader (haven't
tested, but should be OK from memory):

using (IDataReader reader =
cmd.ExecuteReader(CommandBehavior.CloseConnection) ) {
// may wish to cast reader as SqlDataReader if Sql, etc
int gridIndex = 0;
do {
int rowIndex = 0;
while (reader.Read()) {
// get values from the reader (either individually or in an
array) and process
rowIndex++;
}
gridIndex++;
} while (reader.NextResult());
}

This handles multiple rows in multiple grids; note that even if you
only expect 1 grid, I recommend reading the grids (all 1 of them) until
you run out of data - as I have seen (in the past) SQL errors thrown
*after* the first grid not be noticed otherwise.

Marc