Define Table relation and load combined data into DataGrid : Table Relation « Database ADO.net « VB.Net






Define Table relation and load combined data into DataGrid

 
 Imports System
 Imports System.Drawing
 Imports System.Collections
 Imports System.ComponentModel
 Imports System.Windows.Forms
 Imports System.Data
 Imports System.Data.SqlClient

 
Public Class MainClass

    Shared Sub Main(  )
        Application.Run(New ADOForm1() )
    End Sub

   
End Class

  Public Class ADOForm1
     Inherits System.Windows.Forms.Form
     Friend WithEvents DataGrid1 As System.Windows.Forms.DataGrid

     Private components As System.ComponentModel.Container

     ' private System.Data.ADO.ADOConnection myConnection;
     Private myConnection As System.Data.SqlClient.SqlConnection
     Private myDataSet As System.Data.DataSet
     Private myCommand As System.Data.SqlClient.SqlCommand
     Private myCommand2 As System.Data.SqlClient.SqlCommand
     Private myDataAdapter As System.Data.SqlClient.SqlDataAdapter
     Private myDataAdapter2 As System.Data.SqlClient.SqlDataAdapter

     Public Sub New(  )
         InitializeComponent(  )

         ' create the connection object and open it
         Dim connectionString As String = _
             "server=localhost; uid=sa; " & _
             "pwd=YourPassword; database=northwind"

         myConnection = _
         New System.Data.SqlClient.SqlConnection(connectionString)
         myConnection.Open(  )

         ' create the DataSet and set a property
         myDataSet = New System.Data.DataSet(  )
         myDataSet.CaseSensitive = True

         ' create the SqlCommand  object and assign the
         ' connection and the select statement
         myCommand = New System.Data.SqlClient.SqlCommand(  )
         myCommand.Connection = myConnection
         myCommand.CommandText = "Select * from Employees"

         myCommand2 = New System.Data.SqlClient.SqlCommand(  )
         myCommand2.Connection = myConnection
         myCommand2.CommandText = "Select * from Orders"

         ' create the myDataAdapter object and pass in the
         ' SQL Command object and establish the table mappings
         myDataAdapter = New System.Data.SqlClient.SqlDataAdapter(  )
         myDataAdapter2 = New System.Data.SqlClient.SqlDataAdapter(  )
         myDataAdapter.SelectCommand = myCommand
         myDataAdapter2.SelectCommand = myCommand2
         myDataAdapter.TableMappings.Add("Table", "Employees")
         myDataAdapter2.TableMappings.Add("Table", "Orders")

         ' Tell the myDataAdapter object to fill the DataSet
         myDataAdapter.Fill(myDataSet)
         myDataAdapter2.Fill(myDataSet)

         Dim myDataRelation As System.Data.DataRelation
         Dim dataColumn1 As System.Data.DataColumn
         Dim dataColumn2 As System.Data.DataColumn
         dataColumn1 = _
             myDataSet.Tables("Employees").Columns("EmployeeID")
         dataColumn2 = _
             myDataSet.Tables("Orders").Columns("EmployeeID")

         myDataRelation = New System.Data.DataRelation( _
             "EmployeesToOrders", dataColumn1, dataColumn2)

         myDataSet.Relations.Add(myDataRelation)

         Dim dataSetView As DataViewManager = _
             myDataSet.DefaultViewManager

         ' display it in the grid
         DataGrid1.DataSource = _
             dataSetView
         DataGrid1.DataMember = "Employees"

     End Sub 'New

     Private Sub InitializeComponent(  )
         Me.components = New System.ComponentModel.Container(  )
         Me.dataGrid1 = New System.Windows.Forms.DataGrid(  )
         dataGrid1.Location = New System.Drawing.Point(48, 24)
         dataGrid1.Size = New System.Drawing.Size(368, 160)
         dataGrid1.TabIndex = 0
         Me.Text = "ADOFrm1"
         Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
         Me.ClientSize = New System.Drawing.Size(464, 273)
         Me.Controls.Add(dataGrid1)
     End Sub 

 End Class 
           
         
  








Related examples in the same category

1.DataRelation: Create a relation between the two tablesDataRelation: Create a relation between the two tables
2.Relational Data Navigation