Merge two identical DataTables results in DataRowState.Modified

Question

Am I wrong assuming that if two identical DataTables are merged the state of each row will be preserved?

Take a look at this simple example. It creates two identical tables and merge the updated table with original table. But the returned table in original.GetChanges() is not Nothing as expected. Also, the state of each row in the original table are changed to Modified.

So what am I missing? Do I really have to create my own merge method to achieve this?

Public Sub Test()

    Dim original As DataTable = Me.CreateTableWithData()
    Dim updated As DataTable = Me.CreateTableWithData()
    Dim preserveChanges As Boolean = True
    Dim msAction As MissingSchemaAction = MissingSchemaAction.Ignore

    original.Merge(updated, preserveChanges, msAction)

    Dim changes As DataTable = original.GetChanges()

    MessageBox.Show(String.Format("Count={0}", If((changes Is Nothing), 0, changes.Rows.Count)), Me.Text, MessageBoxButtons.OK, MessageBoxIcon.Information)

    If (Not changes Is Nothing) Then changes.Dispose() : changes = Nothing
    updated.Dispose() : updated = Nothing
    original.Dispose() : original = Nothing

End Sub

Private Function CreateTableWithData() As DataTable
    Dim table As New DataTable("TEST")
    table.Columns.Add("ID", GetType(Integer))
    table.Columns.Add("VALUE", GetType(String))
    table.PrimaryKey = New DataColumn() {table.Columns(0)}
    table.Rows.Add(1, "Value 1")
    table.Rows.Add(2, "Value 2")
    table.AcceptChanges()
    Return table
End Function

Output: Count=2

Edit - The workaround

The following code is a workaround for this strange(?) behavior.

Private Shared Sub Merge(target As DataTable, source As DataTable, preserveChanges As Boolean, msa As MissingSchemaAction)

    target.Merge(source, preserveChanges, msa)

    Dim row As DataRow
    Dim column As DataColumn
    Dim acceptChanges As Boolean

    For Each row In target.Rows
        If ((row.RowState = DataRowState.Modified) AndAlso ((row.HasVersion(DataRowVersion.Original)) AndAlso (row.HasVersion(DataRowVersion.Default)))) Then
            acceptChanges = True
            For Each column In target.Columns
                If (Not Object.Equals(row.Item(column, DataRowVersion.Original), row.Item(column, DataRowVersion.Default))) Then
                    acceptChanges = False
                    Exit For
                End If
            Next
            If (acceptChanges) Then
                row.AcceptChanges()
            End If
        End If
    Next

    acceptChanges = Nothing
    column = Nothing
    row = Nothing

End Sub

Answer 1

After some time of working with DataTable merge I found the best solution to merging data, preserving changes and not setting the RowState to Modified for all of the existing rows.

What I discovered is that all of the rows in the original DataTable would have their RowState set to Modified if you use the DataTable Merge and pass True as the preserve changes property. If you pass false instead, the RowStates remain the same.

Going back to the documentation for the DataTable.Merge(DataTable, Boolean, MissingSchemaAction) Method I found this:

...In this scenario, the GetChanges method is first invoked. That method returns a second DataTable optimized for validating and merging. This second DataTable object contains only the DataTable and DataRow objects objects that were changed, resulting in a subset of the original DataTable...

From there I started to realize that the this merge is not really intended to be used with the original data directly... instead you should merge against the table returned by the GetChanges method (passing true in preserving changes) and then merge the changes table into the original source passing false for the preserving changes parameter.

To demonstrate this I have created the following class:

Class TableManger
Implements ComponentModel.INotifyPropertyChanged

Private _table1 As System.Data.DataTable
Private _table2 As System.Data.DataTable
Private _changesDetected As Integer = 0

Public ReadOnly Property Table1
    Get
        Return _table1
    End Get
End Property
Public ReadOnly Property ChangesDetected As Integer
    Get
        Return _changesDetected
    End Get
End Property

