Relational Data Navigation : Table Relation « Database ADO.net « VB.Net

1. VB.Net
2. Database ADO.net
3. Table Relation

Relational Data Navigation

  
Imports System.Data.SqlClient
Imports System.Windows.Forms
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _
Partial Class Relation
    Inherits System.Windows.Forms.Form

    <System.Diagnostics.DebuggerStepThrough()> _
    Private Sub InitializeComponent()
        Me.cmdClear = New System.Windows.Forms.Button
        Me.cmdFill = New System.Windows.Forms.Button
        Me.lstOrder = New System.Windows.Forms.ListBox
        Me.SuspendLayout()
        '
        'cmdClear
        '
        Me.cmdClear.Anchor = CType((System.Windows.Forms.AnchorStyles.Bottom Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me.cmdClear.Location = New System.Drawing.Point(210, 238)
        Me.cmdClear.Name = "cmdClear"
        Me.cmdClear.Size = New System.Drawing.Size(72, 24)
        Me.cmdClear.TabIndex = 5
        Me.cmdClear.Text = "Clear"
        '
        'cmdFill
        '
        Me.cmdFill.Anchor = CType((System.Windows.Forms.AnchorStyles.Bottom Or System.Windows.Forms.AnchorStyles.Left), System.Windows.Forms.AnchorStyles)
        Me.cmdFill.Location = New System.Drawing.Point(10, 238)
        Me.cmdFill.Name = "cmdFill"
        Me.cmdFill.Size = New System.Drawing.Size(72, 24)
        Me.cmdFill.TabIndex = 4
        Me.cmdFill.Text = "Fill"
        '
        'lstOrder
        '
        Me.lstOrder.Anchor = CType((((System.Windows.Forms.AnchorStyles.Top Or System.Windows.Forms.AnchorStyles.Bottom) _
                    Or System.Windows.Forms.AnchorStyles.Left) _
                    Or System.Windows.Forms.AnchorStyles.Right), System.Windows.Forms.AnchorStyles)
        Me.lstOrder.IntegralHeight = False
        Me.lstOrder.Location = New System.Drawing.Point(10, 3)
        Me.lstOrder.Name = "lstOrder"
        Me.lstOrder.Size = New System.Drawing.Size(272, 229)
        Me.lstOrder.TabIndex = 3
        '
        'Relation
        '
        Me.AutoScaleDimensions = New System.Drawing.SizeF(6.0!, 13.0!)
        Me.AutoScaleMode = System.Windows.Forms.AutoScaleMode.Font
        Me.ClientSize = New System.Drawing.Size(292, 267)
        Me.Controls.Add(Me.cmdClear)
        Me.Controls.Add(Me.cmdFill)
        Me.Controls.Add(Me.lstOrder)
        Me.Font = New System.Drawing.Font("Tahoma", 8.25!, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, CType(0, Byte))
        Me.Name = "Relation"
        Me.Text = "Relation"
        Me.ResumeLayout(False)

    End Sub
    Friend WithEvents cmdClear As System.Windows.Forms.Button
    Friend WithEvents cmdFill As System.Windows.Forms.Button
    Friend WithEvents lstOrder As System.Windows.Forms.ListBox

    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 " 
        Dim cmd As New SqlCommand(SQL, con)

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

        con.Open()

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

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

        con.Close()
        Dim relCustomersOrders As New DataRelation("CustomersOrders", _
         dsNorthwind.Tables("Customers").Columns("CustomerID"), _
         dsNorthwind.Tables("Orders").Columns("CustomerID"))

        dsNorthwind.Relations.Add(relCustomersOrders)
        Dim rowParent, rowChild As DataRow
        For Each rowParent In dsNorthwind.Tables("Customers").Rows
            For Each rowChild In rowParent.GetChildRows(relCustomersOrders)
                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

   
    
  

Related examples in the same category

1.	DataRelation: Create a relation between the two tables
2.	Define Table relation and load combined data into DataGrid