Click here to Skip to main content
15,503,369 members
Articles / Programming Languages / Visual Basic
Article
Posted 14 Aug 2008

Tagged as

Stats

52.8K views
14 bookmarked

Merging two DataSets eliminating the duplicate rows

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
14 Aug 2008CPOL1 min read
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#):

VB
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)


Written By
Unknown
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralC# Version [modified] Pin
aebe21-Oct-10 6:29
aebe21-Oct-10 6:29 
Here's a converted C# version, with variables renamed to improve readability (if you speak a bit of english Wink | ;-) )
//http://support.microsoft.com/kb/313540
        //http://www.codeproject.com/KB/grid/Merging_two_DataSets.aspx
        public static DataSet MergeResultSets(DataSet sourceDs, DataSet changes)
        {
            int checkCount = 0;
            string rowPosition = string.Empty;

            for (int sourceTableIndex = 0; sourceTableIndex < sourceDs.Tables.Count; sourceTableIndex++)
            {
                try
                {
                    var sourceDt = sourceDs.Tables[sourceTableIndex];
                    var updatedDt = changes.Tables[sourceTableIndex];
                    for (int sourceRowIndex = 0; sourceRowIndex < sourceDt.Rows.Count; sourceRowIndex++)
                    {
                        for (int changedRowIndex = 0; changedRowIndex < updatedDt.Rows.Count; changedRowIndex++)
                        {
                            DataRow sourceRow = sourceDt.Rows[sourceRowIndex];
                            DataRow updatedRow = updatedDt.Rows[changedRowIndex];
                            if (sourceRow.ItemArray.Length == updatedRow.ItemArray.Length)
                            {
                                for (int cellIndex = 0; cellIndex < sourceRow.ItemArray.Length; sourceRowIndex++)
                                {
                                    if (sourceRow.ItemArray[cellIndex] == updatedRow.ItemArray[cellIndex])
                                        checkCount = checkCount + 1;

                                    if (checkCount == updatedRow.ItemArray.Length)
                                        rowPosition = rowPosition + changedRowIndex + ",";

                                    checkCount = 0;
                                }
                                checkCount = 0;
                            }
                        }
                    }

                    var sa = new List<string>(rowPosition.TrimEnd(',').Split(','));
                    for (int rp = 0; rp < sa.Count - 1; rp++)
                        updatedDt.Rows[Convert.ToInt32(sa[rp])].Delete();

                    // Merging cleaned tables
                    updatedDt.AcceptChanges();
                    sourceDt.Merge(updatedDt);
                }
                catch (Exception e)
                {
                    return null;
                }
            }
            return sourceDs;
        }


modified on Thursday, October 21, 2010 11:50 AM

GeneralA few comments to the code can it more readable Pin
rama charan19-Jun-10 4:34
rama charan19-Jun-10 4:34 

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

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