dataset merge (Is there a better way?)

[Guest]

If you pull changes from a dataset and send the changes to update method of a
data adapter, when you merge the changes back to the original dataset you may
end up adding duplicate rows. This happens when the PK is updated either
because a row is being added and the PK is an Identity column or the row was
is being updated and the PK was also updated.

Datasets will merge rows whose original version of key(s) match and add rows
being merged if the original versions of the key(s) do not match any row in
the original dataset.

I noticed that the when you call the update method on the dataset containing
the changes, the original version of the columns are updated to the current
version. This causes the problem because, now, the original versions of the
PK of the original dataset and changes dataset do not match.

Since this is the case what is the purpose of calling the AcceptChanges
method after calling the data adapter's Update method??

So anytime the PK is updated, this will cause the updated row to be added to
the dataset rather than being merged. So you have to jump through some
programming hoops, to either remove potential duplicate rows in the original
dataset, or reset the original version of the PK in the dataset with the
changes.

Isn't there a simpler way to handle this?

 

[Guest]

Hi Whisky,

As a general rule you shouldn't be updating a primary key for a row in a
dataset or database. You need to have a way to uniquely identify a row in
the dataset, and that is the problem you are running into. How can you know
if a row is the new or updated if there is no unique unchanging way to
identify that row? I realize that you may be tied to a particular
implementation, but I would suggest looking for some alternatives. If you
must update the primary key, then perhaps you should look at why you have a
system designed in this way, and think about refactoring some of that design.
If you must "hack" it though, use a guid in addition the primary key and the
guid will never change, but you can update the primary key. Or use a 2ndary
key for your table mappings.

Ssmoimo

 

[Guest]

Thanks for the response, but you have this problem with or without updatable
PK's.

When you add a row to a dataset that contains a non-updatable PK, the
dataset creates a value for the PK which is not going to match the real PK in
the database after you add the row via the dataadapters's update command.
The update command set the original value to the current value. And since
this was done, by creating a new dataset just containing the changes, the
original values are out of synch between the two datasets.

Unless you synch up the originals, the merge will add the row instead of
merging.

wr