Many-to-Many Data Binding

Many-to-Many Data Binding

Rate This
  • Comments 37

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!

Leave a Comment
  • Please add 3 and 6 and type the answer here:
  • Post
  • This is a great hack for supporting n to n databinding... but unfortunalty, it doesn't scale correctly... If there is too much data, the Filter can't cope with all those "x1 OR x2 OR x3...".

  • Hi Erik,

    You're correct in pointing out my example is not scalable but not because of the filter code. It's not good practice to bring down all the data in your database, *that* is what is not scalable. I show how to create paramererized queries in my video series and also I have a post on how to do that. Remember that when we build the filter for the Products, it's done by reading the *filtered* set of OrderDetails. So unless you brought down thousands of OrderDetails that were on one SINGLE Order, you'd be just fine. This would not be a scenario to worry about in my Order - OrderDetails scenario, however if you did have a scenario like this then make sure to create a parameterized query that also limits the linking table's rows.

    Cheers,

    -B

  • In your "CreatingYour Database.wmv" video serries, how did you define your snippets. I recreated your sample 5 times already and I get everything correctly except the storedprocedure node, on the toolbox tab, there are no usable controls in this group. What did I miss? Can you help me on this issue? Thanks.

  • Hi Edilberto,

    You can create code snippets by selecting text in the code editor and then dragging that onto the toolbox. Then you can use that snippet of code again by dragging from the toolbox into the editor.

    I wrote the stored proceedures and saved them as snippets for the video. They are contained in the database -- look in the Server Explorer under "Stored Proceedures".

    HTH,

    -B

  • I followed instruction in your "Connecting To Database" video, but how come it seems like it doesnt work for me.  It was all connected to database but mine won't update, insert or delete like yours? What did I miss? can you please help? Thanks.

  • Hi Mikaela,

    Please read this post to see if it resolves your issue: http://blogs.msdn.com/bethmassi/archive/2007/05/29/working-with-a-local-data-file-in-vs.aspx

    Cheers,

    -B

  • Thank you so much for the help, Beth! Wow! Thanks a lot! It works now! God bless!

  • Sorry, if I ask again. I follwed your instruction in "Creating A Search Video". My prob now is that, it won't search the Lastname that I enter. It is in the the list ofcourse, coz i added it before  searching. And also, after running, it deletes some datas. Why is that? Please help...Thanks!

  • Hi Mikaela,

    What is your search SELECT query? For help on writing SQL statements you can check out the SQL-Server Development center documentation here: http://msdn2.microsoft.com/en-us/sql/aa336367.aspx?wt.svl=1

    -B

  • How do I setup a one to one relationship. I am using an Access Database.

  • I have taken this excellent information and tried to apply it to my situation.  Instead of Orders 1:M to OrderDetails and Products 1:M to OrderDetails, I have Emps 1:M to EmpTitles (my bridge table for a M:M) and Titles 1:M to EmpTitles.  So, for instance, your OrderDetailBindingSource became my EmpTitlesBindingSource.  And, in the "foreach", your orderDetail became my empTitles.  But when I come to append the ProductID (which is my TitleID), the 3rd parameter won't take (it is titleId in both my Titles and EmpTitles tables).  I thought perhaps it was using a property, so I changed it to TitleId.  In either case, it errs and tells me "the name does not exist in the current context."  What in the world am I doing wrong?  Could it really be this hard?  I think not, but I would certainly appreciate any assistance.  Thanks!

  • it doesnt work if the "id" column properties is not an integer

  • Arthur,

    You just need to construct the filter properly. If the key is a string you need quotes around the value.

    filter.Append(String.Format("ProductID = '{0}'", orderDetail!ProductID))

  • Is it possibe to obtain that as sample project?

  • is it possible to make many-to-many relation whit sql query? Are ther other ways for it?

Page 1 of 3 (37 items) 123