具有交叉引用表（多对多）的数据绑定

引言

我看到很多关于如何将主表绑定到从表（当存在一个包含它们之间交叉引用的第三个表时）的帖子没有得到解答。

以下是一个具有这种结构的数据库示例

好了

首先，我们需要填充一个 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

接下来，我们需要在表之间创建数据关系。

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

现在，我们需要将表绑定到 DataGrid！ 这不是显而易见的部分！！

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

就这样了……关键是在绑定第二个表时使用关系！！

希望我的文章对您有所帮助。完整的示例将很快提供。