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.
Private Sub OrderDetailBindingSource_ListChanged(ByVal sender As Object, _
ByVal e As System.ComponentModel.ListChangedEventArgs) _
Handles OrderDetailBindingSource.ListChanged
If Me.OrderDetailBindingSource.Count > 0 Then
Dim filter As New System.Text.StringBuilder()
For Each orderDetail As DataRowView In CType(Me.OrderDetailBindingSource.List, DataView)
If filter.Length <> 0 Then
' Adding criteria
filter.Append(" OR ")
End If
filter.Append(String.Format("ProductID = {0}", orderDetail!ProductID))
Next
Me.ProductBindingSource.Filter = filter.ToString
Else
' display no rows
Me.ProductBindingSource.Filter = "1 = 0"
End If
End Sub
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.
Happy Data Binding!
Beth is a Program Manager on the Visual Studio Community Team at Microsoft and is responsible for producing and managing content for business application developers, driving community features and team participation onto MSDN Developer Centers (http://msdn.com), and helping make Visual Studio one of the best developer tools in the world. She also produces regular content on her blog (http://blogs.msdn.com/bethmassi), Channel 9, and a variety of other developer sites and magazines. As a community champion and a long-time member of the Microsoft developer community she also helps with the San Francisco East Bay .NET user group and is a frequent speaker at various software development events. Before Microsoft, she was a Senior Architect at a health care software product company and a Microsoft Solutions Architect MVP. Over the last decade she has worked on distributed applications and frameworks, web and Windows-based applications using Microsoft development tools in a variety of businesses. She loves teaching, hiking, mountain biking, and driving really fast.
