VS 2012 [RESOLVED] VB.net Compare two datatables and return differences-VBForums
Results 1 to 4 of 4

Thread: [RESOLVED] VB.net Compare two datatables and return differences

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    105

    Resolved [RESOLVED] VB.net Compare two datatables and return differences

    The following will compare two datatables and return the differences. These datatables only have two columns each. Like many of you I had attempted to use the 'merge' method and 'getchanges', but that does not work for the purpose of simply comparing two datatables. I dumped the differences into a DataGridView:

    Code:
    'Datatables are defined in the public space and filled with separate code:
        Dim sdt As New DataTable
        Dim pnpdt As New DataTable
        Dim xdt As New DataTable
    '''''''
    
            Dim xrow As DataRow
            For Each row As DataRow In sdt.Rows
                Dim sdtval As String = row.Item("RefDes")
                Dim Mfr As String = row.Item("Mfr")
                If pnpdt.Rows.Contains(sdtval) = False Then
                    xrow = xdt.NewRow
                    xrow("RefDes") = sdtval
                    xrow("Mfr") = Mfr
                    xrow("Notes") = sdtval & "  - Found in sdt, but not found in pnpdt."
                    xdt.Rows.Add(xrow)
                End If
            Next
    
            Dim yrow As DataRow
            For Each rowy As DataRow In pnpdt.Rows
                Dim pnpval As String = rowy.Item("RefDes")
                Dim Mfr As String = rowy.Item("Mfr")
                If sdt.Rows.Contains(pnpval) = False Then
                    yrow = xdt.NewRow
                    yrow("RefDes") = pnpval
                    yrow("Mfr") = Mfr
                    yrow("Notes") = pnpval & "  - Found in pnpdt, but not found in sdt."
                    xdt.Rows.Add(yrow)
                End If
            Next
    
            Dim zrow As DataRow
            For Each rowz As DataRow In sdt.Rows
                Dim pnpval As String = rowz.Item("RefDes") 'SMT Ref Des
                If pnpdt.Rows.Contains(pnpval) = True Then
    
                    Dim BMfr As String = rowz.Item("Mfr")
                    Dim Prow As DataRow = (From column In pnpdt.Rows Where column("RefDes") = pnpval).First
                    Dim PMfr As String = Prow.Item("Mfr")
    
                    If BMfr <> PMfr Then
                        zrow = xdt.NewRow
                        zrow("RefDes") = pnpval
                        zrow("Mfr") = "MISMATCH"
                        zrow("Notes") = pnpval & " - (" & BMfr & ") does not match (" & PMfr & ")."
                        xdt.Rows.Add(zrow)
                    End If
    
                End If
            Next
    
                DataGridView1.DataSource = xdt
                Dim Cnotes As DataGridViewColumn = Me.DataGridView1.Columns("Notes")
                Cnotes.Width = 500
    Could probably be done cleaner, but I hope this helps someone else.

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,322

    Re: VB.net Compare two datatables and return differences

    Hello,

    Here something you could try.
    Code:
    Module DataTableCompare
    
        Public Sub Demodt()
    
            Dim dt1 As New DataTable With {.TableName = "MyTable"}
    
            dt1.Columns.Add(New DataColumn With {.ColumnName = "Identifier",
                                                 .DataType = GetType(Int32),
                                                 .AutoIncrement = True,
                                                 .AutoIncrementSeed = 1})
            dt1.Columns.Add(New DataColumn With {.ColumnName = "FirstName",
                                                 .DataType = GetType(String)})
            dt1.Columns.Add(New DataColumn With {.ColumnName = "LastName",
                                                 .DataType = GetType(String)})
    
            dt1.Rows.Add(New Object() {Nothing, "Jane", "Smith"})
            dt1.Rows.Add(New Object() {Nothing, "Anne", "Jones"})
            dt1.Rows.Add(New Object() {Nothing, "Henry", "Jones"})
            dt1.Rows.Add(New Object() {Nothing, "Bill", "Smith"})
            dt1.Rows.Add(New Object() {Nothing, "Bill", "Smith"})
    
            Dim dt2 As New DataTable With {.TableName = "MyTable"}
    
            dt2.Columns.Add(New DataColumn With {.ColumnName = "Identifier",
                                                 .DataType = GetType(Int32),
                                                 .AutoIncrement = True,
                                                 .AutoIncrementSeed = 1})
            dt2.Columns.Add(New DataColumn With {.ColumnName = "FirstName",
                                                 .DataType = GetType(String)})
            dt2.Columns.Add(New DataColumn With {.ColumnName = "LastName",
                                                 .DataType = GetType(String)})
    
            dt2.Rows.Add(New Object() {Nothing, "Jan", "Smith"})
            dt2.Rows.Add(New Object() {Nothing, "Anne", "Jones"})
            dt2.Rows.Add(New Object() {Nothing, "Henry", "Adams"})
            dt2.Rows.Add(New Object() {Nothing, "Bill", "Smith"})
            dt2.Rows.Add(New Object() {Nothing, "Jill", "Smith"})
    
            CompareDataTables(dt1, dt2)
        End Sub
        Private Sub CompareDataTables(ByVal dt1 As DataTable, ByVal dt2 As DataTable)
            Dim Results =
                (
                    From table1 In dt1
                    Join table2 In dt2 On table1.Field(Of Integer)("Identifier") Equals table2.Field(Of Integer)("Identifier")
                    Where table1.Field(Of String)("FirstName") <> table2.Field(Of String)("FirstName") OrElse
                          table1.Field(Of String)("LastName") <> table2.Field(Of String)("LastName")
                    Select table1)
    
            For Each row As DataRow In Results
                Console.WriteLine(String.Join(",", row.ItemArray))
            Next
        End Sub
    End Module
    Result from console
    Code:
    1,Jane,Smith
    3,Henry,Jones
    5,Bill,Smith

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    105

    Re: VB.net Compare two datatables and return differences

    That is a pretty slick databasey kind of method, thank you!

  4. #4
    Junior Member
    Join Date
    Jun 2016
    Location
    PORTLAND, OREGON
    Posts
    22

    Re: VB.net Compare two datatables and return differences

    Quote Originally Posted by kareninstructor View Post
    Hello,

    Here something you could try.
    Code:
    Module DataTableCompare
    
        Public Sub Demodt()
    
            Dim dt1 As New DataTable With {.TableName = "MyTable"}
    
            dt1.Columns.Add(New DataColumn With {.ColumnName = "Identifier",
                                                 .DataType = GetType(Int32),
                                                 .AutoIncrement = True,
                                                 .AutoIncrementSeed = 1})
            dt1.Columns.Add(New DataColumn With {.ColumnName = "FirstName",
                                                 .DataType = GetType(String)})
            dt1.Columns.Add(New DataColumn With {.ColumnName = "LastName",
                                                 .DataType = GetType(String)})
    
            dt1.Rows.Add(New Object() {Nothing, "Jane", "Smith"})
            dt1.Rows.Add(New Object() {Nothing, "Anne", "Jones"})
            dt1.Rows.Add(New Object() {Nothing, "Henry", "Jones"})
            dt1.Rows.Add(New Object() {Nothing, "Bill", "Smith"})
            dt1.Rows.Add(New Object() {Nothing, "Bill", "Smith"})
    
            Dim dt2 As New DataTable With {.TableName = "MyTable"}
    
            dt2.Columns.Add(New DataColumn With {.ColumnName = "Identifier",
                                                 .DataType = GetType(Int32),
                                                 .AutoIncrement = True,
                                                 .AutoIncrementSeed = 1})
            dt2.Columns.Add(New DataColumn With {.ColumnName = "FirstName",
                                                 .DataType = GetType(String)})
            dt2.Columns.Add(New DataColumn With {.ColumnName = "LastName",
                                                 .DataType = GetType(String)})
    
            dt2.Rows.Add(New Object() {Nothing, "Jan", "Smith"})
            dt2.Rows.Add(New Object() {Nothing, "Anne", "Jones"})
            dt2.Rows.Add(New Object() {Nothing, "Henry", "Adams"})
            dt2.Rows.Add(New Object() {Nothing, "Bill", "Smith"})
            dt2.Rows.Add(New Object() {Nothing, "Jill", "Smith"})
    
            CompareDataTables(dt1, dt2)
        End Sub
        Private Sub CompareDataTables(ByVal dt1 As DataTable, ByVal dt2 As DataTable)
            Dim Results =
                (
                    From table1 In dt1
                    Join table2 In dt2 On table1.Field(Of Integer)("Identifier") Equals table2.Field(Of Integer)("Identifier")
                    Where table1.Field(Of String)("FirstName") <> table2.Field(Of String)("FirstName") OrElse
                          table1.Field(Of String)("LastName") <> table2.Field(Of String)("LastName")
                    Select table1)
    
            For Each row As DataRow In Results
                Console.WriteLine(String.Join(",", row.ItemArray))
            Next
        End Sub
    End Module
    Result from console
    Code:
    1,Jane,Smith
    3,Henry,Jones
    5,Bill,Smith
    what if you just wanted those fields that changed and not the entire changed row returned?

    Using this as an example here is what it gives me currently ...


    Name:  ex11.jpg
