合并两个相同的DataTable结果DataRowState.Modified [英] Merge two identical DataTables results in DataRowState.Modified

问题描述

我错了，假设如果两个相同的 DataTables 合并，每行的状态将被保留？

看看这个简单的例子。它创建两个相同的表，并将更新的表与原始表合并。但是， original.GetChanges（）中的返回表不如预期的那样 Nothing 此外，原始表中的每一行的状态都更改为修改。

那么我失踪了什么？我真的要创建自己的合并方法来实现吗？

 公共子测试（）
 
 Dim original As DataTable = Me.CreateTableWithData（）
 Dim更新为DataTable = Me.CreateTableWithData（）
 Dim preserveChanges As Boolean = True 
 Dim msAction As MissingSchemaAction = MissingSchemaAction.Ignore 
 
 original.Merge（更新，preserveChanges，msAction）
 
 Dim更改为DataTable = original.GetChanges（）
 
 MessageBox.Show（String.Format（ Count = {0}，If（（Changes Is Nothing），0，changes.Rows.Count）），Me.Text，MessageBoxButtons.OK，MessageBoxIcon.Information）
 
如果（不是更改是没有）Then changes.Dispose（）：changes = Nothing 
 updated.Dispose（）：updated = Nothing 
 original.Dispose（）：original = Nothing 
 
 End Sub 
 
私有函数CreateTableWithData（）As DataTable 
 Dim table As New DataTable（TEST）
 table.Columns.Add（ID，Get Type（Integer））
 table.Columns.Add（VALUE，GetType（String））
 table.PrimaryKey = New DataColumn（）{ta​​ble.Columns（0）} 
表。 Rows.Add（1，Value 1）
 table.Rows.Add（2，Value 2）
 table.AcceptChanges（）
返回表
结束函数
  

输出： Count = 2 / strong>

编辑 - 解决方法

以下代码是一种解决方法对于这个奇怪的（？）行为。

 私有共享子合并（目标作为DataTable，Source As DataTable，preserveChanges As Boolean，msa作为MissingSchemaAction）
 
 target.Merge（source，preserveChanges，msa）
 
 Dim row As DataRow 
 Dim column As DataColumn 
 Dim acceptChanges As Boolean 
 
对于每一行在target.Rows 
如果（（row.RowState = DataRowState.Modified）AndAlso（（row.HasVersion（DataRowVersion.Original））AndAlso（row.HasVersion（DataRowVersio n.Default））））然后
 acceptChanges = True 
对于每列，在target.Columns 
 If（Not Object.Equals（row.Item（column，DataRowVersion.Original））row。 Item（列，DataRowVersion.Default）））然后
 acceptChanges = False 
退出对于
结束如果
下一个
如果（acceptChanges）然后
行。 AcceptChanges（）
 End If 
 End If 
 Next 
 
 acceptChanges = Nothing 
 column = Nothing 
 row = Nothing 
 
 End Sub 
  

解决方案

经过一段时间的工作DataTable合并我找到了合并数据的最佳解决方案，保留更改，并且不将RowState设置为所有现有行的修改。

我发现所有的原始DataTable中的行将其RowState设置为Modified if您使用DataTable合并，并将True作为preserve更改属性。如果你传递false，RowStates保持不变。

回到 DataTable.Merge（DataTable，Boolean，MissingSchemaAction）方法我发现这个：

...在这种情况下，首先调用GetChanges方法。该方法返回第二个针对验证和合并优化的DataTable。第二个DataTable对象只包含已更改的DataTable和DataRow对象对象，导致原始DataTable的一个子集。

从那里我开始意识到，这个合并并不是真正意图直接与原始数据一起使用...而是应该与GetChanges方法返回的表合并（在保存更改中传递true），然后合并更改表将原始源传递给保存修改参数的false。

为了演示，我创建了以下类：

 类TableManger 
