Handling concurrency violations

[John]

Hi

I have developed the following logic to handle db concurrency violations. I
just wonder if someone can tell me if it is correct or if I need a
different approach.Would love to know how pros handle it.

Thanks

Regards


Dim dc As DataColumn
Dim drCache As DataRow
Dim drCurrent As DataRow

Try
' Attempt the update
daContacts.Update(ds.Contacts)

Catch Ex As DBConcurrencyException

' First - cache the row
drCache = ds.Contacts.NewRow()
For Each dc In ds.Contacts.Columns
If Not dc.ReadOnly Then
drCache(dc.ColumnName) = Ex.Row(dc.ColumnName)
End If
Next

' Refresh from database
daContacts.Fill(ds.Contacts)

' Position to the faulted row
drCurrent = ds.Contacts.Rows.Find(Ex.Row("ID"))

' Apply User Changes
For Each dc In ds.Contacts.Columns
If Not dc.ReadOnly Then
drCurrent(dc.ColumnName) = drCache(dc.ColumnName)
End If
Next

' Save again
daContacts.Update(ds.Contacts)
End Try

 

[Cowboy \(Gregory A. Beamer\)]

My mind is not wrapped around what you are trying to do, so I apologize.

In the DataSet, which is, underlying, an XML document, you have the original
row, the changed row, etc. You can query the different versions to find out
what has changed. That is how .NET determines what to update and insert.

As for concurrency, you can query a DataSet for the error rows after you
attempt to update.

My suggestion is look for Dino Esposito's works on concurrency. A google
search using site:msdn.microsoft.com and Dino Esposito and concurrency
should yield something. You might have to search a bit, but you should find
his work. It is a very good pattern.

--
Gregory A. Beamer
MVP, MCP: +I, SE, SD, DBA

Subscribe to my blog
http://gregorybeamer.spaces.live.com/lists/feed.rss

or just read it:
http://gregorybeamer.spaces.live.com/

*************************************************
| Think outside the box!
|
*************************************************

 

[Kerry Moorman]

John,

It looks like your approach to concurrency handling is to get around the
concurrency error by reloading the data from the database and then applying
your changes.

You might as well just turn off concurrency checking, since that is the end
result anyway.

In other words, it looks like your code is just avoiding concurrency
exceptions, not handling concurrency conflicts.

Kerry Moorman

 

[John]

So Kerry how do I handle it? which was my original question.

Regards

 

[Kerry Moorman]

John,

You need to decide how you want to handle concurrency issues in a particular
application.

For example, how do you want to handle this situation:

Mary loads your employee record from the Employees table into the Employee
Manager application. She loaded your record because your cell phone number
has changed and she is updating that piece of information in your record.

While Mary is working on your record, Jim loads your employee record from
the Employees table into the Employee Manager application. He loaded your
record because you have just gotten a big raise and he is updating that piece
of information in your record. He changes your salary and updates your record
in the database.

Mary has now finished changing your cell phone number and attempts to update
your record in the database.

Do you want Mary's update to succeed? If it does you have just lost your
raise because her update will overwrite your new salary that Jim entered with
your old salary as it was when Mary loaded your record.

Do you want Mary's update to fail because of a concurrency conflict? When it
fails, how do you want to deal with the conflict?

Your current code appears to read the updated record and then apply the old
values to it. This gets rid of the concurrency conflict but you still end up
with your old salary instead of your raise.

My choice would be to inform the user that the record has been changed by
another user and let the user reload the record and start over.

Kerry Moorman

 

[John]

Hi Kerry

Many thanks for this insight. Is there an easy way to display both new and
old row values to the user to let him/her select?

Thanks

Regards

 

[Kerry Moorman]

John,

I don't display both new and old values to the user, so I don't have any
code examples.

However, this book has a section on just that topic. I can highly recommend
this book in general:

Programming Microsoft ADO.NET 2.0 Core Reference by David Sceppa

Kerry Moorman