Record count for SQLDataReader

[VK Link]

Hi - this was so simple in ASP, where we could get a record count of the recordset. How would we do the same in using SQLDataReader? The sql query is executed using sqlcommand and using datareader to read the records - need a count of total number of records retrieved. Thanks.

[Anonymous]

hi there, you can use the follwing codes for examples which i've been using: static int rows; sqlString="SELECT * FROM whatever WHERE condition; sqlCom = new SqlCommand(sqlString,quizConn); sqlCom.Connection.Open(); SqlDataReader tryReader = sqlCom.ExecuteReader(); while(tryReader.Read() == true) { rows++; }

[Luis Alonso Ramos]

I suppose that you need the count in advance of reading every record (as the other reply suggests.) You can't do that using a data reader, since you get each record as it is read from the database. What I'd suggest is to use a SqlDataAdapter to get all the records inside a DataTable, and then you have the count.

SqlCommand cmd = new SqlCommand("SELECT * FROM MyTable", conn);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
 
// dt.Rows.Count has now the number of records read
 
foreach(DataRow row in dt.Rows)
{
   // Use row["Field"] as you would use dataReader["Field"]
}

I hope this helps! -- LuisR

---

Luis Alonso Ramos Intelectix - Chihuahua, Mexico Not much here: My CP Blog!

The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005

[Vitaly Tomilov]

SQLCommand myCommand = new SQLCommand("SELECT COUNT(*) FROM MyTable", MyConnection); MyConnection.Open(); int nRecords = myCommand.ExecuteScalar(); // get the number of records in the table;

[VK Link]

We then could also set the a datareader - like myReader = myCommand.ExecuteReader - can we set both of them? A ExecuteReader and ExecuteScalar? Thanks.