实现ComponentModel.INotifyPropertyChanged 
 
 Private _table1 As System.Data.DataTable 
 Private _table2 As System.Data.DataTable 
 Private _changesDetected As Integer = 0 
 
公共ReadOnly属性Table1 
获取
返回_table1 
结束Get 
结束属性
公共ReadOnly属性更改被检测为整数
获取
返回_changesDetected 
结束获取
结束属性
 
 Public Sub New（）
 _table1 = CreateTableWithData（）
 _table1.AcceptChanges（）
 
 AddHandler _table1.RowChanged，New System.Data.DataRowChangeEventHandler（AddressOf Row_Changed）
 End Sub 
 
公共子合并表（）
 
 _table2 = _table1。 Clone 
 Dim tableRows作为新列表（System.Data.DataRow）
对于每个r在_table1.Rows 
 Dim dr2 = _t able2.NewRow 
对于每个col作为System.Data.DataColumn在_table1.Columns 
 dr2（col.ColumnName）= r（col.ColumnName）
下一个
 _table2.Rows。添加（dr2）
 tableRows.Add（dr2）
下一个
 _table2.AcceptChanges（）
 
 
如果_table2.Rows.Count> 0然后
 _table2.Rows（0）（1）=TB2更改
结束如果
 
如果_table1.Rows.Count> 0然后
'_table1.Rows（0）（1）=TB1更改'
 _table1.Rows（1）（1）=TB1更改
如果
 
 _changesDetected = 0 
 Dim perserveChanges As Boolean = True 
 Dim msAction As System.Data.MissingSchemaAction = System.Data.MissingSchemaAction.Ignore 
 
 Dim更改为System。 Data.DataTable = _table1.GetChanges（）
如果更改IsNot Nothing然后
 changes.Merge（_table2，perserveChanges，msAction）
 _table1.Merge（更改，False，msAction）
 Else 
 _table1.Merge（_table2，False，msAction）
 End If 
 
 
 MessageBox.Show（String.Format（Change in Change Table：{0} {1}检测到的变化：{2}，If（（更改为Nothing），0，changes.Rows.Count），System.Environment.NewLine，_changesDetected），Testing）
 
 RaiseEvent PropertyChanged（Me，New ComponentModel.PropertyChangedEventArgs（Table1））
 RaiseEvent PropertyChanged（Me，New ComponentModel.Property ChangedEventArgs（ChangesDetected））
 End Sub 
 
 Private Sub Row_Changed（ByVal sender As Object，ByVal e As System.Data.DataRowChangeEventArgs）
选择案例e.Action 
 Case System.Data.DataRowAction.Change 
如果e.Row.RowState<> System.Data.DataRowState.Unchanged然后
 _changesDetected + = 1 
如果
结束选择
 End Sub 
 
私有函数CreateTableWithData（）作为系统。 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）} 
对于i = 0到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）
下一个
返回newTable 
结束函数
 
公共事件PropertyChanged发送er As Object，e As System.ComponentModel.PropertyChangedEventArgs）实现System.ComponentModel.INotifyPropertyChanged.PropertyChanged 
结束类
  

所以，在MergeTables方法中，我更改了_table2中的第一行，并在_table1的第二行进行更改。

由于我对_table1中的第一行进行了更改，所以_table1.GetChanges方法返回一个具有所有更改的行的DataTable（在本例中为第一行）。

然后，我将包含更改的表与_table2合并，表示我想保留更改。

一次那个合并已经完成了，我知道结果是保留了在合并之前所做的更改，并且该表也将包含新的数据（只要没有冲突）。将输入数据合并到更改表中的结果将解决数据中的任何冲突。

在我找到解决表后，我可以安全地合并到原来的_table1表中，表示preserve change = false。因为传递false作为保留更改参数导致原始数据没有RowState更改，所有工作完全正常！我的更改被保留并且RowStates不被修改！

快乐编码！

-Frinny

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

解决方案

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

这篇关于合并两个相同的DataTable结果DataRowState.Modified的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！