Sunday, March 07, 2010

Uncommitted Child Table Changes

A few months ago, there was a question on the MSDN forums that reminded me of a similar question quite some time ago that I had answered. The issue crops up with parent/child DataTables using Relations. The symptoms of the problem are that your Child table is often left with uncommitted changes, even if you thought you properly did an .EndEdit() on that Child table. This blog post will explain why that happens and what to do about it.

OK, so let’s get some sample stuff set up first.

// First, let's set the BindingSource using MyRelation
this.bsParent = new BindingSource();
this.bsChild = new BindingSource();

this.bsParent.DataSource = this.MyDataSet;
this.bsParent.DataMember = "MyTable";
this.bsChild.DataSource = this.bsParent;
this.bsChild.DataMember = "MyRelation";

// now bind a grid and a textbox
this.oGrid.DataSource = this.bsParent;
this.txtLastName.DataBindings.Add("Text", this.bsChild, "description");


If you make a change in the TextBox (bound to the Child table), and move to other rows in the Grid (the Parent table), maybe even making other changes in the TextBox for each Grid row, then click on a Save button, when you do this.MyDataSet.GetChanges(), you will be missing some of those changes from your Child table. Here’s why:

1) Data in a DataRow has several different versions, one of which is a “Proposed” version and your changes are still stuck in this Proposed state and the .GetChanges() method only gets those with the “Current” version. See my earlier blog entry for a more in-depth explanation of DataRow versions and one way of handling this issue:  http://geek-goddess-bonnie.blogspot.com/2009/09/fun-with-datasets.html

2) Because you’ve made changes to child records that are related to different parent records, then the bsChild.EndEdit() only commits the Proposed changes for the current relation, even though you've changed other rows with different parents.

At first, I thought a good solution to this was to create a third BindingSource associated with the entire Child table, having nothing whatsoever to do with the Relationship. That way, when you Save, you could call bsChildTable.EndEdit() rather than bsChild.EndEdit(). Sounds good in theory, but unfortunately, it did *NOT* work.

So, are we stuck using the CommitProposedChanges() method I created in the above-mentioned earlier blog post? (You *did* read that post I hope).  Well, it will still work just fine. But, because we are using relationships and BindingSources based on those relationships, we can speed it up a bit as follows:

// defining ParentTable simply for clarity of the example
DataTable ParentTable = this.MyDataSet.Tables["MyTable"];

for (int i = 0; i < ParentTable.Rows.Count; i++)
{
this.bsParent.Position = i;
this.bsParent.EndEdit();
this.bsChild.EndEdit();
}


So, all we’re doing is spinning through the Parent table, moving the “record pointer” (setting the .Position property) to each parent row. This then “re-sets” the current relationship with each iteration through the Parent table’s rows so that the bsChild.EndEdit() applies to each successive relation.

No comments:

Post a Comment