Click here to Skip to main content
Click here to Skip to main content

Merging two DataSets eliminating the duplicate rows

, 14 Aug 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
How to merge two DataSets by 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

License

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

Share

About the Author

gnk424

Unknown
No Biography provided

Comments and Discussions

 
GeneralC# Version [modified] Pinmemberaebe21-Oct-10 6:29 
GeneralA few comments to the code can it more readable Pinmemberrama charan19-Jun-10 4:34 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141220.1 | Last Updated 14 Aug 2008
Article Copyright 2008 by gnk424
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid