Many-to-Many Data Binding

In my Forms over Data videos series I show you how to create a one-to-many data entry form in video #3. Recently, I've had a few people ask how to create a form that displays a many-to-many relationship so I thought I'd post on how to do that today.

You can actually think of a many-to-many relationship as two one-to-many's and depending on how you are designing your UI, you may be displaying it one way or the other. Take the many-to-many relationship Orders --< OrderDetail >-- Product where an Order has many OrderDetails and Product also has many OrderDetails. Typically when users are entering the data, we're choosing to display one of the one-to-many relationships for editing. For instance we could have users enter one order at a time that displayed the Order fields as textboxes and the OrderDetails in a grid. Then we could choose Products from a dropdown list in the OrderDetails grid. (The Products are edited elsewhere on the Product Catalog form for instance.)

However when we want to display or report on data, we don't necessarily need it to be easy to enter the data, we want to be able to easily see the data instead. And depending on your UI and your target users, you still may be able to easily guide the users through editing. But what if we wanted to just get a list of all the Products on a particular Order? Or know all of the Orders for a particular Product? These are easy SQL queries but what if we want to reuse a DataSet we already have built, or we want to allow editing of the data?

Say I have a DataSet of what we're describing now: Orders --< OrderDetail >-- Product.

We want a form that will allow the users to scroll through a list of orders and see all the products on that order. So we just want two grids on a form, one of Orders and one of Product. To get the filtering of rows set up properly, we will use the same exact technique for setting up automatic filtering on a One-to-Many form, but with one additional manual step which I'll show you isn't so bad.

To get the form quickly designed and set up, from the Data Sources window I just drag the Orders table and it's related OrderDetails onto the Form. Make sure you select the related OrderDetail table under the Order table otherwise you won't get the automatic filtering set up on the BindingSources it creates for you.

Then drag the Product table onto the form and delete the OrderDetails grid becasue we don't want to display that to the user. This process sets up all the binding components in the Form's component tray properly for all three of our DataTables. It should look something like this:

Now when the position changes in the OrdersBindingSource, the OrderDetailBindingSource will filter the proper rows based on the selected Order. All that's left to do is to filter the ProductBindingSource based on this list of OrderDetails. So open up the code behind and we're going to handle the OrderDetailBindingSource.ListChanged event. In this handler we'll create the filter to apply on the ProductBindingSource based on the filtered rows in the OrderDetailsBindingSource.

Now when we run this form, as the user scrolls through the Order rows in the first grid, the Product rows are displayed in the second grid for the entire order. To really understand what is going on just take a look at how the BindingSources are set up in the property sheet. The OrdersBindingSource has the DataSet as the Datasource and the DataMember is set to "Order". Then the OrderDetailBindingSource has its Datasource set to the OrdersBindingSource and its DataMember to "OrderDetails". This sets up the One-to-Many chaining and automatic filtering as explained in video #3 of my Forms over Data videos series.

All that's left in this case is getting the filtering on the ProductBindingSource which has the same Datasource as the OrderBindingsource, the DataSet, but its DataMember is set to "Product". We do this by simply handling the ListChanged event on the OrderDetailBindingSource. These grids are editable too, just be careful that your users understand the filtering that's being applied when working with the Product grid.

If we have two table A and B, these two table related to parent table by column "Parent_ID", now I want to create another table that link all record in table A to each record in table B automatically based on there Parent_ID, are there way to do that, I really need your help because i'm stuck with this for over two week

I want to make a listbox containing all the dates that a Patient has visited a doctor. When the user clicks a specified date from the listbox the grid containing the fields Reason for visiting,Diagnosis and Receipt from that specific date are automatically displayed. Does anyone knows how to implement this.

swestfisher

4 Oct 2012 6:03 AM

Hi Beth, I don't expect you are monitoring this so long after, but I'm having a runtime problem I don't understand with this code. In "For Each orderDetail As DataRowView In CType(Me.OrderDetailBindingSource.List, DataView)" I get an error that a DataViewManager cannot be cast to a DataView. How can I control the list being the rows of the table and not the tables in the dataset as you have in your example?

Kevin Gallagher

4 Oct 2012 6:41 AM

First off I always enjoy reading Beth's blog and recommend that others do too as they can learn from her. Just one comment on the above code, although perfectly valid to use the bang operator for those that want nice easily read code don't use the bang operator, in this case we use filter.Append(String.Format("ProductID = {0}", orderDetail.Item("ProductID").ToString)) instead of filter.Append(String.Format("ProductID = {0}", orderDetail!ProductID))

Hi Beth

5 Mar 2013 5:57 AM

Thanks for the great tutorials.

Have you created a tutorial on how to populate the linking data table on a many-to-many relationship?

Regards Gareth.

David

17 May 2013 5:08 AM

So I got your solution working for my needs, to display the information, but how do I add and delete items?

Spike

11 May 2014 2:53 AM

This was a great help. Thanks.

TJ

18 Sep 2014 12:23 PM

How do I change the ListChanged event code if the data source tables are coming from a LINQ to SQL Class instead of a DataSet? I get an error, unable to cast the binding list to a System.Data.Dataview.