Any way to force an update after DBConcurrencyException thrown?

  • Thread starter Thread starter mracuraintegra
  • Start date Start date
M

mracuraintegra

I've tried everything I can think of, but I'm still struggling with a
solution to force an update to a table, even if a
DBConcurrencyException is thrown in my application. Is there any
tried-and-true solution to allow an Update to occur, even if the
particular record isn't concurrent - in essence, implementing a
last-in-wins scenario?

Thanks in advance for your responses!
 
Perhaps you could switch off concurrency check (remove the WHERE condition)?
 
Look at Dino Espositos articles. He has dealt with concurrency in many
instances. Worst case is firing the update command directly, outside of the
adapter, which would force a last in wins scenario.

Some ADO.NET 1.x articles
http://msdn.microsoft.com/data/prev/ref/adonet/

In particular:
http://msdn.microsoft.com/data/prev.../msdnmag/issues/03/04/dataconcurrency/toc.asp


--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com

*************************************************
Think outside of the box!
*************************************************
 
Thanks to both of you for the responses.

....Yikes! When I turned off the optimistic WHERE check, it didn't have
exactly the effect I was hoping for. I was hoping all this time that
only specifically changed fields in a datarow inside my DataSet's
datatable were being changed, but that is obviously not the case. I
tried changing a different field on the same record on two different
clients, and the only data that persisted after both saved was the
exact view of the data from the last-in client. Is there any way that
doesn't involve thousands of lines of code that can fix this?

Also, I did read through the article re: DataSets and using the
RowUpdate, and it was interesting, but ultimately frustrating as I'm
using C# and not VB and am using the command-building stuff in Visual
Studio. I wish there were a lot more articles from MSDN regarding C#
and Visual Studio 2005, but especially when trying to find info about
how all of their automatic command-builder and Dataset stuff works,
I've found very little about it. Maybe I'm just looking in all the
wrong places, but I can't believe that I'm the only person out there
who occasionally has two users edit the same record and need to do
something about it!
 
Hi,

So, if I understand your scenario, you want to update just *modified*
fileds, right?
This isn't trivial at all as you would have to generate an update statement
for each row (you'll have to build sql statement on the fly based on
comparing field's original and current value) and you would have to iterate
manually (instead of simply using Update method).
Perhaps there is a better way I am not aware of...
 
That is one of the features of the LLBLGen ORM product at www.llblgen.com

Concurrency control by default only sends an update statement with the
fields that have changed for each row.

You also have the option to override it as needed.
 
CommandBuilder provides you exactly what you are looking for.

Let me show you the following code snippet:

public static DbCommandBuilder CreateCommandBuilder(
DbDataAdapter adapter,
string Provider)
{
DbProviderFactory factory = DbProviderFactories.GetFactory(Provider);
DbCommandBuilder cmdbuilder = factory.CreateCommandBuilder();
cmdbuilder.ConflictOption = ConflictOption.OverwriteChanges;
cmdbuilder.SetAllValues = false;
cmdbuilder.DataAdapter = adapter;
return cmdbuilder;
}

Command builders subscribe to DbDataAdapter.RowUpdating event, and generate
proper commands.

if you set cmdBuilder.ConflictOption = ConflicOption.OvewriteChages, the
command builder includes only the primary key fields in where clauses of
update and delete commands, implementing the last-in-wins model.

if you set cmdBuilder.SetAllValues = false, which is the default, the comand
builder includes only the changed fields in SET clause of UPDATE command.
This is what you are looking for, right?

Regards from Madrid (Spain)

Jesús López
 
Back
Top