Databinding with Cross Reference table (many to many)

Introduction

I have seen many posts that have gone unanswered about how to databind a master table to a details table when there is a third table that holds the cross references between them.

Here is an example of a database with this structure:

Here we go:

First, we need to fill a DataSet.

Public Function FillDataSet() as DataSet
    Dim daNames As OleDbDataAdapter
    Dim daPhoneNumbers As OleDbDataAdapter
    Dim daNamePhone As OleDbDataAdapter
    Dim cmd As OleDbCommand
    Dim reader As OleDbDataReader
    Try
        conn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; "_
               & "Data Source=" & "D:\_MySource\FundRaiser\FundRaising.mdb")
        conn.Open()
        ds = New DataSet

        daNames = New OleDbDataAdapter
        Dim s As OleDbCommandBuilder = New OleDbCommandBuilder(daNames)
        ds = GetDataSet(ds, conn, daNames, _
             "SELECT * FROM tbl_Names", "tbl_Names")

        daPhoneNumbers = New OleDbDataAdapter
        ds = GetDataSet(ds, conn, daPhoneNumbers, _
             "SELECT * FROM tbl_PhoneNumbers", "tbl_PhoneNumbers")

        daNamePhone = New OleDbDataAdapter
        ds = GetDataSet(ds, conn, daNamePhone, _
             "SELECT * FROM tbl_Name_Phone", "tbl_Name_Phone")

    Catch

    End Try
    conn.Close()
    Return ds
End Function

Public Function GetDataSet(ByVal ds As DataSet, _
  ByVal conn As OleDbConnection, _
  ByVal Adapter As OleDbDataAdapter, _
  ByVal query As String, ByVal TableName As String) As DataSet
    Adapter.SelectCommand = New OleDbCommand(query, conn)
    Adapter.Fill(ds, TableName)
    Return ds
End Function

Next, we need to create the data relationships between the tables.

Public Sub CreateRelations(byref ds as DataSet)
    Dim parentCol As DataColumn
    Dim childCol As DataColumn
    Dim rel As DataRelation

    parentCol = ds.Tables("tbl_Names").Columns("NameID")
    childCol = ds.Tables("tbl_Name_Phone").Columns("NameID")
    ' Create DataRelation.
    rel = New DataRelation("NamePhone", parentCol, childCol)
    ' Add the relation to the DataSet.
    rel.Nested = True
    ds.Relations.Add(rel)

    parentCol = ds.Tables("tbl_Name_Phone").Columns("PhoneNumberID")
    childCol = ds.Tables("tbl_PhoneNumbers").Columns("PhoneNumberID")
    ' Create DataRelation.
    rel = New DataRelation("NamePhone_PhoneNumbers", parentCol, childCol)
    ' Add the relation to the DataSet.
    rel.Nested = True
    ds.Relations.Add(rel)

End Sub

Now, we need to bind the tables to the DataGrids! This is the not so obvious part!!

Private Sub BindDataSet(ByRef ds As DataSet)
    Call dgNames.SetDataBinding(ds, "tbl_Names")
    Call dgPhoneNumbers.SetDataBinding(ds, _
         "tbl_Names.NamePhone.NamePhone_PhoneNumbers")
End Sub

That's it... The key is to use the relationships when binding the second table!!!

I hope my article was helpful. A complete sample will be available soon.