DataReader : No RowCount???

[JeffTullin]

There is probably some simple answer to this, but as a newbie I was surprised to find that the SQLdatareader class presents a .HasRows property but does not tell you how many to expect.

No .RowCount?

As a no-brainer workaround, I imagined I could read through the records and then start again, but I don't see a 'go back to the beginning' method either.

What is the prefered method of determining the number of rows obtained by instantiating a SQLDataReader with some ad-hoc SQL?

 

[link9]

The DataReader has a .RecordsAffected property, but I have not seen it work reliably... I think it usually returns 1 for me (I quit trying to use it a while back, but I may have been misusing it).

DataReaders are forward-only cursors, so typically, you would only use them in a firehose manner, where you probably wouldn't need a rowcount.

If you do need RowCount or "Go Back" type of functionality, consider the DataTable, instead. It adds alot of weight, as well as functionality. It's my tool-of-choice for all but the most simple of data operations.

-paul

The answer to getting answered -- faq855-2992

 

[Nelviticus]

If you want to use the data from a reader and want to know how many rows you have you can do something like this snippet:

int iCount = 0;
sdrRead = scCmd.ExecuteReader();
while (sdrRead.Read())
	iCount++;
sdrRead.Close();
sdrRead = scCmd.ExecuteReader();

I think the reason it doesn't have a .RowCount property is that it doesn't actually examine the results of the query until you use .Read() to advance it to the next record. Bearing in mind that a query string can be very complicated there can be a lot of database activity involved in reading all the data. In order to identify the number of rows read, so that there could be a .RowCount property, the system would have to do a little more work as soon as you instantiated the reader.

The method above would involve twice as much work for the SQL server as it would have to read the records twice, once to count them and then again when you come to use the data in the rest of your code.

As link9 suggests, DataTables can be better. A downside is that they take up more memory (I think).

The .RecordsAffected propertly of an SQLDataReader only relates to queries where you're affecting the data, such as INSERT or DELETE and so on. If you're just reading data then no rows are actually "affected" in the sense that nothing has happened to them.

Nelviticus

 

[obislavu]

Use ExecuteScalar or ExecuteNonQuery() to get the number of potential records returned by DataReader.

SqlCommand myCommand = ..;
string mySQLSelectPassedToDataReader = " SELECT * FROM ..."; 
myCommand.CommandText = "select count(*) as NumberOfRecords from " + mySQLSelectPassedToDataReader;
Int32 count = (int) myCommand.ExecuteScalar();

-obislavu-

 

[sacheson]

A datareader object is a forward only cursor through a result set. THere is now Count property because the reader doesn't know how many records it is returning.

You can do either of the two methods above, or you can use a DataAdapter to fill a DataTable object in a DataSet, then you'll have a full table object in memory and can do a bunch of stuff with it.

______________________________________________
When told, "goto hell", a programmer finds the method, not the destination as harmful.

 

[JeffTullin]

Thanks all.
I had those possible workarounds in my back pocket, but was hoping to avoid doing the 'double execute' method because of time overheads.
Maybe the dataadaptor is the way to go, I just get wary about having a 'full table object in memory'.
What a waste..

 

[sacheson]

what are you trying to do? Maybe if we have a better idea of why you want to get the number of rows, we can better assist you with how to proceed.

Sam

______________________________________________
When told, "goto hell", a programmer finds the method, not the destination as harmful.

 

[JeffTullin]

Pretty straightforward.
One painstakingly assembled query across a few tables, grouped and summed.
Dont know in advance how many rows I'll get.
Want to process the results row by row, with a progressbar.
The math on the bar is basic:
thisrow/totalrows * 100

But the totalrows thing is where I got stuck without th rowcount.

(Oddly, when I tried the execute scalar solution above, the resulting sql seems to be rejected.)

 

[HFloyd]

JeffTullin,
I don't know if this will solve your problem, but have you thought about having another Stored Procedure that would just get the Count(*) from the Stored Procedure you are calling? You would probably have to test the performance, but you could get this value separate from running your ACTUAL Stored Procedure to use in you Progress bar calculation.

Just a thought,

Heather

Floyd Innovations

http://www.floydinnovations.com