Creating a Friendly User Interface for Many to Many Relationship Scenario

Level: Advanced
Knowledge Required: To understand the following solution you must have the knowledge of:

• Typed DataSets
• DataTables
• Data Binding
• DataGridView Control

Description:

In our daily life development, we usually face a scenario where we need to create a user interface for many to many relationship scenario. For example I will discuss here a scenario where we require to store the Shops along with the Products they deal in.

We have created 2 Typed DataSets here:

• Product DataSet
• Shop DataSet

Product DataSet contains 1 Table i.e. Product. We will load products in this Table. Note that I haven’t created any Physical Database here, so I will be manually filling some products in this table at runtime.

In the Shop DataSet we have 2 Tables:

• Shop Table
• ShopProducts Table

ShopProducts Table is a Junction Table, which means a shop can have multiple Products. One way to create this interface is to place a DataGridView control for entering the Shop’s Products as I have shown in the following figure:

As you can see user can add multiple Products in this list by opening the ComboBox and selecting a Product. This way of creating user interface is fine but NOT friendly. As you can see whenever user opens the ComboBox all the Products display no matter how many products user has already selected. Therefore whenever user tries to duplicate a product the Primary Key Violation exception will occur which we have handled in the DataError event of the DataGridView control. You can note that this Exception which user sees is also NOT Friendly.

So to make it simple we can add a Checked List Box here, in which all the products are displayed and user just has to check the products which he/she wants to be added with Shop. Since Checked List Box control is a bit old fashioned and NOT a good looking control so we will use the same DataGridView control but in a different way.

We will now add another DataSet here i.e. ProductSelectionDataSet. This DataSet is same as ProductDataSet except that it’s Product Table contains a field Boolean field IsSelected, which will be used to check whether a product is selected or NOT.

Next we will replace current DataGridView control with another DataGridView control having 2 Columns

• IsSelected Column
• Product Name Column

We will setup this DataGridView in the following manner:

• AllowUserToAddRows = False
• AllowUserToDeleteRows = False
• RowHeadersVisible = False
• ReadOnly = True

Also we will set the 1st Column i.e. IsSelected column as:

• HeaderText = “” (empty string, we don’t want its header to be displayed)
• Resizable = False
• Width = 32

And for the 2nd column (Product Name):

• HeaderText = “Product”
• AutoSizeMode = Fill

This will make our DataGridView a bit like Checked ListBox. Now we just have to handle some of the events. First we will create a method SelectCurrentShopProducts; this method will select the Products according the given Products in the ShopProduct Table (of ShopDataSet). This method will be executed each time when Binding Source’s Position is changed.

Private Sub SelectCurrentShopProducts()
    ' first we will clear the currently selected shops
    Call Me.ClearAllSelection()

    Dim drvShop As DataRowView
    ' get current shop
    drvShop = Me.ShopBindingSource.Current
    ' if there is some shop selected
    If drvShop IsNot Nothing Then
        Dim rowShop As ShopDataSet.ShopRow
        Dim rowsProduct() As ShopDataSet.ShopProductsRow
        ' get the row from shop table
        rowShop = CType(drvShop.Row, ShopDataSet.ShopRow)
        ' get the current selected products of that shop
        rowsProduct = rowShop.GetShopProductsRows()
        ' for each selected product
        For Each r As ShopDataSet.ShopProductsRow In rowsProduct
           Dim rowProdSel As ProductSelectionDataSet.ProductWithSelectionRow
           ' get the product row from ProductWithSelection table
            rowProdSel =Me.ProductSelectionDataSet.ProductWithSelection.FindByProd_ID(r.Prod_ID)
           ' and mark it as selected
            rowProdSel.IsSelected = True
        Next
    End If
End Sub

Private Sub ClearAllSelection()
    For Each r As ProductSelectionDataSet.ProductWithSelectionRow In Me.ProductSelectionDataSet.ProductWithSelection
        r.IsSelected = False
    Next
End Sub

Next we will handle the CellContentClick event of DataGridView control. This event will be triggered whenever the CheckBox is checked or unchecked. In this event we will first check: If CheckBox is NOT selected then we will add this Product in our ShopProduct table otherwise if CheckBox is selected then it means we have already added this Product in ShopProduct table so now we will remove it.

Private Sub ProductWithSelectionDataGridView_CellContentClick(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles ProductWithSelectionDataGridView.CellContentClick
    If e.RowIndex >= 0 Then
        ' if checkbox is clicked
        If e.ColumnIndex = Me.ColumnIsSelected.Index Then
           Try
               Me.ShopBindingSource.EndEdit()

               Dim rowProdSel As ProductSelectionDataSet.ProductWithSelectionRow
                rowProdSel = CType(Me.ProductWithSelectionDataGridView.Rows(e.RowIndex).DataBoundItem, DataRowView).Row
                   ' if currently selected then we will de-select it
                   If rowProdSel.IsSelected Then
                   Try
                       Call Me.RemoveProductFromCurrentShop(rowProdSel.Prod_ID)
                        rowProdSel.IsSelected = False
                   Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "De-Select Shop")
                   End Try
               Else ' if NOT selected then we will select it
                   Try
                       Call Me.AddProductInCurrentShop(rowProdSel.Prod_ID)
                        rowProdSel.IsSelected = True
                   Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Select Shop")
                   End Try
                End If
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Edit Shop")
           End Try
        End If
    End If
End Sub

Private Sub AddProductInCurrentShop(ByVal Prod_ID As Integer)
    Dim drvShop As DataRowView
    Dim rowNew As ShopDataSet.ShopProductsRow
    ' get the current shop
    drvShop = Me.ShopBindingSource.Current
    ' create new ShopProduct Row
    rowNew = Me.ShopDataSet.ShopProducts.NewShopProductsRow()
    ' set values
    With rowNew
        .Shop_ID = drvShop("Shop_ID")
        .Prod_ID = Prod_ID
    End With
    ' add it in DataTable
    Me.ShopDataSet.ShopProducts.AddShopProductsRow(rowNew)
End Sub

Private Sub RemoveProductFromCurrentShop(ByVal Prod_ID As Integer)
    Dim drvShop As DataRowView
    Dim rowShopProduct As ShopDataSet.ShopProductsRow
    ' get the current shop
    drvShop = Me.ShopBindingSource.Current
    ' get the ShopProductRow
    rowShopProduct = Me.ShopDataSet.ShopProducts.FindByShop_IDProd_ID(drvShop("Shop_ID"), Prod_ID)
    rowShopProduct.Delete()
End Sub

NOTE: We have used the READONLY version of DataGridView that means when user clicks on the CheckBox in first column, then it does NOT get checked or un-checked Automatically. We handle this in the CellContentClick event of the DataGridView control. In this event we set the IsSelected = True or False which automatically updates the CheckBox in DataGridView control since it is binded to this table.

Download the full code from here:

JunctionTable.rar