how to compare 2 datatable and get unique records in 3rd datatable?

Question

I have 2 datatable let say dtData1 and dtData2. I have records in both the datatable I want to compare both the data table and want to create a new datatable let say dtData3 with uniqe records.

suppose: dtData1 has 10 records, and dtData2 has 50 records, but what ever records are there in dtData2 in that 7 records are same as dtData1. So here I want unique records in dtData3, means I want only 43 records in dtData3.

we don't know in datatable where we have the duplicate, and its possible that we will not have duplicates or its also possible that we will have all duplicate records.

So in dtData3 I need unique records

Some one please help me.

Not an exact duplicate, but close enough - [How to select distinct values from datatable?](http://stackoverflow.com/questions/1199176/how-to-select-distinct-values-from-datatable) — James, Aug 08 '12 at 10:04

YS. · Answer 1 · 2012-08-08T13:12:22.330

5

var dtData3 = dtData2.AsEnumerable().Except(dtData1.AsEnumerable(), DataRowComparer.Default);

edited Aug 08 '12 at 13:12

answered Aug 08 '12 at 10:08

YS.

1,808
1
19
33

are you sure that the default equality-comparer (or the `.GetHashCode`/`.Equals`-implementation of `System.Data.DataRow`) of `System.Collections.IEnumerable` checks the equality against the values of the columns instead of the instances? – Aug 08 '12 at 10:10
From http://msdn.microsoft.com/en-us/library/bb386998.aspx The Equals(DataRow, DataRow) method is then called and the two DataRow objects to be compared are passed in as input parameters. The Equals(DataRow, DataRow) method returns true if the ordered set of column values in both DataRow objects are equal; otherwise, false. – YS. Aug 08 '12 at 10:23
- that is not true ... `.Equals(DataRow, DataRow)` does not look like an instance-method - and yes, it is not - instead it's defined in `IEqualityComparer` ... so, providing no implementation of `System.Collections.Generic.IEqualityComparer` means: goto hell. you have to use following overload: `Enumerable.Except(IEnumerable, IEnumerable, IEqualityComparer)` (http://msdn.microsoft.com/en-us/library/bb336390) like: `sequence1.Except(sequence2, System.Data.DataRowComparer.Default)` - otherwise you are doing an instance/reference-comparison... – Aug 08 '12 at 12:49
please see full stack @ http://pastebin.com/mWmJW5v5 ... it should be clear, why your solution is failing ... – Aug 08 '12 at 13:00
"goto hell" ? is the name calling really necessary ? – YS. Aug 08 '12 at 13:05
:) sry, that was a disastrous word choice ... i did not name you, rather the situation you are in then. thanks for the edit, will convert my downvote to an upvote – Aug 08 '12 at 13:15
Superb solution. My problem got resolved. Thanks a lot. – Aditi Dec 19 '16 at 09:11

score 1 · Answer 2 · answered Aug 08 '12 at 11:32

Use this.. Probably it will help you.

Suppose you have two data table

DataTable dt1 = new DataTable();
            dt1.Columns.Add("Name");
            dt1.Columns.Add("ADD");
            DataRow drow;
            for (int i = 0; i < 10; i++)
            {
                drow = dt1.NewRow();
                drow[0] = "NameA" + 1;
                drow[1] = "Add" + 1;
                dt1.Rows.Add();
            }
            DataTable dt2 = new DataTable();
            dt2.Columns.Add("Name");
            dt2.Columns.Add("ADD");
            DataRow drow1;
            for (int i = 0; i < 11; i++)
            {
                drow1 = dt2.NewRow();
                drow1[0] = "Name" + 1;
                drow1[1] = "Add" + 1;
                dt2.Rows.Add();
            }

Now To solve your problem Call :-

DataTable d3 = CompareTwoDataTable(dt1, dt2);

The method is something like this;--

public static DataTable CompareTwoDataTable(DataTable dt1, DataTable dt2)
        {
            dt1.Merge(dt2);
            DataTable d3 = dt2.GetChanges();

            return d3;
        }

score 0 · Answer 3 · answered Aug 08 '12 at 10:10

0

Then where the need to compare dtData1 and dtData2? Instead you can copy the contents from dtData2 to dtData3 starting from index7.

answered Aug 08 '12 at 10:10

tnchalise

1,573
2
18
38

1

because we don't know in datatable where we have the duplicate, and its possible that we will not have duplicate or its also possible that we will have all duplicate records. – Rocky Aug 08 '12 at 10:14

score 0 · Answer 4 · edited Aug 08 '12 at 13:21

0

First Create Array variables to save unique coloumn values for datatable3. Use Foreach loop with second gridview rows. If any match then dnt save it in a array value, if dnt match then save it in arrays. and display it by attaching with third gridview.......

e.g

string[] name = new string[4];

int i=0,j=0;
foreach(GridViewRows gv in GridView1.rows)
{
    if(gv.Cells[0].Text == GridView2.Rows[i].Cells[0].Text)' //if match
    {
       // dnt save
    } 
    else'  //if dnt match save in array for further use
    {
        name[j] = gv.Cells[0].Text;
        j= j++;
    }
    i=i++;
}

After Saving unique values in Array "name"...Bind it in Third Gridview During DataBound of third Gridview add this method...

Private void GridView3_RowDataBound(object sender,EventArgs e)
{
    if(e.Row.RowState == DataControlState.DataRow)
    {
        foreach(string nm in name)
        {
            e.Rows.Cells.Add(name);      
        }
    }
}

edited Aug 08 '12 at 13:21

answered Aug 08 '12 at 11:34

Maf

1
2

sorry i m new here..trying to learn how to use.. can u tell me how to post code in answers here...thnx – Maf Aug 08 '12 at 13:03
please see my edit - it should be clear now ... btw - welcome to the community! – Aug 08 '12 at 13:21
Sorry but I am not using grid. – Rocky Aug 09 '12 at 04:50
I am selecting records from database – Rocky Aug 09 '12 at 07:03
You can use sql command to intersect second table which will select only unique rows of eachother.... `SELECT id FROM tab1 INTERSECT SELECT id FROM tab2` Read This example...your problem will resolved... http://www.java2s.com/Tutorial/SQLServer/0120__Set-Operations/Theintersectionoftwotablesisthesetofrowsbelongingtobothtables.htm – Maf Aug 09 '12 at 08:14

score 0 · Accepted Answer · answered Mar 07 '13 at 05:22

public DataTable CompareTwoDataTable(DataTable dtOriginalTable, DataTable dtNewTable, ArrayList columnNames)
    {
        DataTable filterTable = new DataTable();
        filterTable = dtNewTable.Copy();
        string filterCriterial;
        if (columnNames.Count > 0)
        {
            for (int iNewTableRowCount = 0; iNewTableRowCount < dtNewTable.Rows.Count; iNewTableRowCount++)
            {
                filterCriterial = string.Empty;
                foreach (string colName in columnNames.ToArray())
                {
                    filterCriterial += colName.ToString() + "='" + dtNewTable.Rows[iNewTableRowCount][colName].ToString() + "' AND ";
                }
                filterCriterial = filterCriterial.TrimEnd((" AND ").ToCharArray());
                DataRow[] dr = dtOriginalTable.Select(filterCriterial);
                if (dr.Length > 0)
                {
                    filterTable.Rows[filterTable.Rows.IndexOf(filterTable.Select(filterCriterial)[0])].Delete();
                }
            }
        }
        return filterTable;
    }

how to compare 2 datatable and get unique records in 3rd datatable?

5 Answers5