datareader - number of rows

[Guest]

I need some help determining the number of rows returned
by any data reader. Thank you in advance.

 

[Mickey Williams]

int i = 0;
while(reader.Read()) ++i;
Console.WriteLine(i);

This has the disadvantage of destroying the state of the reader, but it is
the only way to determine the number of rows.

 

[William Ryan eMVP]

There's no way to do that without walking through the reader. At best you
can find out if there are in fact rows with HasRows if you are using the 1.1
framework. Otherwise, you'll have to iterate through it and count the
iterations.

HTH,

Bill

 

[Mark Rae]

There's no way to do that without walking through the reader. At best you
can find out if there are in fact rows with HasRows if you are using the 1.1
framework. Otherwise, you'll have to iterate through it and count the
iterations.

Can the DataReader support multiple recordsets? If so, is there no way it
can be done with ROWCOUNT or, at worst, sending a multiple query SQL string
where the first query does a SELECT COUNT(*)... and the second does the
actual SELECT...?

Or maybe this is a really stupid thought...???

 

[Jon Skeet [C# MVP]]

Can the DataReader support multiple recordsets?
Yes.

If so, is there no way it
can be done with ROWCOUNT or, at worst, sending a multiple query SQL string
where the first query does a SELECT COUNT(*)... and the second does the
actual SELECT...?

Or maybe this is a really stupid thought...???

I suspect that the disadvantages of that are:

1) The results may have changed between the SELECT COUNT and the SELECT
2) It'll be significantly slower

It could be that SQL Server would optimise it to only actually do the
query once, and give you fast and consistent results, but you'd have to
check.

 

[Ravichandran J.V.]

You could use select count(*) statement or alternately, I think you do
have the Count property of the DataReader object, I don't remember
though.

with regards,


J.V.Ravichandran
- http://www.geocities.com/
jvravichandran
- http://www.411asp.net/func/search?
qry=Ravichandran+J.V.&cob=aspnetpro
- http://www.southasianoutlook.com
- http://www.MSDNAA.Net
- http://www.csharphelp.com
- http://www.poetry.com/Publications/
display.asp?ID=P3966388&BN=999&PN=2
- Or, just search on "J.V.Ravichandran"
at http://www.Google.com

 

[Uri Dor]

according to Oreilly's ADO.NET Cookbook (2.7):
- iterate
- use count(*) in a batch query
- use @@ROWCOUNT

their site has sample code