Sunday, February 25, 2018

Parent/Child DataBinding

I have seen a few questions lately on the MSDN Forums about synchronizing parent/child data on a Windows Form. I thought that I had written a blog post about this years ago, but apparently I'm mis-remembering (probably remembering all the Forum posts that I've answered in the past)! This particular post will deal with parent/child using DataSets, and we can deal with this in two ways: using DataRelations or using the Filter property of the child BindingSource.

First, let's take a look at using DataRelations ... this is a pretty straightforward process (especially if you are already using DataRelations).

// First, you'll need two BindingSources:
private BindingSource bsParent;
private BindingSource bsChild;

You obviously need to have a DataRelation between two DataTables in your DataSet. If you don't already know how to set that up, it's pretty straightforward:

this.dsCust.Relations.Add("CustomerOrdersRelation",
    this.dsCust.Tables["Customer"].Columns["CustID"],
    this.dsCust.Tables["Orders"].Columns["CustID"]);

I usually recommend that DataBinding be set up in the Form's Load event handler. You'd have code like this (this would be after you've already set up a DataRelation):

this.bsParent = new BindingSource();
this.bsChild = new BindingSource();
 
this.bsParent.DataSource = this.dsCust;
this.bsParent.DataMember = "Customer";
this.bsChild.DataSource = this.bsParent;
this.bsChild.DataMember = "CustomerOrdersRelation";

There are two key things to note here:
  1. The DataSource for the bsChild is the bsParent.
  2. The DataMember for the bsChild is the Relationship, rather than a DataTable. This is what makes the whole thing work.
Now, we can use the BindingSources to DataBind parent/child DataGridViews and/or TextBoxes or other Controls, depending on the functionality you need:

this.oGridCustomer.DataSource = this.bsParent;
this.oGridOrders.DataSource = this.bsChild;
this.txtOrderDescription.DataBindings.Add("Text", this.bsChild, "description");

There *is* one little "gotcha" that you sometimes need to watch out for. That's the subject of bsChild.EndEdit(). The potential problem and solution is described in my blog post: https://geek-goddess-bonnie.blogspot.com/2010/03/uncommitted-child-table-changes.html

However, if you *can't* (or don't want to) use a DataRelation, then you do this with a Filter on the child BindingSource. It's only slightly different, because we have to utilize the bsParent.PositionChanged event handler to reset the Filter.

The bsParent is set up the same way as it was above. It's the bsChild that will be different:

bsChild.DataSource = this.dsCust.Tables["Orders"];
bsChild.Filter = string.Format("CustID = '{0}'", this.dsCust.Tables["Customer"].Rows[bsParent.Position]["CustID"]);
bsParent.PositionChanged += bsParent_PositionChanged;

And, for the Parent PositionChanged event handler, we just reset the Child Filter:

private void bsParent_PositionChanged(object sender, EventArgs e)
{
    bsChild.Filter = string.Format("CustID = '{0}'", this.dsCust.Tables["Customer"].Rows[bsParent.Position]["CustID"]);
}

And, obviously, since the two grids and the TextBox are set up using the bsParent and bsChild, nothing needs to change there.

In either scenario, as the user moves through the rows in the Customer grid, the corresponding child rows will be all that's displayed in the Order grid and the corresponding Order description in the textbox.