SqlDataAdapter, DiffGram, Merge, and an identity key

  • Thread starter Thread starter Brad Williams
  • Start date Start date
B

Brad Williams

This KB article describes a fix for a problem ("by design"!) using
SqlDataAdapter.Update followed by a DataSet.Merge:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q313540

The problem addressed is: The default behavior of SqlDataAdapter.Update sets
the private state of certain rows of the dataset incorrectly, so that if
that dataset was originally derived from a source dataset using
DataSet.GetChanges, then the updated "changes" dataset cannot be correctly
merged back into the source dataset (you get duplicate rows). This happens
when the primary key of the dataset is an identity column generated by the
DB.

The fix in the KB article boils down to adding an event handler to the
SqlDataAdapter like this:

private void dataAdapter_RowUpdated(object sender, SqlRowUpdatedEventArgs e)
{
if (e.StatementType == StatementType.Insert)
e.Status = UpdateStatus.SkipCurrentRow;
}

My question is: Are there any other side-effects of using this fix, or
situations where it will screw things up? Is there any reason not to ALWAYS
use this event handler?

A secondary question would be: Why does the article say to "check and
update each row manually instead of using AcceptChanges", but in their
example fix they don't follow this advice, and it seems to work out just
fine for them?

Thanks,
Brad Williams
 
Hi Brad,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you need to know the side-effects of
the solution provided by KB article Q313540 and why can't we call
AcceptChanges on the DataSet after updating. If there is any
misunderstanding, please feel free to let me know.

Based on my understanding, setting the Status property to
UpdateStatus.SkipCurrentRow prevents the DataAdapter from calling
AcceptChanges on the current DataRow. If the RowUpdated event is not
handled, the RowState will be set to Unchanged. The merge method will think
these Rows different from each other and result in duplicated rows. If we
skip calling AcceptChanges on the inserted rows, merge method will compare
the rows with original version and put the updated version of this row to
DS.

I think there might be problem using this solution in an async situation.
If we make changes to DS during updating, things might screw up when
merging, because the merge method might keep the wrong version of row. So
it's better to disable editing during update. As far as I know, this is the
best solution to handle this issue.

"Check and update each row manually instead of using AcceptChanges" means
remove "DS.AcceptChanges()" from the original code and add "If
e.StatementType = StatementType.Insert Then e.Status =
UpdateStatus.SkipCurrentRow" in the RowUpdated event. It might not have
been shown in sample code.

HTH. If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Found this article which has a little more info. I guess the implicit
answer (since neither 320301 nor 313540 says otherwise) is that you *have*
to do this workaround when (1) inserting via a diffgram dataset to a table
with an identity column and then (2) merging the updated diffgram back into
main dataset; and this fix has *no* bad side-effects to worry about. It
essentially fixes an ADO.NET bug. I presume that since the bug wasn't fixed
by 1.1, it will never be fixed.

http://support.microsoft.com/default.aspx?scid=kb;[LN];320301&product=vcSnet

There's a VB.NET version of the article too:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q310350
 
Hi Brad,

It is nice to know that you have had the problem resolved. Thanks for
sharing your experience with all the people here. If you have any
questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top