none
Dataset relation question and .GetChanges RRS feed

  • Question

  • Hi All,

    I have a question regarding using GetChanges when using 3 hierarchical datatables. First let me try to explain the schema.

    Inspection Datatable
    Has a parent relationship and foreign key contraint onto both the Items datatable and the failures datatable on the primary key

    Items Datatable
    Has a parent relationship and foreign key contraint on the Failures datatable on the primary key
    Has a child relationship on the Inpection datatable as stated above

    Failures Datatable
    Has a child relationship on the Inpection datatable as stated above
    Has a child relationship on the Item datatable as stated above

    I need both relationships on the Inspection table to update the ID on inserts.

    When loading the dataset and persisting new rows to the database it works fine but I get an issue when updating rows.

    For instance if I download an existing dataset and then add a new row to the Failures database then when calling the GetChanges method on the dataset it gives me the unchanged parent Inspection row, the changed Failure row but not the unchanged parent Item row. Obviously this causes a key violation when attempting the update server side.

    Is there any way around this?

    Thanks

    Phil


    Wednesday, March 10, 2010 12:36 PM

Answers

  • Phil,

    Any word on the behavior with the cascading constraints?  I'd like to repro the GetChanges behavior you're seeing and help provide an explanation or workaround if I can.

    Relying on GetChanges in the scenario you described makes sense.  Once the update succeeds from within the WCF service, you'll need to do some work on the client side if you want to continue to use the existing DataSet there since that DataSet does not contain the new ID values or other server-generated values.  It can be a bit of a challenge to synch things up manually but there are a few approaches.  However, I'd prefer that we tackle the GetChanges issue first, or at least keep this forum thread focused on GetChanges.


    David Sceppa
    • Marked as answer by Phil Murray Tuesday, December 14, 2010 4:44 PM
    Thursday, April 1, 2010 11:15 PM
    Moderator

