Record count in SqlDataReader

[Charlie@CBFC]

Hi:

When returning an SqlDataReader from a database query, how do you get a
count of the number of records contained in the reader? There is no "Count"
property for reader object.

Thanks,
Charlie

 

[Andrew Robinson]

A reader doesn't have a count. You will need to read all the records and
keep track of count yourself.

Better to use a Command.ExecuteScalar() or to use a dataAdaptor and load
your records into a DataSet or Table and get your count there.

using (SqlConnection cn = new SqlConnection(DataConnection))
using (SqlCommand cm = new SqlCommand("SELECT Count(*) FROM MyTable", cn))

{

cn.Open();

cm.CommandType = CommandType.Text;

int i = (int)cm.ExecuteScalar();

cn.Close();

return i;

}

 

[Brock Allen]

There is no count because the reader is streaming the results in form the
DB server. IOW, it's possible the DB server is still finding records that
match your criteria after you start using the data reader.