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.