All replies

  • Anyone care to comment?
    Monday, March 15, 2010 10:59 AM
  • Phil,

    Let's omit GetChanges from the equation for now.  GetChanges creates a separate copy of the DataSet with the requested changes, which can complicate things in many cases.  We can get back to GetChanges later.

    If I understand your scenario correctly, you're working with parent (Inspection), child (Items), grandchild (Failures) tables where each table uses an auto-increment column for the primary key, which is referenced in the foreign key constraints.

    There are three steps we'll walk through:
        1.)  Create related parent and child (and grandchild) rows in a DataSet
        2.)  Retrieve the database-generated auto-increment values as new rows are submitted to the database
        3.)  Cascade the database-generated auto-increment values to the related child DataRows
    Submitting the child inserts to the database involves following step 2, but with a different INSERT INTO query.


    Step 1.)  Create related parent and child (and grandchild) rows in a DataSet

         Set the ADO.NET DataColumn's AutoIncrement property to True and ADO.NET will generate placeholder values for new rows.  The 
    new values depend on the AutoIncrementStep, AutoIncrementSeed, and the last value used in the DataTable.  I strongly recommend setting 
    AutoIncrementSeed and AutoIncrementStep to -1.  These settings will generate placeholder values of -1, -2, -3, …  There are two 
    benefits to this approach.  The values won't conflict with any that actually exist in the database.  This is important.  I've seen situations where having the DataSet generate new values using the DataSet defaults ultimately led to the pending child inserts referencing the wrong parent rows and being submitted to the database without generating an error.  As a secondary benefit, the user will not 
    misinterpret the placeholder value as an actual value from the database.

         As you add the parent rows and ADO.NET generates placeholder values, use those placeholder values for your pending child rows. 
     The DataRelation object will make it easy to go from parent to child and back, either in code or in bound controls. 


    Step 2.)  Retrieve the database-generated auto-increment values as new rows are submitted to the database

         If you're using SQL Server, this process is actually very simple.  If you were writing your own queries, you would execute an "INSERT INTO…" query to insert the new row and then execute a "SELECT SCOPE_IDENTITY()" query to retrieve the last identity value generated on that connection. 

         The DataAdapter submits changes via its InsertCommand property.  You can append ";SELECT @@IDENTITY AS MyIDColumn" to the end of the "INSERT INTO..." query.  (SQL 2000 users should use "SELECT SCOPE_IDENTITY()..." instead of "SELECT @@IDENTITY".   See SQL Server Books OnLine for more information on why.)  If you're building your DataAdapters via Visual Studio .NET's DataAdapter Configuration Wizard, the wizard will do this for you 
    automatically. 

         If you're writing your code by hand, make sure the InsertCommand's UpdatedRowSource property is set to Both (the default) or FirstReturnedRecord.  This property controls whether the DataAdapter will fetch the row returned by the query and apply that data to the DataRow object. 

         This functionality is possible because SQL Server allows you to execute a batch of queries that returns rows.  However, not all databases support this feature. 

         If you're working with an Access database, you'll need to go a slightly different route.  Trap for the DataAdapter's RowUpdated event and use code to check for a successful insert.  Execute the "SELECT @@IDENTITY" query using a Command object and assign the value returned by the query to the appropriate column and call the DataRow object's AcceptChanges method.  Your code will look something like this: 

    //C#
    OleDbDataAdapter da = new OleDbDataAdapter(strSQL, strConn); 
    OleDbConnection cn = da.SelectCommand.Connection; 
    OleDbCommand cmdGetIdentity = new OleDbCommand("SELECT @@IDENTITY", cn); 
    da.RowUpdated += new OleDbRowUpdatedEventHandler(HandleRowUpdated); 
    DataTable tbl = CreateMyDataTable(); 
    da.Fill(tbl); 
    ... 
    da.Update(tbl); 
    
    private void HandleRowUpdated(object sender, OleDbRowUpdatedEventArgs e) { 
        if ((e.Status == UpdateStatus.Continue) && 
           ((e.StatementType == StatementType.Insert)) { 
            e.Row["OrderID"] = cmdGetIdentity.ExecuteScalar(); 
        } 
    }


    'Visual Basic
    Dim da As New OleDbDataAdapter(strSQL, strConn) 
    Dim cn As OleDbConnection = da.SelectCommand.Connection 
    Dim cmdGetIdentity As New OleDbCommand("SELECT @@IDENTITY", cn) 
    AddHandler da.RowUpdated, AddressOf HandleRowUpdated 
    Dim tbl As DataTable = CreateMyDataTable() 
    da.Fill(tbl) 
    ... 
    da.Update(tbl) 
    
    Private Sub HandleRowUpdated(ByVal sender As Object, _ 
                                 ByVal e As OleDbRowUpdatedEventArgs) 
        If e.Status = UpdateStatus.Continue AndAlso _ 
           e.StatementType = StatementType.Insert Then 
            e.Row("OrderID") = cmdGetIdentity.ExecuteScalar()
        End If 
    End Sub


         You can use similar techniques to retrieve server-generated values from other databases as well.  MySQL developers can use the "LAST_INSERT_ID()" instead of "@@IDENTITY" to retrieve the last auto-increment value generated.  Oracle developers can use "SELECT SequenceName.CURRVAL FROM DUAL" to retrieve the last value generated for a sequence on the connection.


    Step 3.)  Cascade the database-generated auto-increment values to the related child DataRows

         This is the simplest part of the process.  When you create a DataRelation object, ADO.NET will add a ForeignKeyConstraint object to make sure that child rows match up to a parent row.   The ForeignKeyConstraint object exposes a CascadeRule property.  If this property is set to True (the default), ADO.NET will automatically cascade changes made to the parent down to the associated child rows. 

         So, if you have a DataRelation set up between the DataTables based on the auto-increment column, and you've set the parent DataAdapter's InsertCommand to fetch the new auto-increment values from the database, ADO.NET will cascade the new values down to the associated child rows automatically. 

         I hope this information proves helpful.  For more information, see Chapter 11 of Microsoft ADO.NET, available through Microsoft Press.


    Back to GetChanges

         GetChanges creates a separate copy of the DataSet or DataTable with only the requested pending changes.  Submitting updates in this copy does not affect the original DataSet or DataTable, so the newly retrieved auto-increment values are not automatically integrated into the original DataSet.  There are ways to address this but for now I'd suggest working in a small isolated application with the original DataSet to verify that you're able to submit pending parent and child inserts successfully.  Once that's done, if you could provide a little background about your application architecture  and how you're using GetChanges (a WinForms app talking directly to the database, a WPF app relying on a WCF service to send DataSets to the client and submit pending changes, etc.) I'm sure someone will be able to help with that part of the equation.

    I hope this information proves helpful.


    David Sceppa
    Tuesday, March 16, 2010 10:02 PM
    Moderator
  • Thanks for the reponse David,

    You are almost correct on the table schema with the addition that there is a relationship from the parent table (inspection) to the grand child (failures). The columns are set up as they should be with the Autoincrement set to -1 and the updated key values returned and cascaded through the relationships on an insert.

    The problem I have, due to the additional relationship stated above, is that when calling GetChanges where the grandchild (failures) table has a change the changes dataset contains the parent table (inspection) row, with a rowstate of unchanged, and the grandchild (failure) row, with a row state of created, but middle table (item). I was expecting there to be an unchanged item row from the relationship between the child (item) and the grandchild (failures).

    As you can imagine when I try to persist the changes to the database I get a concurrency error due to the missing unchanged item row as the dataset can not understand the missing relationship.

    In my testing I remove the additional relationship so we has the standard parent -- child -- grandchild and the save worked as the GetChanges included the unchanged item row. I can't seem to get it to work with the additional parent -- grandchild relationship.

    Im sure thats all clear as mud

    Thanks

    Phil
    Wednesday, March 17, 2010 1:16 PM
  • Phil,

    I'd like to reproduce the behavior using (relatively) small code snippets.  I created the basic scenario using just a DataSet but couldn't reproduce the behavior you described.  The DataSet created via GetChanges has the pending grandchild row, as well as the corresponding parent and child rows.

    Maybe the easiest thing to do would be to have you work with the code and tweak it until you see the behavior you described.  Here's the code I'm using.

    //Create the DataSet structure
    DataSet mainDataSet = new DataSet();
    DataTable parentTable, childTable, grandchildTable;
    DataColumn col;
    
    parentTable = mainDataSet.Tables.Add("Parent");
    col = parentTable.Columns.Add("ParentID", typeof(int));
    col.AutoIncrement = true;
    col.AutoIncrementSeed = -1;
    col.AutoIncrementStep = -1;
    parentTable.Columns.Add("Description", typeof(string));
    parentTable.PrimaryKey = new DataColumn[] { col };
    
    childTable = mainDataSet.Tables.Add("Child");
    col = childTable.Columns.Add("ChildID", typeof(int));
    col.AutoIncrement = true;
    col.AutoIncrementSeed = -1;
    col.AutoIncrementStep = -1;
    childTable.Columns.Add("Description", typeof(string));
    childTable.Columns.Add("ParentID", typeof(int));
    childTable.PrimaryKey = new DataColumn[] { col };
    
    grandchildTable = mainDataSet.Tables.Add("GrandChild");
    col = grandchildTable.Columns.Add("GrandChildID", typeof(int));
    col.AutoIncrement = true;
    col.AutoIncrementSeed = -1;
    col.AutoIncrementStep = -1;
    grandchildTable.Columns.Add("Description", typeof(string));
    grandchildTable.Columns.Add("ParentID", typeof(int));
    grandchildTable.Columns.Add("ChildID", typeof(int));
    grandchildTable.PrimaryKey = new DataColumn[] { col };
    
    mainDataSet.Relations.Add("Parent_Child",
        parentTable.Columns["ParentID"],
        childTable.Columns["ParentID"]);
    
    mainDataSet.Relations.Add("Parent_Grandchild",
        parentTable.Columns["ParentID"],
        grandchildTable.Columns["ParentID"]);
    
    mainDataSet.Relations.Add("Child_Grandchild",
        new DataColumn[] { childTable.Columns["ParentID"], 
                           childTable.Columns["ChildID"] },
        new DataColumn[] { grandchildTable.Columns["ParentID"], 
                           grandchildTable.Columns["ChildID"] });
    
    
    DataRow parentRow, childRow, grandchildRow;
    
    //Create the pre-existing parent and child rows
    parentRow = parentTable.NewRow();
    parentRow["Description"] = "Pre-existing parent";
    parentTable.Rows.Add(parentRow);
    
    childRow = childTable.NewRow();
    childRow["ParentID"] = parentRow["ParentID"];
    childRow["Description"] = "Pre-existing child";
    childTable.Rows.Add(childRow);
    
    //Assign theoretical key values from database
    parentRow["ParentID"] = 10;
    childRow["ChildID"] = 100;
    
    //Accept the newly created rows to simulate
    //fetching them from the database
    mainDataSet.AcceptChanges();
    
    //Add a new grandchild row
    grandchildRow = grandchildTable.NewRow();
    grandchildRow["ParentID"] = childRow["ParentID"];
    grandchildRow["ChildID"] = childRow["ChildID"];
    grandchildRow["Description"] = "New grandchild";
    grandchildTable.Rows.Add(grandchildRow);
    
    DisplayRows(mainDataSet, "Main DataSet");
    
    DataSet changesDataSet = mainDataSet.GetChanges();
    DisplayRows(changesDataSet, "DataSet returned via GetChanges()");
    


    And here's the code I use to display the contents of the DataSet:

    static void DisplayRows(DataSet dataSet, string dataSetDescription)
    {
        Console.WriteLine(dataSetDescription);
        foreach (DataRow parentRow in dataSet.Tables["Parent"].Rows)
        {
            Console.WriteLine("  {0}, ParentID: {1}, RowState: {2}",
                parentRow["Description"],
                parentRow["ParentID"],
                parentRow.RowState);
    
            foreach (DataRow childRow in parentRow.GetChildRows(dataSet.Relations["Parent_Child"]))
            {
                Console.WriteLine("  {0}, ParentID: {1}, ChildID: {2}, RowState: {3}",
                    childRow["Description"], 
                    childRow["ParentID"], childRow["ChildID"], 
                    childRow.RowState);
    
                foreach (DataRow grandchildRow in childRow.GetChildRows(dataSet.Relations["Child_Grandchild"]))
                {
                    Console.WriteLine("  {0}, ParentID: {1}, ChildID: {2}, GrandchildID: {3}, RowState: {4}",
                        grandchildRow["Description"],
                        grandchildRow["ParentID"], grandchildRow["ChildID"], grandchildRow["GrandchildID"], 
                        grandchildRow.RowState);
                }
            }
        }
        Console.WriteLine();
    }
    

    I hope this information proves helpful.


    David Sceppa
    Wednesday, March 17, 2010 6:19 PM
    Moderator
  • Phil,

    It would also help if you could provide a little information about why you're relying on GetChanges within your application.  There may be better ways to handle the scenario that would avoid the problem you're experiencing.

    David Sceppa
    Wednesday, March 17, 2010 8:48 PM
    Moderator
  • Hi David,

    Your code example works as expected by bring both sets of unchanged parent rows with the GetChanges call. I think the problem may have something to do with the cascading constraints set against the parent and child table. I need to verify this though.

    The reason I am using the GetChanges on the dataset is to reduce the amount of traffic going across the wire for updates. Simply I capture the changed rows + related records, convert the XML to a compressed binary stream to send back to the WCF service on the server for DB updates. At the moment I have dropped the get changes and I am sending the complete dataset back but its a little inefficient.

    Phil
    Thursday, March 18, 2010 2:44 PM
  • Phil,

    Any word on the behavior with the cascading constraints?  I'd like to repro the GetChanges behavior you're seeing and help provide an explanation or workaround if I can.

    Relying on GetChanges in the scenario you described makes sense.  Once the update succeeds from within the WCF service, you'll need to do some work on the client side if you want to continue to use the existing DataSet there since that DataSet does not contain the new ID values or other server-generated values.  It can be a bit of a challenge to synch things up manually but there are a few approaches.  However, I'd prefer that we tackle the GetChanges issue first, or at least keep this forum thread focused on GetChanges.


    David Sceppa
    • Marked as answer by Phil Murray Tuesday, December 14, 2010 4:44 PM
    Thursday, April 1, 2010 11:15 PM
    Moderator
  • Phil,

    Just checking in.  Any updates on this issue?


    David Sceppa
    Wednesday, May 12, 2010 7:12 PM
    Moderator
  • Hi,

      I´m currently seeing the same problem, but trying to call GetChanges from a DataTable. In this case i´m getting a contraint error, that tells that there is an error in a parent relation. I have tried to disable constraints before calling the GetChanges method, but didn´t help. 

    I´m copying the piece of code...

        /// <summary>

        /// Check if the given BindingSource has at least one Row in edition mode.

        /// </summary>

        /// <param name="bindingSource"></param>

        /// <returns></returns>

        private bool GetUnderEdition(BindingSource bindingSource)

        {

          bool underEdition = false;

     

          if (bindingSource != null && bindingSource.DataSource != null)

          {

            DataView curDV = bindingSource.DataSource as DataView;

     

            //Cast has been successful

            if (curDV != null && curDV.Table != null)

            {

              //Disable constraints

              curDV.Table.DataSet.EnforceConstraints = false;

     

              //Call GetChanges() in the current table.

              DataTable curChangesTable = curDV.Table.GetChanges();

     

              if (curChangesTable != null && curChangesTable.Rows.Count > 0)

              {

                underEdition = true;

              }

     

              //Enable contraints

              curDV.Table.DataSet.EnforceConstraints = true;

            }

          }

     

          return underEdition;

        }

     

    Thanks in advance, 

    Paola

    Thursday, August 5, 2010 5:10 PM