Views: 4178
Size:  31.7 KB

    and what I would like for it to return is this

    Name:  ex12.jpg
Views: 4105
Size:  31.3 KB


    Here is the code that is giving me the first image, how would I get it to just return differences as in image 2?

    Code:
    Private Sub CompareDataTables(ByVal dt1 As DataTable, ByVal dt2 As DataTable)
            Dim Results =
                (
                    From table1 In dt2
                    Join table2 In dt1 On table1.Field(Of Integer)("Identifier") Equals table2.Field(Of Integer)("Identifier")
                    Where table1.Field(Of String)("FirstName") <> table2.Field(Of String)("FirstName") OrElse
                          table1.Field(Of String)("LastName") <> table2.Field(Of String)("LastName")
                    Select table1)
    
            Dim boundtable As DataTable = Results.CopyToDataTable
            DataGridView1.DataSource = boundtable
            For Each row As DataRow In Results
                Console.WriteLine(String.Join(",", row.ItemArray))
            Next
    
    
        End Sub
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            
            DataGridView3.DataSource = GetTable1()
            DataGridView2.DataSource = GetTable2()
    
            'CompareTwoDataTable(GetTable1, GetTable2, New ArrayList From {"FirstName", "LastName"})
            CompareDataTables(GetTable1, GetTable2)
    
        End Sub
        Private Function GetTable1() As DataTable
            Dim dt1 As New DataTable With {.TableName = "MyTable1"}
    
            dt1.Columns.Add(New DataColumn With {.ColumnName = "Identifier",
                                                 .DataType = GetType(Int32),
                                                 .AutoIncrement = True,
                                                 .AutoIncrementSeed = 1})
            dt1.Columns.Add(New DataColumn With {.ColumnName = "FirstName",
                                                 .DataType = GetType(String)})
            dt1.Columns.Add(New DataColumn With {.ColumnName = "LastName",
                                                 .DataType = GetType(String)})
    
            dt1.Rows.Add(New Object() {Nothing, "Jane", "Smith"})
            dt1.Rows.Add(New Object() {Nothing, "Anne", "Jones"})
            dt1.Rows.Add(New Object() {Nothing, "Henry", "Jones"})
            dt1.Rows.Add(New Object() {Nothing, "Bill", "Smith"})
            dt1.Rows.Add(New Object() {Nothing, "Bill", "Smith"})
    
    
            Return dt1
        End Function
    
        Private Function GetTable2() As DataTable
    
            Dim dt2 As New DataTable With {.TableName = "MyTable2"}
    
            dt2.Columns.Add(New DataColumn With {.ColumnName = "Identifier",
                                                 .DataType = GetType(Int32),
                                                 .AutoIncrement = True,
                                                 .AutoIncrementSeed = 1})
            dt2.Columns.Add(New DataColumn With {.ColumnName = "FirstName",
                                                 .DataType = GetType(String)})
            dt2.Columns.Add(New DataColumn With {.ColumnName = "LastName",
                                                 .DataType = GetType(String)})
    
            dt2.Rows.Add(New Object() {Nothing, "Jan", "Smith"})
            dt2.Rows.Add(New Object() {Nothing, "Anne", "Jones"})
            dt2.Rows.Add(New Object() {Nothing, "Henry", "Adams"})
            dt2.Rows.Add(New Object() {Nothing, "Bill", "Smith"})
            dt2.Rows.Add(New Object() {Nothing, "Jill", "Smith"})
            Return dt2
        End Function
    Last edited by pjkeady; Nov 2nd, 2016 at 01:33 PM. Reason: adding images for better description

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.