[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.
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
Re: VB.net Compare two datatables and return differences
That is a pretty slick databasey kind of method, thank you!
2 Attachment(s)
Re: VB.net Compare two datatables and return differences
Quote:
Originally Posted by
kareninstructor
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 ...
Attachment 142083
and what I would like for it to return is this
Attachment 142085
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
