Imports System

Imports System.Data

Imports System.Data.Linq

Imports System.Data.DataSetExtensions

Public Module Module1

    Public Sub Main()

        Console.WriteLine(".DataTable Join/Merge")

​

        Dim cust As DataSet = New DataSet  

        Dim custTbl As DataTable = cust.Tables.Add("CustomersTable")

​

        custTbl.Columns.Add("key", GetType(System.Int32))

        custTbl.Columns.Add("desc", GetType(System.String))

        custTbl.Rows.Add(100, "1desc")

        custTbl.Rows.Add(200, "2desc")

        custTbl.Rows.Add(300, "3desc")

        custTbl.Rows.Add(400, "4desc")

        custTbl.Rows.Add(500, "5desc")

        custTbl.Rows.Add(600, "6desc")

        custTbl.Rows.Add(800, "8desc")

        Console.WriteLine(custTbl.Rows.Count.ToString())

​

        Dim cust2 As DataSet = New DataSet  

        Dim cust2Tbl As DataTable = cust.Tables.Add("CustomersTable2")

​

        cust2Tbl.Columns.Add("key", GetType(System.Int32))

        cust2Tbl.Columns.Add("desc", GetType(System.String))

​

        cust2Tbl.Rows.Add(200, "x xxx")

        cust2Tbl.Rows.Add(300, "x 30cust")

        cust2Tbl.Rows.Add(350, "x 35cust")

        cust2Tbl.Rows.Add(400, "x 4cust")

        cust2Tbl.Rows.Add(550, "x 55cust")

        cust2Tbl.Rows.Add(600, "x 6cust")

        cust2Tbl.Rows.Add(700, "x 7cust")

        Console.WriteLine(custTbl.Rows.Count.ToString())

        Console.WriteLine(cust2Tbl.Rows.Count.ToString())       

        Dim outResults As DataSet = New DataSet  

        Dim ICASSOut   As DataTable = outResults.Tables.Add("ICASSOut")

​

        ICASSOut.Columns.Add("SSN", GetType(System.String))

        ICASSOut.Columns.Add("Name", GetType(System.String))

        ICASSOut.Columns.Add("APPN_Prior", GetType(System.String))

        ICASSOut.Columns.Add("APPN_Current", GetType(System.String))

        ICASSOut.Columns.Add("Allot_Prior", GetType(System.String))

        ICASSOut.Columns.Add("Allot_Current", GetType(System.String))

        ICASSOut.Columns.Add("OrgCode_Prior", GetType(System.String))

        ICASSOut.Columns.Add("OrgCode_Current", GetType(System.String))

        ICASSOut.Columns.Add("Status_Prior", GetType(System.String))

        ICASSOut.Columns.Add("Status_Current", GetType(System.String))

​

        ' Select ALL records from DataTable and Sort records by *Key*

        Dim currTblResults1() as DataRow = custTbl.Select("","key ASC")

​

        ' Loop thru every row in custTbl

        For Each currRow As DataRow In currTblResults1 

            Console.WriteLine("c1 "+currRow.ItemArray(0).ToString+"  "+currRow.ItemArray(1).ToString)

​

            ' Select only rows from cust2Tbl where cust2Tbl.key=custTbl.key

            Dim prevTblResults2() as DataRow = cust2Tbl.Select("key='" + currRow.ItemArray(0).ToString()+"'")

            ' Loop thru every row found in cust2Tbl 

                For Each prevRow As DataRow In prevTblResults2

                        Console.WriteLine("---c2 "+prevRow.ItemArray(0).ToString+"  "+prevRow.ItemArray(1).ToString)

            Next

            Console.WriteLine("")

        Next

    End Sub

End Module