Public Sub New()
    _table1 = CreateTableWithData()
    _table1.AcceptChanges()

    AddHandler _table1.RowChanged, New System.Data.DataRowChangeEventHandler(AddressOf Row_Changed)
End Sub

Public Sub MergeTables()

    _table2 = _table1.Clone
    Dim tableRows As New List(Of System.Data.DataRow)
    For Each r In _table1.Rows
        Dim dr2 = _table2.NewRow
        For Each col As System.Data.DataColumn In _table1.Columns
            dr2(col.ColumnName) = r(col.ColumnName)
        Next
        _table2.Rows.Add(dr2)
        tableRows.Add(dr2)
    Next
    _table2.AcceptChanges()


    If _table2.Rows.Count > 0 Then
        _table2.Rows(0)(1) = "TB2 Changed"
    End If

    If _table1.Rows.Count > 0 Then
        '_table1.Rows(0)(1) = "TB1 Change"'
        _table1.Rows(1)(1) = "TB1 Change"
    End If

    _changesDetected = 0
    Dim perserveChanges As Boolean = True
    Dim msAction As System.Data.MissingSchemaAction = System.Data.MissingSchemaAction.Ignore

    Dim changes As System.Data.DataTable = _table1.GetChanges()
    If changes IsNot Nothing Then
        changes.Merge(_table2, perserveChanges, msAction)
        _table1.Merge(changes, False, msAction)
    Else
        _table1.Merge(_table2, False, msAction)
    End If


    MessageBox.Show(String.Format("Changes in Change Table: {0} {1}Changes Detected: {2}", If((changes Is Nothing), 0, changes.Rows.Count), System.Environment.NewLine, _changesDetected), "Testing")

    RaiseEvent PropertyChanged(Me, New ComponentModel.PropertyChangedEventArgs("Table1"))
    RaiseEvent PropertyChanged(Me, New ComponentModel.PropertyChangedEventArgs("ChangesDetected"))
End Sub

Private Sub Row_Changed(ByVal sender As Object, ByVal e As System.Data.DataRowChangeEventArgs)
    Select Case e.Action
        Case System.Data.DataRowAction.Change
            If e.Row.RowState <> System.Data.DataRowState.Unchanged Then
                _changesDetected += 1
            End If
    End Select
End Sub

Private Function CreateTableWithData() As System.Data.DataTable
    Dim newTable As New System.Data.DataTable
    Dim columnID As New System.Data.DataColumn("ID", GetType(Guid))
    Dim columnA As New System.Data.DataColumn("ColumnA", GetType(String))
    Dim columnB As New System.Data.DataColumn("ColumnB", GetType(String))
    newTable.Columns.AddRange({columnID, columnA, columnB})
    newTable.PrimaryKey = {newTable.Columns(0)}
    For i = 0 To 5
        Dim dr = newTable.NewRow
        dr("ID") = Guid.NewGuid
        dr("ColumnA") = String.Format("Column A Row {0}", i.ToString)
        dr("ColumnB") = String.Format("Column B Row {0}", i.ToString)
        newTable.Rows.Add(dr)
    Next
    Return newTable
End Function

Public Event PropertyChanged(sender As Object, e As System.ComponentModel.PropertyChangedEventArgs) Implements System.ComponentModel.INotifyPropertyChanged.PropertyChanged
End Class

So, in the MergeTables method, I make a change to the first row in _table2 and I make a change in the second row of _table1.

Because I made a change the first row in _table1, the _table1.GetChanges method returns a DataTable with all of the changed rows (just the first row in this case).

I then merge the table containing the changes with _table2 and indicate that I want to preserve changes.

Once that merge is completed I know that the results are preserving the changes that I had made before the merge and that the table will contain the new data as well (so long as there were no conflicts). The result of merging the incoming data into the changes table will resolution of any conflicts in data.

After I have that resolved table I can safely merge into the original _table1 table indicating that preserve change = false. Because passing false as the preserve changes parameter results in no RowState changes for the original data everything works perfectly fine! My changes are preserved And the RowStates are not modified!

Happy Coding!

-Frinny