Imports System.Data.SqlClient

Public Class Relation

    Private Sub cmdFill_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdFill.Click

        Dim Connect As String = "Data Source=localhost;Integrated Security=True;" & _
          "Initial Catalog=Northwind;"
        Dim con As New SqlConnection(Connect)

        Dim SQL As String = "SELECT * FROM Orders " & _
         "WHERE OrderDate < '2000/01/01' AND OrderDate > '1987/01/01'"
        Dim cmd As New SqlCommand(SQL, con)

        Dim adapter As New SqlDataAdapter(cmd)
        Dim dsNorthwind As New DataSet()

        con.Open()

        adapter.Fill(dsNorthwind, "Orders")

        ' This command is still linked to the data adapter.
        cmd.CommandText = "SELECT * FROM Customers"
        adapter.Fill(dsNorthwind, "Customers")

        cmd.CommandText = "SELECT * FROM Employees"
        adapter.Fill(dsNorthwind, "Employees")

        con.Close()

        ' Define the relation.
        Dim relCustomersOrders As New DataRelation("CustomersOrders", _
         dsNorthwind.Tables("Customers").Columns("CustomerID"), _
         dsNorthwind.Tables("Orders").Columns("CustomerID"))

        ' Add the relation to the DataSet.
        dsNorthwind.Relations.Add(relCustomersOrders)



        Dim rowParent, rowChild As DataRow
        For Each rowParent In dsNorthwind.Tables("Customers").Rows
            For Each rowChild In rowParent.GetChildRows(relCustomersOrders)
                ' Display combined information using both rows..
                lstOrder.Items.Add(rowParent("CompanyName") & _
                 " ordered on " & rowChild("OrderDate"))
            Next
        Next

    End Sub

    Private Sub cmdClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClear.Click
        lstOrder.Items.Clear()
    End Sub
End Class