SqlDataReader Number of Rows

Lost User

Hi, I've used an SqlDataReader to retrieve some rows from my database. I can then iterate through the results calling its Read() method but how can I determine the number of rows without having to call the Read() method? Thanks :)

Lost User

Do a SELECT COUNT(*) that you read before entering the loop, or use a DataSet (with DataAdapter.Fill).

I are troll :)

Lost User

Thanks for your reply :) I'm a bit of a SQL noob so I'll have to look into using a DataSet but doing a SELECT COUNT will be effectively calling the same query twice (apart from actually returning the columns). Won't that be a great deal of overhead? I just thought the SQLDataReader must know how many rows were returned when it's created from calling the SQLCommand's ExecuteReader() method. Is that the case?

Lost User

Member 6038196 wrote:

Won't that be a great deal of overhead?

A great deal? Not unless you're going to call this query often, couting the rows in a table and returning the result is quite fast.

Member 6038196 wrote:

I just thought the SQLDataReader must know how many rows were returned when it's created from calling the SQLCommand's ExecuteReader() method. Is that the case?

If memory serves correct, it didn't - it just keeps getting records until it runs out of records. If you want both the records and the count in a single SQL-statement, use the DataAdapter.Fill method :)

I are troll :)

Lost User

Thanks - much appreciated :)

Pete OHanlon

Member 6038196 wrote:

I just thought the SQLDataReader must know how many rows were returned when it's created from calling the SQLCommand's ExecuteReader() method. Is that the case?

A datareader only ever knows about 1 row. It's what's known as a Firehose cursor, whereby data is squirted through as quickly as possible. You could always just increment a count on each read and use that.

"WPF has many lovers. It's a veritable porn star!" - Josh Smith

As Braveheart once said, "You can take our freedom but you'll never take our Hobnobs!" - Martin Hughes.

My blog | My articles | MoXAML PowerToys

tech603

Another solution for you if the others do not work, SqlDataReader reader = cmd.ExcecuteReader(); int count = 0; while ( reader.read){ // do your normal code for gathering column info count++; } when its done reading you'll have your data and your count. Hope that helps.

Matthew Vass QA Analyst mvass@hostmysite.com HostMySite.com