none
DataTable.Update is breaking all my relationships, what am I doing wrong? RRS feed

  • Question

  • I'm trying to update a parent table (which has an autogenerated key on the database) and then update a related child table, and I'm having some difficulties. I cannot find any good examples of how to do this. MSDN documentation on DataTable relationships seems focused on getting and navigating relationships, but not on inserting. So resources on the topic overall would be appreciated.

    Here's what I'm doing at runtime:

    - Adding a row to the parent table

    - Adding a row to the child table and setting the parent using SetParent

    - Using a breakpoint, I'm verifying that this part works well.

         * Calls to GetParent("relationshipName") return the correct value.

         * The FKId column in the child show the placeholder Id from the parent's PKId column (e.g. -1, -2, etc.)

    - I'm then updating as follows (I'm declaring conn and trans appropriately prior to executing this code):

     

    using (MyDataSetTableAdapters.tblParentTableAdapter adapter = new MyDataSetTableAdapters.tblParentTableAdapter())
    {
    	adapter.Connection = conn;
    	adapter.Transaction = trans;
    	adapter.Update(m_MyDataSet.tblParent); // I put a breakpoint here
    }
    
    using (MyDataSetTableAdapters.tblChildTableAdapter adapter = new MyDataSetTableAdapters.tblChildTableAdapter())
    {
    	adapter.Connection = conn;
    	adapter.Transaction = trans;
    	adapter.Update(m_MyDataSet.tblChild);
    }
    

    At the commented line above I put a breakpoint. Before that line is executed, everything is fine. Afterwards, the relationship is severed and calls to get the parent of the child return null. It seems obvious as to why, the child still refers to the placeholder Id (e.g. -1, -2, etc.) but the parent has a new PKId from the DB. So understanding what has happened isn't my issue, my issue is I don't know how this is supposed to work. Am I expected to maintain this relationship by subscribing to the RowUpdated or RowUpdating events and then rebuild the relationships myself, or some other similarly "manual" process? Do I need to structure my connection and/or transaction differently.

    Also, as a bonus to difficulty, I cannot use the TableAdapterManager because it stomps on transactions, and I have things to do from other DataSets in a single, shared transaction.

    Wednesday, September 7, 2011 5:29 PM

Answers

  • I changed the relationship in the DataSet Designer from "Relationship" to "Relationship and Foreign Key" and then set the Update behavior to "Cascade".

    This resolved the issue.

    • Marked as answer by Sid Forcier Wednesday, September 7, 2011 5:40 PM
    • Unmarked as answer by Sid Forcier Wednesday, September 7, 2011 5:41 PM
    • Marked as answer by Sid Forcier Wednesday, September 7, 2011 5:41 PM
    Wednesday, September 7, 2011 5:40 PM

All replies

  • I changed the relationship in the DataSet Designer from "Relationship" to "Relationship and Foreign Key" and then set the Update behavior to "Cascade".

    This resolved the issue.

    • Marked as answer by Sid Forcier Wednesday, September 7, 2011 5:40 PM
    • Unmarked as answer by Sid Forcier Wednesday, September 7, 2011 5:41 PM
    • Marked as answer by Sid Forcier Wednesday, September 7, 2011 5:41 PM
    Wednesday, September 7, 2011 5:40 PM
  • Hi Sid,

    Thanks for sharing your experience here.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, September 9, 2011 3:32 AM
    Moderator