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.


6 comments:

  1. This is Excellent text !!!

    What is your recommendation: DataRelations or filter (note: if we consider that we are working with more records) ?

    ReplyDelete
    Replies
    1. I think that if you are already using DataRelations (for other reasons), then DataRelations are the way to go with the Child BindingSource.

      DataRelations can have their own issues (such as problems with FK Constraints if you're not careful).

      If you haven't needed to use a DataRelation for the rest of your application, then stick with Filters.

      Delete
  2. Thank you for your answer.
    Whether the number of records affects the performance if the filter is used ?

    ReplyDelete
    Replies
    1. I don't know, I've never benchmarked the performance difference between Filter and DataRelations. They probably have similar performance, but without actually doing a test, that's just a guess.

      Delete
  3. In case when used Datarelation, What is the difference between:

    this.bsParent.DataSource = this.dsCust;
    this.bsParent.DataMember = "Customer";


    and

    this.bsParent.DataSource = this.dsCust.Customer
    without this.bsParent.DataMember = "Customer";

    next...
    this.bsChild.DataSource = this.bsParent;
    this.bsChild.DataMember = "CustomerOrdersRelation";

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

    ReplyDelete
    Replies
    1. There is no difference at all between

      this.bsParent.DataSource = this.dsCust;
      this.bsParent.DataMember = "Customer";

      and

      this.bsParent.DataSource = this.dsCust.Customer

      In fact, I would prefer the second one. I wrote it like the first one in my blog post, just for consistency with the bsChild (which does have to be specified like the first one).

      Delete