Recordcount with OleDbDataReader

Squirm

Squirm

Well-known member
Joined
May 21, 2001
Messages
139
Location
London, England
User Rank
{modbadge}
After creating an OleDbDataReader by executing an OleDbCommand, how does one find out how many rows have been returned by the command? Failing that, is it possible to have the OleDbDataReader return to the first row after reading all the data? Obviously I can run the command again, but Im hoping there is an easier way, as there was in ADO.

Code: 
Dim Reader As OleDbDataReader
Dim RunCmd As New OleDbCommand(sCommand, Con)
Dim i As Int32

Con.Open()
Reader = RunCmd.ExecuteReader(CommandBehavior.Default)

Do While Reader.Read
    i += 1
Loop

Now have recordcount in i, but have to requery to get back to the first row!

I did try the .RecordsAffected property, but that always returns 0, and according to the help, returns how many records were affected by an UPDATE INSERT or DELETE operation. This leaves me wondering why it is a property of the OleDbDataReader which is for reading data, not updating it (as far as I can tell).

:confused:
 
Squirm,

The datareader is a read only forward only object. Fast...but the point of no return is when you say read.

You can interupt it, but you cant go back.

Have to use a dataset for that ability.

Quote from "Coding techniques for Visual Basic.Net" from Microsoft Press by Connell:

"While the DataReader perimits direct, high-performance access to the database, it provides only read-only and forward only access to the data.
The results it returns are not memory resident, and it can access only one record at a time.
While these limitations put less load on the servers memory requirements, you can use the DataReader only to display data."

Connell goes on to say this isnt a bad thing, since many apps need just this display of data.

You could still use a dataadapter and dataset to execute the command once and get the number of rows affected.
Then use yourreader to efficiently pull the data out of the database recurrently to save overhead.

With the datareader youre filling the reader object with data...different set of methods then to access info
about your data than when you fill the dataset object with data.


Jon
 
hi ,

just as jfackler said you nedd to use a dataadapter and a dataset for this.

say you fill the dataset using the dataadapter and call you table as Table1 then you can get the recordcount as

Dataset.Tables("Table1").Rows.Count

bye.
 
For some reason I always find myself using Datasets, but it seems like you should be able to stuff @@ROWCOUNT into an output parameter as a workaround?
 
Its taken a bit of reworking, but its going to make things a lot easier in the long run. Thankyou very much.

:)
 
I did try the .RecordsAffected property, but that always returns 0, and according to the help, returns how many records were affected by an UPDATE INSERT or DELETE operation. This leaves me wondering why it is a property of the OleDbDataReader which is for reading data, not updating it (as far as I can tell).
Click to expand...

The most common use for the RecordsAffected property is to check whether the action actually took place. For instance, someone else is deleting a record that youre deleting and they got in first. Your delete may not do anything if the ID passed in isnt found. You might have other code that does an INSERT after a DELETE, but you wouldnt want to insert if the DELETE failed (such as on a Bank Transfer type of transaction).

Just adding info...

-Nerseus
 
That makes sense for a DELETE operation, and of course UPDATE and INSERT too. However, the DataReader is for reading data (SELECT operations), so RecordsAffected is useless here.

:-\
 
Well audit operations will want to return data that was affected. You could use the Read method but you may want to know how many records were affected before you start looping. Dont forget that INSERT, UPDATE, and DELETE can affect multiple records or even all records (if theres no WHERE clause). If youre writing auditing/logging code, you may want to write out the rows affected to a parent log table, and the details of what was affected to a child table. Youd have to know the rows affected to write the parent record first, then write out the details and associate them to the parent ID. Thats just one way to use it - Im sure others have found a use for it as well. :)

Now, make those squirmy eye quit twitching, I cant think straight! :p

-Nerseus
 
Back
Top