How to get the differences between two datatables?
Looking at this: How to compare 2 dataTables
I tried this:
DataTable dataTable1; // Load with data
DataTable dataTable2; // Load with data (same schema)
var differences =
dataTable1.AsEnumerable().Except(dataTable2.AsEnumerable(),
DataRowComparer.Default);
return differences.Any() ? differences.CopyToDataTable() : new DataTable();
but got this:
"Public member 'Any' on type '<ExceptIterator>d__73(Of DataRow)' not found."
Looking at this: How to get difference between two DataTables
I tried this:
DataSet firstDsData = new DataSet();
DataSet secondDsData = new DataSet();
DataSet finalDsData = new DataSet();
DataSet DifferenceDataSet = new DataSet();
finalDsData.Merge(firstDsData);
finalDsData.AcceptChanges();
finalDsData.Merge(secondDsData);
DifferenceDataSet = finalDsData.GetChanges();
But get this:
?DifferenceDataSet
Nothing
I know there is a difference because I put it there.
I do not wish to link tables because the app allows any server and any database to be compared. This is so we can easily view the data from production, test and development as the need arises.
Also note that the data table is built using "SELECT *" but will eventually be able to be changed to choose certain fields.
Yeah, I could loop through the rows and columns, but this would require a sort. And since any table could be used I would have to pull the PK data from SQL server, append it to the sql. While not impossible, I hope there is a better way.
I do not wish to use LINQ, but do not stop from posting LINQ solutions since they may help others.
My code:
Public Function GetTable(ByVal strTable As String, ByVal strServer As String, ByVal strDatabase As String, ByVal strUser As String, ByVal strPassword As String) As DataTable
Dim DT As DataTable
Using Conn As New SqlConnection
Conn.ConnectionString = [String].Format(mconSQLConnectString, strServer, strDatabase, strUser, strPassword, strDatabase)
Conn.Open()
DT = New DataTable
Using cmd As SqlCommand = New SqlCommand("SELECT * FROM " & strTable, Conn)
DT.Load(cmd.ExecuteReader(CommandBehavior.KeyInfo))
DT.Load(cmd.ExecuteReader())
End Using
End Using
Return DT
End Function
Private Sub Diff()
Dim DT As DataTable
Dim DTS As DataTable
Dim DTT As DataTable
Dim strTableName As String
Dim TableNode As TreeNode
mdictSource = New Dictionary(Of String, DataTable)
mdictTarget = New Dictionary(Of String, DataTable)
For Each TableNode In treSource.Nodes
strTableName = TableNode.Text
If TableNode.Checked Then
DTS = DA.GetTable(strTableName, cboServerSource.Text, cboDatabaseSource.Text, txtUserSource.Text, txtPasswordSource.Text)
mdictSource.Add(strTableName, DT)
DTT = DA.GetTable(strTableName, cboServerTarget.Text, cboDatabaseTarget.Text, txtUserTarget.Text, txtPasswordTarget.Text)
mdictTarget.Add(strTableName, DT)
'''''''''''''''''''''''''''''''''''''
'Method 1
Dim differences
differences = DTS.AsEnumerable().Except(DTT.AsEnumerable(), DataRowComparer.Default)
If differences.Any() Then 'Public member 'Any' on type '<ExceptIterator>d__73(Of DataRow)' not found.
DT = differences.CopyToDataTable
End If
'''''''''''''''''''''''''''''''''''''
'Method 2
Dim finalDsData = New DataSet
Dim DifferenceDataSet As DataSet
finalDsData.Merge(DTS)
finalDsData.AcceptChanges()
finalDsData.Merge(DTT)
DifferenceDataSet = finalDsData.GetChanges()
End If
Next
End Sub
I know there is a difference because I created it:
?DTt.rows(11).Item(2)
"RFQs Issued" {String}
String: "RFQs Issued"
?DTs.rows(11).Item(2)
"RFQs Issued!" {String}
String: "RFQs Issued!"
I don't really care how I get the results as long as I get the table, pkey and field(s)
EDIT: Solution to finding mis-matching primary keys. Still need to check datatypes but when this logic is complete it should be easy to include non-primary key columns:
Private Sub Diff()
Dim DGV As DataGridView
Dim DT As DataTable = Nothing
Dim DTS As DataTable
Dim DTT As DataTable
Dim strMessage As String
Dim TP As TabPage
Dim strTableName As String
Dim TableNode As TreeNode
mdictSource = New Dictionary(Of String, DataTable)
mdictTarget = New Dictionary(Of String, DataTable)
For Each TableNode In treSource.Nodes
If TableNode.Checked Then
strTableName = TableNode.Text
DGV = New DataGridView
DGV.AllowUserToAddRows = False
DGV.Dock = DockStyle.Fill
DGV.Name = "dgv" & strTableName
DGV.Visible = True
TP = New TabPage
TP.Name = "tp" & strTableName
TP.Text = strTableName
TP.Controls.Add(DGV)
tcTableResults.TabPages.Add(TP)
DTS = DA.GetTable(strTableName, cboServerSource.Text, cboDatabaseSource.Text, txtUserSource.Text, txtPasswordSource.Text)
mdictSource.Add(strTableName, DT)
DTT = DA.GetTable(strTableName, cboServerTarget.Text, cboDatabaseTarget.Text, txtUserTarget.Text, txtPasswordTarget.Text)
mdictTarget.Add(strTableName, DT)
If PrimarykeyCountMatches(DTS, DTT) Then
If PrimarykeysMatch(DGV, DTS, DTT) Then
If ColumnCountMatches(DTS, DTT) Then
If ColumnsMatch(DTS, DTT) Then
strMessage &= strTableName & " matches." & Environment.NewLine
Else
End If
Else
DisplayDiffPrimarykeys(DGV, DTS, DTT)
End If
End If
Else
DisplayDiffPrimarykeys(DGV, DTS, DTT)
End If
End If
Next
If strMessage <> String.Empty Then
MessageBox.Show(strMessage, "Results")
End If
End Sub
Private Sub DisplayDiffColumns(DGV As DataGridView, DTS As DataTable, DTT As DataTable)
End Sub
Private Sub DisplayDiffPrimarykeys(DGV As DataGridView, DTS As DataTable, DTT As DataTable)
Dim DC As DataGridViewColumn
Dim DR As DataGridViewRow
Dim intNewRow As Integer
Dim intOffset As Integer
Dim r As Integer
For r = 0 To DTS.PrimaryKey.Length - 1
DC = New DataGridViewTextBoxColumn
DC.HeaderText = "Source: PKey" & (r + 1).ToString
DC.ReadOnly = True
DGV.Columns.Add(DC)
Next
For r = 0 To DTT.PrimaryKey.Length - 1
DC = New DataGridViewTextBoxColumn
DC.HeaderText = "Target: PKey" & (r + 1).ToString
DC.ReadOnly = True
DGV.Columns.Add(DC)
Next
intNewRow = DGV.Rows.Add()
DR = DGV.Rows(intNewRow)
DR.HeaderCell.Value = "PKeys"
For r = 0 To DTS.PrimaryKey.Length - 1
DR.Cells(r).Value = DTS.PrimaryKey(r)
Next
intOffset = r
For r = 0 To DTT.PrimaryKey.Length - 1
DR.Cells(r + intOffset).Value = DTT.PrimaryKey(r)
Next
End Sub
Private Function PrimarykeyCountMatches(DTS As DataTable, DTT As DataTable) As Boolean
Return DTS.PrimaryKey.Length = DTT.PrimaryKey.Length
End Function
Private Function PrimarykeysMatch(DGV As DataGridView, DTS As DataTable, DTT As DataTable) As Boolean
Dim DC As DataGridViewColumn
Dim lisDiffSource As List(Of Integer)
Dim lisDiffTarget As List(Of Integer)
Dim DR As DataGridViewRow
Dim intIndex As Integer
Dim bMatch As Boolean = False
Dim intNewRow As Integer
Dim PKeysSource As String()
Dim PKeysTarget As String()
Dim s As IOrderedEnumerable(Of DataColumn)
Dim strSCaption As String
Dim intSIndex As Integer
Dim t As IOrderedEnumerable(Of DataColumn)
Dim strTCaption As String
Dim intTIndex As Integer
s = DTS.PrimaryKey.OrderBy(Function(c) c.Caption)
t = DTT.PrimaryKey.OrderBy(Function(c) c.Caption)
lisDiffSource = New List(Of Integer)
lisDiffTarget = New List(Of Integer)
Do
strSCaption = s(intSIndex).Caption.ToLower
strTCaption = t(intTIndex).Caption.ToLower
If strSCaption = strTCaption Then
intSIndex += 1
intTIndex += 1
Else
If strSCaption > strTCaption Then
lisDiffTarget.Add(intTIndex)
intTIndex += 1
Else 'strSCaption < strTCaption
lisDiffSource.Add(intSIndex)
intSIndex += 1
End If
End If
Loop While intSIndex < s.Count And intTIndex < t.Count
While intSIndex < s.Count
lisDiffSource.Add(intSIndex)
intSIndex += 1
End While
While intTIndex < t.Count
lisDiffTarget.Add(intTIndex)
intTIndex += 1
End While
If lisDiffSource.Count = 0 And lisDiffTarget.Count = 0 Then
bMatch = True
Else
DC = New DataGridViewTextBoxColumn
DC.HeaderText = "Table"
DC.ReadOnly = True
DGV.Columns.Add(DC)
DC = New DataGridViewTextBoxColumn
DC.HeaderText = "Column"
DC.ReadOnly = True
DGV.Columns.Add(DC)
DC = New DataGridViewTextBoxColumn
DC.HeaderText = "Status"
DC.ReadOnly = True
DGV.Columns.Add(DC)
For Each intIndex In lisDiffSource
intNewRow = DGV.Rows.Add()
DR = DGV.Rows(intNewRow)
DR.Cells(0).Value = "Source"
DR.Cells(1).Value = s(intIndex).Caption
DR.Cells(2).Value = "Missing from Target"
Next
For Each intIndex In lisDiffTarget
intNewRow = DGV.Rows.Add()
DR = DGV.Rows(intNewRow)
DR.Cells(0).Value = "Target"
DR.Cells(1).Value = t(intIndex).Caption
DR.Cells(2).Value = "Missing from Source"
Next
End If
Return bMatch
End Function
Private Function ColumnCountMatches(DTS As DataTable, DTT As DataTable) As Boolean
Return DTS.Columns.Count = DTT.Columns.Count
End Function
Private Function ColumnsMatch(DTS As DataTable, DTT As DataTable) As Boolean
Return True
End Function
