kaborka

This is weird. I have a typed DataSet with two tables, tblHeader and tblDetail, related by a FK relationship declared in the DataSet designer. After making inserts and changes to both tables, I want to use the TableAdapters to write the changes to MSSQL. After doing the Update on tblHeader, however, the RowState of rows in tblDetail is changed to Unchanged, and no update occurs when I use the tblDetail TableAdapter. Both updates are bound to a SqlTransaction:

Code Snippet

sqlTran = MyConn.BeginTransaction();

... set SqlCommand.Transaction to sqlTran in all commands in both TableAdapters

... confirm that tblDetail[0].RowState == Added

tblHeaderTableAdapter.Update(tblHeader);

... check tblDetail[0].RowState and discover it is now Unchanged

tblDetailTableAdapter.Update(tblDetail);

sqlTran.Commit();

... clear SqlCommand.Transaction

... confirm that NO changes were written to tblDetail in the SQL database

Can anyone explain this behavior What should I look for It's as if AcceptChanges() is being called on tblDetail as a result of the Update on tblHeader.


Re: .NET Framework Data Access and Storage Update on one table clears RowState in related table

kaborka

Deleting the FK relationship from the typed DataSet designer fixed the problem! Without the FK in the DataSet, the RowState of tblDetail[0] remains 'Added' after the Update to tblHeader.

I had intended the FK in the DataSet to cascade identity column values in tblHeader to the related records in tblDetail when inserts are made to tblHeader in the SQL database. I will now have to do this myself in code, rather than relying on the FK rel. as I had hoped would work. Am I totally misunderstanding how the FK rel works in a .Net DataSet

Edit: I entered this as a bug.

https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx FeedbackID=299617





Re: .NET Framework Data Access and Storage Update on one table clears RowState in related table

Dragonlips

I'm no expert at the DataSet's still but this problem is probably caused by having the Accept/Reject Rule set to Cascade in the FK relationship in the DataSet designer. If you set it to "none" then your keys will update/propogate properly between tables but each table will maintain it's own state properly.




Re: .NET Framework Data Access and Storage Update on one table clears RowState in related table

kaborka

I set this up again and tested Drangonlips' suggestion and made sure that Accept/Reject rule on the FK rel was set to 'None'. Also, since MS's response to my bug report implied that AcceptChangesDuringUpdate needed to be False, I tried that setting. With these revisions, the assignment of the database identity value did propagate to new rows in the child (FK) table, and the RowState of those new rows was not reset (remained 'Added').

I tried again with AcceptChangesDuringUpdate set to 'true' on the parent (PK) table, and it still worked the same, so I'm not sure about why MS's response about that setting was pertinent.

Dragonlips gets credit for the answer on this.