locked
Identifying identical Datatables / identifying change RRS feed

  • Question

  • I have a function which fills a datatable with mysql content then posts it (as appropriate) to a listview. This function is currently on a timer which activates every 5 seconds which unfortunately does horrible destruction to the drag-and-drop features of my software.

    The solution: I have decided after filling the content to the listview to copy the content from the active datatable to another datatable for comparison, every time the data is taken from the mysql db have it save to a datatable and compare the two datatables - if they are not identical the software should run the function however if they are identical there is no reason to update the listview with the same exact data.

        Public pendrun As New DataTable
        Public postrun As New DataTable
    
       Private Sub Timer1_Tick(sender As Object, e As EventArgs) Handles Timer1.Tick
            con.ConnectionString = "server=localhost;" _
    & "user id=user;" _
    & "password=password;" _
    & "database=DMT"
        adptr = New MySqlDataAdapter("Select * from data", con)
        Try
            adptr.Fill(pendrun)
        Catch err As Exception
            Dim strError As String = "Exception: & err.ToString()"
        End Try
    if pendrun <> postrun then
        ListView2.Items.Clear()
        pendrun.Clear()
    
    ' commands to add
    ' pendrun datatable information
    ' to listview
    
    postrun = pendrun 'to transfer data to the holding datatable
    
    end if

    the problem is

    if pendrun <> postrun then

    is not a valid way to compare datatables. How do I identify if the datatables are identical (all rows and columns identical)

    Monday, December 30, 2013 4:06 PM

Answers

  • Hello,

    the simple method would be to compare rows. In the example below we loop thru rows in one DataTable, locate the primary key (of course you could use some other method to identify rows but this is keeping things simple) then use IEqualityComparer to compare two DataRow objects. Please note we only loop thru the first three rows, you could of course figure out which table has the higher number of rows and use that logic or simply 'know' that one DataTable is the one to use for searching into the other.

    Console.WriteLine is used to show results, you would of course use a counter or Boolean variable to know if something has changed.

    Private Sub DemoCompare()
        Dim dt1 As New DataTable With {.TableName = "Table1"}
        dt1.Columns.Add(New DataColumn With {.ColumnName = "Identifier", .DataType = GetType(Int32),
                                             .AutoIncrement = True, .AutoIncrementSeed = 1})
        dt1.Columns.Add(New DataColumn With {.ColumnName = "LastName", .DataType = GetType(String)})
        dt1.Columns.Add(New DataColumn With {.ColumnName = "Amount", .DataType = GetType(Decimal)})
    
        dt1.Rows.Add(New Object() {Nothing, "Smith", 10.99D})
        dt1.Rows.Add(New Object() {Nothing, "Jones", 100.79D})
        dt1.Rows.Add(New Object() {Nothing, "Jenkins", 6.99D})
    
        Dim dt2 As New DataTable With {.TableName = "Table2"}
        dt2.Columns.Add(New DataColumn With {.ColumnName = "Identifier", .DataType = GetType(Int32),
                                             .AutoIncrement = True, .AutoIncrementSeed = 1})
        dt2.Columns.Add(New DataColumn With {.ColumnName = "LastName", .DataType = GetType(String)})
        dt2.Columns.Add(New DataColumn With {.ColumnName = "Amount", .DataType = GetType(Decimal)})
    
        dt2.Rows.Add(New Object() {Nothing, "Smith", 10.99D})
        dt2.Rows.Add(New Object() {Nothing, "Jones", 100.79D})
        dt2.Rows.Add(New Object() {Nothing, "Jenkins", 6.99D})
        dt2.Rows.Add(New Object() {Nothing, "Adams", 6.99D})
    
        Dim ResultingRows() As DataRow
        '
        ' At this point the comapre done the first three rows in dt1 are matches to the first three rows
        ' in dt1. No checks done to see that one table has more rows than the other on purpose as this
        ' simply is another check.
        '
        Console.WriteLine("First time")
        For x As Integer = 0 To dt1.Rows.Count - 1
            ResultingRows = dt2.Select("Identifier =" & dt1.Rows(x).Field(Of Integer)("Identifier").ToString)
            If ResultingRows.Count > 0 Then
                Dim comparer As IEqualityComparer(Of DataRow) = DataRowComparer.Default
                Dim bEqual = comparer.Equals(dt1.Rows(x), ResultingRows(0))
    
                If (bEqual = True) Then
                    Console.WriteLine("Two rows are equal")
                Else
                    Console.WriteLine("Two rows are not equal")
                End If
            End If
        Next
    
        Console.WriteLine("Second time")
    
        '
        ' Change a value in row 1, this will cause comparer to return false against the row in dt2 with
        ' the same identifier.
        '
        dt2.Rows(0).SetField(Of String)("LastName", "Hatfield")
    
        For x As Integer = 0 To dt1.Rows.Count - 1
            ResultingRows = dt2.Select("Identifier =" & dt1.Rows(x).Field(Of Integer)("Identifier").ToString)
            If ResultingRows.Count > 0 Then
                Dim comparer As IEqualityComparer(Of DataRow) = DataRowComparer.Default
                Dim bEqual = comparer.Equals(dt1.Rows(x), ResultingRows(0))
    
                If (bEqual = True) Then
                    Console.WriteLine("Two rows are equal")
                Else
                    Console.WriteLine("Two rows are not equal")
                End If
            End If
        Next
    
    End Sub


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem.

    Monday, December 30, 2013 5:25 PM