I have two data tables. I want to merge them and want to get only unmatched rows from both data tables. Can anyone please help on this?
Currently I am using the following code, but it's returning nothing:
Actual_Table = myDataSet_Actual.Tables("Actuals")
Planned_Table = myDataset_Planned.Tables("Planned")
Planned_Table.AcceptChanges()
Planned_Table.Merge(Actual_Table, False)
change = Planned_Table.GetChanges(DataRowState.Added)
DGV1.DataSource = change ' DGV1= Data grid view
You can do this with the following code, but please read on for more details:
<!-- language: lang-vb -->
change = Planned_Table.AsEnumerable _
.Except(Actual_Table.AsEnumerable, DataRowComparer.Default) _
.CopyToDataTable
As pointed out in this SO question, DataTable.Merge does not change row state, so looking for rows with DataRowState.Added will always come up empty.
Also, Merge adds every row from the second table to the first, so everything from the second table would have come up even if the RowState had changed.
Finally, supposing we only added unique values from the second datatable to the first, we'd only get back the elements that were in the second set, but not in the first. Perhaps that is what you wanted, but I'll delineate both options in case you really want either one.
As found in other SO questions here and here, we can find the right set of entries by using any of the LINQ Set Operations:
You can explore each of these further in MSDN, but just as an FYI, as mentioned in this SO Question, Union and Concat will both append an enumerable onto another, but Union will remove duplicates (when checking the equality of DataRows, you must pass in the overloaded IEqualityComparer = DataRowComparer.Default)
Also, we'll want to Enumerate over a DataTable, so to do this we'll need to call the AsEnumerable method from the DataTableExtensions namespace.
Just so we're looking at the same code, here are some basic DataTables on which we can perform set operations
<!-- language: lang-vb -->Dim dt, dataTable1, dataTable2 As New DataTable
With dt.Columns
.Add("ID", GetType(Integer))
.Add("Name", GetType(String))
End With
dataTable1 = dt.Clone
dataTable2 = dt.Clone
With dataTable1.Rows
.Add(1, "Tod")
.Add(2, "Jim")
End With
With dataTable2.Rows
.Add(2, "Jim")
.Add(3, "Han")
End With
So if you want just the new items in the second table that don't appear in the first table, then you want a Right Outer Join:
If you want all the data that is different between the two sets, you want a Full Outer Join that excludes an Inner Join
You can read more about different sets and the joins that create them in this Jeff Atwood post.
If you want the first option, you can get the elements in and only in the the second set by calling Except on the second Datatable and passing in the first
Dim just2 As DataTable
just2 = dataTable2.AsEnumerable _
.Except(dataTable1.AsEnumerable, DataRowComparer.Default) _
.CopyToDataTable()
The output will be:
| ID | Name |
| 3 | Han |
If you want the unique values in each set, you'll have to call distinct twice and then join those two values. We won't need to pass in the DataRowComparer to the Union method because we've guaranteed that there are no duplicates in the two tables
Dim just1 As IEnumerable(Of DataRow)
Dim just2 As IEnumerable(Of DataRow)
Dim union As DataTable
'get rows that are only in table1
just1 = dataTable1.AsEnumerable.Except(dataTable2.AsEnumerable, DataRowComparer.Default)
'get rows that are only in table2
just2 = dataTable2.AsEnumerable.Except(dataTable1.AsEnumerable, DataRowComparer.Default)
'get all unique rows
union = just1.Union(just2).CopyToDataTable
Since we won't need the unique element sets individually, there's no need to call CopyToDataTable just so we can cast back into an IEnumerable(Of DataRow) with AsEnumerable
The output of union will be:
| ID | Name |
| 1 | Tod |
| 3 | Han |