The scenario is something like this: we have two
DataTables from two different database servers, and want to merge the data in the two
DataTables using C# / VB.NET. Sounds easy!!! Yes, it is easy - you can always use
DataSet.DataTable(index).Merge(DataTable to Merge). But if you have to delete the duplicates in the two
DataTables, then you do not have any predefined functions in .NET. But you can achieve this in many ways, and this is one of them.
I had a scenario in my project where I had to get the results out of two databases, eliminating the duplicates from the two different database tables. If it were to be from the same server, we could have done it using the "
Union" operation in the database Stored Procedure and got the desired result. But from two different servers, we have to use
DataSet.DataTable.Merge(DataSet.DataTable), and it will not eliminate the duplicates if any in the end result set. Hence, I came up with a function in VB.NET / C# which gives us a
DataTable eliminating the duplicates.
Using the Code
The basic code is like this (it is in VB.NET and it's very simple to convert it to C#):
Public Shared Function MergeResultSets() As DataSet
Dim ds As DataSet, checkCount As Integer = 0, rowPosition As String = ""
ds = DAL.DataAcess.Sample(param1, param2)
For i As Integer = 0 To ds.Tables(0).Rows.Count - 1
For j As Integer = 0 To ds.Tables(1).Rows.Count - 1
For k As Integer = 0 To ds.Tables(0).Rows(i).ItemArray().Count - 1
checkCount = checkCount + 1
If checkCount = ds.Tables(1).Rows(j).ItemArray().Count Then
rowPosition = rowPosition + j.ToString() + ","
checkCount = 0
checkCount = 0
Dim sa As Array = Split(rowPosition.TrimEnd(","), ",")
For rp As Integer = 0 To sa.Length - 1
Catch ex As Exception