Recordcount with OleDbDataReader

[Squirm]

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 I'm hoping there is an easier way, as there was in ADO.

 

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:

[jfackler]

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 can't 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 server's memory requirements, you can use the DataReader only to display data."

 

Connell goes on to say this isn't 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 you're 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

[hemenkap]

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.

[quwiltw]

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?

[Squirm]

It's taken a bit of reworking, but it's going to make things a lot easier in the long run. Thankyou very much.

 

:)

[Nerseus]

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).

 

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 you're deleting and they got in first. Your delete may not do anything if the ID passed in isn't found. You might have other code that does an INSERT after a DELETE, but you wouldn't want to insert if the DELETE failed (such as on a Bank Transfer type of transaction).

 

Just adding info...

 

-Nerseus

[Squirm]

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.

 

:-\

[Nerseus]

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. Don't forget that INSERT, UPDATE, and DELETE can affect multiple records or even all records (if there's no WHERE clause). If you're 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. You'd have to know the rows affected to write the parent record first, then write out the details and associate them to the parent ID. That's just one way to use it - I'm sure others have found a use for it as well. :)

 

Now, make those squirmy eye quit twitching, I can't think straight! :p

 

-Nerseus