none
Modified DataTables Not Updating DataSet RRS feed

  • Question

  • I'm having problems getting my modified DataTables back into my DataSet so that I can update my data source.

     

    I have a parent table Teacher and a child table Student_Teacher.  I get the new identity value from Teacher and assign it to the children records so that they are created with the correct ID.  My DataTables are appropriately modified in the code below, but I'm having difficulty understanding how the changes get back to the DataSet, then data source. 

    studentTeacherDataSet.AcceptChanges() is not getting the job done.

     

    I'm also having difficulty locating a DataSet-level transaction example.  Thanks!

     

    Code Snippet

    private void teacherBindingNavigatorSaveItem_Click(object sender, EventArgs e)

    {

    this.Validate();

    this.teacherBindingSource.EndEdit();

     

    try

    {

    // Teacher records to update

    StudentTeacherDataSet.TeacherDataTable newTeacherDataTable = (StudentTeacherDataSet.TeacherDataTable)studentTeacherDataSet.Teacher.GetChanges(DataRowState.Added);

     

    // Student_Teacher records to update

    StudentTeacherDataSet.Student_TeacherDataTable newStudent_TeacherDataTable = (StudentTeacherDataSet.Student_TeacherDataTable)studentTeacherDataSet.Student_Teacher.GetChanges(DataRowState.Added);

     

    // Loop through Teacher records, insert new Teacher records, modify the DataSet children records with the new Teacher identity values

    foreach (StudentTeacherDataSet.TeacherRow rowTeacher in newTeacherDataTable)

    {

    // Get the current record's Teacher.TeacherID

    int startTeacherID = rowTeacher.TeacherID;

     

    // If it's a new Teacher, insert it (VS uses neg numbers for identity)

    if (startTeacherID < 0)

    {

    // Insert the new Teacher record into the DB, returning the new identity value

    teacherTableAdapter.Update(rowTeacher);

     

    // Modify the new Student_Teacher records

    foreach (StudentTeacherDataSet.Student_TeacherRow rowStudentTeacher in newStudent_TeacherDataTable)

    {

    if (startTeacherID == 

    rowStudentTeacher.TeacherID

    )

    {

    // Assign the new identity value to Student_Teacher.TeacherID

    rowStudentTeacher.TeacherID

    = rowTeacher.TeacherID;

    }

    }

    }

    }

    studentTeacherDataSet.AcceptChanges();

    }

     

    catch (Exception ex)

    {

    MessageBox.Show(ex.ToString());

    }

     

    finally

    {

    // Update remaining records

    tableAdapterManager.UpdateAll(studentTeacherDataSet);

    }

    }

     

     

    Monday, November 10, 2008 10:19 PM

Answers

