OnRowUpdated, Rollback in DB, ConcurrencyException and RowState question. Please give your opinions.

[David Lindgren]

Hello.

If you think this post is too long to read please scroll to the bottom and
read the last two sentences. Thank you

I have a problem which I cannot find a good solution to and hopefully I can
get some ideas from the readers of this group. I will first describe my
environment for you;

I have a database without constraints. It has a lot of tables ofcourse and
among all of them are two specific tables. Lets call them Customer and
CustomerDetails. In the Customer table there are some columns ofcourse and
the last column is DetailsId that works as reference to the CustomerDetails
table. The column datatype is an int and points out the Id column in the
CustomerDetails table. The Id column in CustomerDetails has autoincrement
turned on and is the primary key column. In the dataset in the C#
application there is a relation between Customer and CustomerDetails
(DetailsId in Customer refers to Id in CustomerDetails). Before a Customer
has been "opened" for the first time in the Application the CustomerDetails
table does not have any entries which is refered to from the Customer row.
The DetailsId value of the specific customer is DBNULL.

Now, imagine the following scenario:

Person A opens up a customer in the application. Automatically a row in
CustomerDetails is added and it's Id value is automatically assigned. This
value is then, due to the relation between the tables also inserted into the
customer row's DetailsId column. Lets say the value is 20 (because there are
19 other entries in CustomerDetails already).

Then Person B opens up the same customer in the application. Same thing
happens with the CustomerDetails table in the dataset. It gets a new row
with Id 20.

Person A changes something in the customer row and then saves all changes
back to the DB. Everything works fine so far.

Person B changes the same value in the customer row and then also tries to
save. What happens now is that first the CustomerDetails table in the
dataset is saved to the db. When this happens the RowUpdated event triggers
and we get a hint that the actual value of Id has changed in the DB so it is
now 21 (since 20 was already taken). With the help of the relation between
the tables also the DetailsId value in the Customer table is changed to 21.
Then some other updates follow, but finally there is a concurrency in the
Customer table since both person A and B have tried to changed the very same
rowvalues. A rollback is performed in the database. Person B is informed
that a concurrenyexception has been thrown and is now given two options.
First option is to loose all of his changes due to the concurrency. Second
option is to override the data in the database with his specific changes.
The person B chooses the second option since he thinks his changes are more
important. What happens now is that data is collected from the database into
a new dataset. Then this dataset is merged into the first dataset (in which
all of person B's changes are). It is merged with the "preserve changes"
flag so that all of the changes that person B has made are still there.
Since the DetailsId value has been changed in the RowUpdated event to 21
this value is still 21 in both Customer and CustomerDetails. Though, when
the new dataset was filled from the database there was already a row in
CustomerDetails (with Id value 20). So after the merge the CustomerDetails
table now holds two rows. One with Id 20 and one new with Id 21. In the
Customer table the row's value is 21 (which is correct).

Now, person B saves for the second time to the DB. Now there is no
concurrency and everything seemd to work fine. But everything is certainly
not fine.

The first problem is that no row in the Customer table refers to the row in
CustomerDetails which has Id 20. The second problem is that the row in
CustomerDetails with Id value 21 is never saved to the DB so the row in the
Customer table now refers to a row that does not exist in the DB. The first
problem you probably understand why it occured. The second problem is a
little more tricky to understand. Atleast it was for me. The thing is that
when person B saved to the DB the very first time all of the rows that were
saved before the concurrency occured had it's rowstate property changed from
added/modified to unchanged. Then the rollback was done in the DB, but the
dataset were still "changed". That is why the row in CustomerDetails was not
saved when person B saved to the DB the second time.

To fix the second problem I have this solution in mind; If I first make a
copy of the changes (before person B is saving for the first time) in the
dataset with dataset.GetChanges() and use this "copy" when I update the DB.
If everything goes fine with the DB update I can merge any changes (that
could have been done due to RowUpdated event activity) from the "copy"
dataset back to the original dataset. If something goes wrong with the DB
update (i.e. if a concurrency exception is being thrown), I will still have
the original dataset unchanged with all rowstates. I can then still give
person B the option to "override" the concurrency, which means I refresh a
new dataset from the DB and merge the changes into it. Then person B tries
to save once again.

Question now is, what will happen if I implement my solution? What if the
DetailsId in person B's dataset is something different from what's already
in the DB (say, 22). Will the existing row (21) that person A once saved
have it's Id value changed to 22 du to the merge between the newly refreshed
dataset and person B's dataset? Or will there just be a new row added (22)
to CustomerDetails so that problem 1 occurs (row 21 will be left on it's own
with reference from the Customer table) ?

I know that this was a quite big post but I felt I needed to explain
everything good enough to get good help. I am VERY thankful if you find a
working solution for me.

Regards,
David.

 

[Val Mazur \(MVP\)]

Hi David,

Does this example solve this issue

http://support.microsoft.com/default.aspx?scid=kb;en-us;310350

--
Val Mazur
Microsoft MVP

http://xport.mvps.org

 

[David Lindgren]

I'm afraid I don't see how that example solves my problem. Could you be more
specific about it? Thank you.

//David.

 

[Val Mazur \(MVP\)]

As I understand you need to know how to work with the identity values when
they are updated in a database. Is it right? If yes, then example shows how
to do this generating temp values for the identities and then loading them
with the actual values from the db. If this is not what you need, then I
probably did not understand your issue correctly

--
Val Mazur
Microsoft MVP

http://xport.mvps.org

 

[David Lindgren]

Well, you're right about it has to do with indentity values, but the example
does not solve my problem (or atleast I don't see it do so). I'll try to
explain my problem in another way.

There are two users. Both recieving data into their datasets. They both
modify the same row in table A. This row has a column value that refeers to
a row in table B. At first the value of this column is NULL (there is no row
in table B yet). Both users then add a row to table B. The value of the Id
column of this row is then put in the parent row in table A. The first user
then saves his dataset which means the row in table A is updated and the row
in table B is added. Lets say that the value of the Id column in table B is
10 (and ofcourse the referring column in table A has the same value). Then
the second user tries to save his dataset to the DB. His Id column value is
15. Since both users have modified the same row in table A there is a
concurrency. Now, the second user wants to "override" this and force his
values into the DB. Therefore the second user refreshes a new dataset from
the DB in which the first user's changes to table A and B are present. Then
this dataset is merged into the second user's dataset with the preserve
changes flag set. But what happens now is that the conflicting row in table
A gets 15 as Id value and in table B we now have two rows (with Id column
values 10 and 15). When the second user then tries to save again everything
works fine. Except for the one thing that the row with Id column value 10 in
table B is "left on it's own" without any reference. If the DB would have
had proper constraints and not only the dataset this would not have
happened, but now it does. Question is, how can I prevent this from
happening?

Thank you for helping out.

//David.

 

[Val Mazur \(MVP\)]

Hi David,

I see. What you could do is to define your own stored procedure for
UpdateCommand of the dataadapter. Since you do not have to specify just an
UPDATE SQL statement, you could use SP to use more complex logic. Inside of
SP check if this column already has value, not NULL. If yes, then return
this value as a result of the SP and update column value on a client side
with returned result. If it is NULL in a database, then generate new id and
return new id. This is a general idea, but I am pretty sure it will work.

--
Val Mazur
Microsoft MVP

http://xport.mvps.org