problem with merge and new row

[Paul Delcogliano]

Hi All,

I think I am mis-understanding something about the DataSet's Merge method. I
am getting changes from my DataSet and storing them in another DataSet
called changes. I then pass those changes to the UpdateDataset method of the
Data Access Application Block. Any new rows in the changes dataset are
inserted into my SQL Server db and the new ID value from the database is
assigned to the added rows in the changes dataset.

Now, I want to merge my changes back into my original dataset. When I call
the Merge method, the new rows are not merged so much as they are added. So
what I end up with is 2 copies of the new row. Reading through the
documentation, it sounds like this is the expected behavior since the
original new row in the dataset has a primary key value different that the
primary key value that came back from the database. In my case, when I add a
new row to the dataset, I set the primary key value to a negative number,
i.e. -1. Then when after the database insert occurs, the -1 is replaced with
a new identity value from the database. Naturally, the two don't match.

My question is this, how do I force the two rows to match so that the merge
updates the dataset correctly with the new id value returned from the db?

Any insight is greatly appreciated,

Paul

 

[Sahil Malik [MVP C#]]

Paul,

Good question.

Right after Merge, run DataTable.Select(.. .. DatViewRowState.Added) <-- or
DataTable.Select("id < 0") ; and remove those rows.

If you *must* require Merge to take care of this, then you need to create an
additional column - mark that as PK in your disconnected DataTable instead -
and keep your "-1" logic as is (identity, but not PK). Then when you "merge"
the -1's will no longer be PK, and the unique keys will override the PK. You
will also have to reverse the direction of the Merge.

Sounds like more pain than worth it, plus it really doesn't give you any
significant advantage - Just use approach #1 of removing rows

- Sahil Malik [MVP]
ADO.NET 2.0 book -
http://codebetter.com/blogs/sahil.malik/archive/2005/05/13/63199.aspx
__________________________________________________________

 

[Paul Delcogliano]

Thanks Sahil. I will give both of these solutions a try.