All replies

  • The DataSet is a disconnected cache of data.

     

    Calling AcceptChanges will change the DataRow state from Added/Modified/Deleted to Unchanged/Detached.

    Then UpdateAll will see there are no changes in the DataSet and do nothing.

     

    This set of pages describe the concepts and tasks associated with saving data from your application to a database.

    http://msdn.microsoft.com/en-us/library/ms171932.aspx

     

    Tuesday, November 11, 2008 1:22 AM
    Moderator
  • Mark,

    I commented out the AcceptChanges() from my code but am still showing that the DataSet is not being updated with new, non-negative TeacherID's.

    The rowTeacher DataRows are inserted into the DB appropriately and the rowTeacher status is set to Unchanged by the Update() method.  However, TeacherID is not updated in the DataSet with the new value until after I call the UpdateAll() method, at which time I get a FK constraint error (because of its child below).

    The rowStudentTeacher DataRows are appropriately assigned their new TeacherID's and are marked as Added, but an inspection of the DataSet shows that these changes are never propogated out to the DataSet, even after the UpdateAll() call.  I could use Update() to insert these child rows in the foreach as well, but when I do call UpdateAll() I get the FK constraint error (since the DataSet is never replacing the temporary, negative keys with the new values, via my TableAdapter or DataTable commands).
     
    SQL Server Profiler shows that the UpdateAll() call at the end reperforms all SQL inserts with the old negative values, regardless of whether I called Update() previously.

    Are the DataTables I created from the DataSet by value or reference?  If value, is there a way to save the changes back to the DataSet.  I've tinkered with the Merge() method without any success.  (I checked all of the above in the DB or the Locals window)

    Also, shouldn't the tableAdapterManager.UpdateAll() be smart enough to do all of this (both insert and get back the parent identity, then insert the child with the same identity as a PK/FK) under the same UpdateAll() call because of its knowledge of table constraints and such?  This seems quite difficult for what should be a common task.  Thanks!

    Teacher
    -TeacherID (PK)

    Student
    -StudentID (PK)

    Student_Teacher
    -TeacherID (PK, FK)
    -StudentID (PK, FK)
    Wednesday, November 12, 2008 10:08 PM
  • Right clicking on the GetChanges() method, I went to "Go To Definition" and found the following comments in the DataTable class:

     

    Code Snippet

    // Summary:

    // Gets a copy of the System.Data.DataTable containing all changes made to it

    // since it was last loaded, or since System.Data.DataTable.AcceptChanges()

    // was called, filtered by System.Data.DataRowState.

    //

    // Parameters:

    // rowStates:

    // One of the System.Data.DataRowState values.

    //

    // Returns:

    // A filtered copy of the System.Data.DataTable that can have actions performed

    // on it, and later be merged back in the System.Data.DataTable using System.Data.DataSet.Merge(System.Data.DataSet).

    // If no rows of the desired System.Data.DataRowState are found, the method

    // returns null.

     

     

    The DataTable created by the GetChanges() method appears to be disconnected from the DataSet and must be combined with the original DataSet via the Merge() method.  The merge works, merging my DataSet with my modified DataTable.  However, because I changed a part of the concatenated key on my child row, it does not recocognize my change as a modification during the merge, but treats it as a new record.  It adds the "new" row and retains the old row, causing my UpdateAll() to fail because of the old Student_Teacher.TeacherID.

     

    I thought I'd try passing the row to the DataSet to be deleted before I modified and created the new one but I receive the following error, even though the row being passed to the DataSet for deletion is a copy from it.  An examination of the DataSet shows the original still exists.  Any ideas why the two wouldn't match?  Thanks.

     

    Message = "The given DataRow is not in the current DataRowCollection."

     

    Code Snippet

    private void teacherBindingNavigatorSaveItem_Click(object sender, EventArgs e)

    {

    this.Validate();

    this.teacherBindingSource.EndEdit();

    try

    {

    // Teacher records to update

    StudentTeacherDataSet.TeacherDataTable newTeacherDataTable = (StudentTeacherDataSet.TeacherDataTable)studentTeacherDataSet.Teacher.GetChanges(DataRowState.Added);

     

    // Student_Teacher records to update

    StudentTeacherDataSet.Student_TeacherDataTable newStudent_TeacherDataTable = (StudentTeacherDataSet.Student_TeacherDataTable)studentTeacherDataSet.Student_Teacher.GetChanges(DataRowState.Added);

     

    // Determine if there are any new Teacher records

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

    {

    // Loop through Teacher records, insert new Teacher records, modify the DataSet children records with the new Teacher identity values

    foreach (StudentTeacherDataSet.TeacherRow rowTeacher in newTeacherDataTable)

    {

    // Get the current record's Teacher.TeacherID

    int startTeacherID = rowTeacher.TeacherID;

     

    // Insert the new Teacher record, returning the new identity value

    teacherTableAdapter.Update(rowTeacher);

     

    // Modify the new Student_Teacher records

    foreach (StudentTeacherDataSet.Student_TeacherRow rowStudent_Teacher in newStudent_TeacherDataTable)

    {

    if (startTeacherID == rowStudent_Teacher.TeacherID)

    {

    // Remove row from DataSet

    studentTeacherDataSet.Student_Teacher.RemoveStudent_TeacherRow(rowStudent_Teacher);

     

    // Assign the new identity value to Student_Teacher.TeacherID

    rowStudent_Teacher.TeacherID = rowTeacher.TeacherID;

     

    // Insert the new Student_Teacher record

    student_TeacherTableAdapter.Update(rowStudent_Teacher);

    }

    }

    }

     

    // Merge into the DataSet

    studentTeacherDataSet.Teacher.Merge(newTeacherDataTable);

    studentTeacherDataSet.Student_Teacher.Merge(newStudent_TeacherDataTable);

    }

    }

    catch (Exception ex)

    {

    MessageBox.Show(ex.ToString());

    }

    finally

    {

    // Update remaining records

    tableAdapterManager.UpdateAll(studentTeacherDataSet);

    }

    }

     

     

     

     

    Friday, November 14, 2008 8:13 PM
  • I found that although the DataSet is aware of the database's constraints, it cannot automatically update a child's FK id when the parent's PK id is changed during the initial insert.  This update must be done manually, using the specific order required to CRUD the DataSet as in the following example:


    http://msdn.microsoft.com/en-us/library/4esb49b4.aspx

    Monday, February 2, 2009 4:55 PM