VB.NET Sql executescalar and reader?

Question

Using VB.NET, I have opened an SQLConnection and retrieved an SQLDataReader with the data I want to process. As I am going through each record in the reader, I may want to update the record. What is the best way to go about doing this?

Is it possible to call ExecuteScalar with UPDATE commands while the Reader is still open without the Reader keeping track of where it is?

Will I have to create a second connection?

What is the "best practice" approach?

Thanks!

Answer 1

You have several design options: cache all the records locally and pour through them to determine which you want to change, then execute scalars for each record. This involves all of the architecture of the prior answer, and is the correct MS recommended way to do disconnected recordsets.

OR, open a second connection while reading. If you are reading fast enough, opening a second connection shouldn't be much of a burden. The memory burden is much less, the processing is *much* faster (why hold rows you don't wish to update).

The absolute best method would be to put your check for updating or not into a temporary table (if possible) and run a stored procedure against the table and your temp table. This puts the burden on the larger machine, the DB server, and saves a bunch of network traffic.

Answer 2

No - you cannot use the connection for anything else while you have a SqlDataReader open against it. You would have to open a separate connection. The reason for this is that a SqlDataReader is a live, connected operation.

ADO.Net provides a disconnected data model for what you are trying to do. This is the combination of using DataTables/DataSets and Data adapters. Here is an example.

Sub UpdateBob(NewValue as String)
Dim tblBob as new DataTable("tblBob")
Dim adp as new _
SqlDataAdapter("select * FROM tblBob", _
"Some SQL connection string")
Dim cb as new SqlCommandBuilder(adp)
adp.UpdateCommand = cb.GetUpdateCommand()
adp.Fill(tblBob)
Dim dr as DataRow
For each dr in tblBob.Rows
dr("UpdateCol") = NewValue
Next
adp.Update(tblBob)
End Sub

That is how you would update inline results - using the disconnected data model of DataTables and DataAdapters.

Here is a more detailed explanation:

http://www.ondotnet.com/pub/a/dotnet/excerpt/progvisbasic_ch08-2/index.html?page=4