Updating Dataset with Identity column

[Nick Chakarov]

Hi everybody,
I have a problem updating typed dataset with identity columns in it.The
dataset contains parent-children tables and the updates must happen in order
1. update deleted records in children
2. Update modified-added records in parent, children

The code goes like that: let say that MyDataSet is the dataSet under
consideration and all data adapters are setup to refresh the dataset after
update

DataSet deleted = MyDataSet.GetChanges(DataRowState.Deleted);
if (deleted != null) {
DataAdapter1.Update(deleted);
DataAdapter2.Update(deleted) and so on
MyDataSet.Merge(deleted);
}
DataSet added_modified =
MyDataSet.GetChanges(DataRowState.Added||DAtaRowState.Modified);
if (added_modified != null) {
DataAdapter1.Update(deleted);
DataAdapter2.Update(deleted) and so on
MyDataSet.Merge(deleted);
}
MyDataSet.AcceptChanges();
Well, and the end of that process The dataset doubles the added records,
because Merge does not actually merge new added records but adds them again.

So I setup a little sample with Northwind Categories table using VS.NET Data
Form generator to build the form. The behavior is repeatable in the
generated code as well. Initially in the table there are 9 records. After
adding one new record and pressing update button the data set contains 11
instead of 10 records. So apparently Merge does not merge properly or I am
missing something.

Any help is greatly appreciated
TIA

 

[Miha Markic [MVP C#]]

Hi Nick,

You should modify the merge behaviour.
Here is what I do.
Before doing any update, I store pairs of MyDataSet.Row and GetChanges.Row
in a list where row has Added status.
Then, before merging, I update the MyDataSet.Row id to the id from
GetChanges.row and only after this step I do Merge (which now correctly
matches rows since they both have same id).

 

[Nick Chakarov]

Hi Miha,
Thanks for the responce.
I've found MS suggested resolution for that problem which is easier .. Here
you go ..
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q313540

Hi Nick,

You should modify the merge behaviour.
Here is what I do.
Before doing any update, I store pairs of MyDataSet.Row and GetChanges.Row
in a list where row has Added status.
Then, before merging, I update the MyDataSet.Row id to the id from
GetChanges.row and only after this step I do Merge (which now correctly
matches rows since they both have same id).

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

Hi everybody,
I have a problem updating typed dataset with identity columns in it.The
dataset contains parent-children tables and the updates must happen in
order
1. update deleted records in children
2. Update modified-added records in parent, children

The code goes like that: let say that MyDataSet is the dataSet under
consideration and all data adapters are setup to refresh the dataset
after update

DataSet deleted = MyDataSet.GetChanges(DataRowState.Deleted);
if (deleted != null) {
DataAdapter1.Update(deleted);
DataAdapter2.Update(deleted) and so on
MyDataSet.Merge(deleted);
}
DataSet added_modified =
MyDataSet.GetChanges(DataRowState.Added||DAtaRowState.Modified);
if (added_modified != null) {
DataAdapter1.Update(deleted);
DataAdapter2.Update(deleted) and so on
MyDataSet.Merge(deleted);
}
MyDataSet.AcceptChanges();
Well, and the end of that process The dataset doubles the added records,
because Merge does not actually merge new added records but adds them
again.

So I setup a little sample with Northwind Categories table using VS.NET
Data Form generator to build the form. The behavior is repeatable in the
generated code as well. Initially in the table there are 9 records. After
adding one new record and pressing update button the data set contains 11
instead of 10 records. So apparently Merge does not merge properly or I
am missing something.

Any help is greatly appreciated
TIA

 

[Miha Markic [MVP C#]]

Jup, seems easier.

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

Hi Miha,
Thanks for the responce.
I've found MS suggested resolution for that problem which is easier ..
Here you go ..
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q313540

Hi Nick,

You should modify the merge behaviour.
Here is what I do.
Before doing any update, I store pairs of MyDataSet.Row and
GetChanges.Row in a list where row has Added status.
Then, before merging, I update the MyDataSet.Row id to the id from
GetChanges.row and only after this step I do Merge (which now correctly
matches rows since they both have same id).

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
SLODUG - Slovene Developer Users Group www.codezone-si.info

Hi everybody,
I have a problem updating typed dataset with identity columns in it.The
dataset contains parent-children tables and the updates must happen in
order
1. update deleted records in children
2. Update modified-added records in parent, children

The code goes like that: let say that MyDataSet is the dataSet under
consideration and all data adapters are setup to refresh the dataset
after update

DataSet deleted = MyDataSet.GetChanges(DataRowState.Deleted);
if (deleted != null) {
DataAdapter1.Update(deleted);
DataAdapter2.Update(deleted) and so on
MyDataSet.Merge(deleted);
}
DataSet added_modified =
MyDataSet.GetChanges(DataRowState.Added||DAtaRowState.Modified);
if (added_modified != null) {
DataAdapter1.Update(deleted);
DataAdapter2.Update(deleted) and so on
MyDataSet.Merge(deleted);
}
MyDataSet.AcceptChanges();
Well, and the end of that process The dataset doubles the added records,
because Merge does not actually merge new added records but adds them
again.

So I setup a little sample with Northwind Categories table using VS.NET
Data Form generator to build the form. The behavior is repeatable in the
generated code as well. Initially in the table there are 9 records.
After adding one new record and pressing update button the data set
contains 11 instead of 10 records. So apparently Merge does not merge
properly or I am missing something.

Any help is greatly appreciated
TIA