Merging two DataSets eliminating the duplicate rows

Introduction

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.

Background

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 DataSet/ 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 = ""
    Try
        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
            If Equals(ds.Tables(0).Rows(i).ItemArray().Count,
                ds.Tables(1).Rows(j).ItemArray().Count) Then
                For k As Integer = 0 To ds.Tables(0).Rows(i).ItemArray().Count - 1
                If Equals(ds.Tables(0).Rows(i).ItemArray(k),
                    ds.Tables(1).Rows(j).ItemArray(k)) Then
                    checkCount = checkCount + 1
                End If
                If checkCount = ds.Tables(1).Rows(j).ItemArray().Count Then
                    rowPosition = rowPosition + j.ToString() + ","
                    checkCount = 0
                End If
                Next
                    checkCount = 0
                Else
                'Throw an exception or delete the row here itself
                End If
            Next
        Next
        Dim sa As Array = Split(rowPosition.TrimEnd(","), ",")
        For rp As Integer = 0 To sa.Length - 1
            ds.Tables(1).Rows(sa(rp)).Delete()
        Next
        ds.Tables(1).AcceptChanges()
        ds.Tables(0).Merge(ds.Tables(1))
        Return ds ' this has the two datatables merged as one dataset / datatable
                  ' without duplicate rows.
    Catch ex As Exception
        Return Nothing
    End Try
End Function

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)