compare datatable and return unmatched rows

Question

I am trying to compare two datatables and capture the difference third datatable.

DataTable one = new DataTable();
            one.Columns.Add("ID");
            one.Columns.Add("PCT");
            one.Rows.Add("1", "0.1");
            one.Rows.Add("2", "0.2");
            one.Rows.Add("3", "0.3");
            gvone.DataSource = one;
            gvone.DataBind();
            DataTable two = new DataTable();
            two.Columns.Add("ID");
            two.Columns.Add("PCT");
            two.Columns.Add("OldPCT");
            two.Rows.Add("1", "0.0", "0");
            two.Rows.Add("2", "0.1", "0");
            two.Rows.Add("3", "0.9", "0");
            two.Columns.Remove("OldPCT");
            gvtwo.DataSource = two;
            gvtwo.DataBind();
            DataTable dt3 = two.AsEnumerable().Except(one.AsEnumerable()).CopyToDataTable();
            var diffName = two.AsEnumerable().Select(r => r.Field<string>("PCT")).Except(one.AsEnumerable().Select(r => r.Field<string>("PCT")));
            if (diffName.Any())
            {
                DataTable Table3 = (from row in two.AsEnumerable()
                                    join name in diffName
                                    on row.Field<string>("PCT") equals name
                                    select row).CopyToDataTable();
            }

Now my result in table 3 should be all rows in datatable two. since there is a mismatch in values. But it return only 1st and last row of datatable two. But I need to get all rows of table 2. How can I order the rows and compare.

You might need to use the version of `.Except()` that accepts an `IEqualityComparer` in order for that to work correctly. — Cory, May 22 '15 at 15:45
@Cory - I got it to work. But if the rows are same it says datasource has no rows. Please tell me how to handle that. — Vinod Kumar, May 22 '15 at 15:50
I'm not sure exactly what you want it to do? If the tables have the same rows then the result of the `Except()` should have no rows...If all you want is the headers to still display on your GridView, this may be helpful http://stackoverflow.com/questions/354369/gridview-show-headers-on-empty-data-source — Cory, May 22 '15 at 15:56

score 0 · Answer 1 · answered May 22 '15 at 15:49

0

Loop through the rows of each data table and then through each column within that loop to compare individual values.

Sample here:

http://canlu.blogspot.com/2009/05/how-to-compare-two-datatables-in-adonet.html

answered May 22 '15 at 15:49

William Xifaras

5,212
2
19
21

Carl Prothman · Answer 2 · 2015-05-23T00:21:23.900

Try this solution.

// Create a DataTable
DataTable one = new DataTable();
one.Columns.Add("ID", typeof(int));
one.Columns.Add("PCT", typeof(double));
one.PrimaryKey = new DataColumn[] { one.Columns["ID"] };
one.Rows.Add(1, 1.0); // Occur in two, same
one.Rows.Add(2, 2.0); // Occurs in two, but different
one.Rows.Add(3, 3.0); // Not in two 
one.Rows.Add(5, 5.0); // Occur in two, same

// Create a second DataTable
DataTable two = new DataTable();
two.Columns.Add("ID", typeof(int));
two.Columns.Add("PCT", typeof(double));
two.PrimaryKey = new DataColumn[] { two.Columns["ID"] };
two.Rows.Add(1, 1.0); // Occur in one, same
two.Rows.Add(2, 2.1); // Occurs in one, but different
two.Rows.Add(4, 4.0); // Not in one
two.Rows.Add(5, 5.0); // Occur in one, same

// Perform the Except 
// one: whose elements that are not in second will be returned.
// two: whose elements that also occur in the first sequence will cause those elements to be removed from the returned sequence.
DataTable oneTwo = one.AsEnumerable().Except(two.AsEnumerable()).CopyToDataTable();
DataTable twoOne = two.AsEnumerable().Except(one.AsEnumerable()).CopyToDataTable();

// Sort the results by "PCT" 
oneTwo.DefaultView.Sort = "PCT DESC";
twoOne.DefaultView.Sort = "PCT DESC";

// For each row (one except two)
foreach (DataRowView dr in oneTwo.DefaultView)
{
    string id = dr["ID"].ToString();
    string pct = dr["PCT"].ToString();
}

// Returns four rows
// 5, 5
// 3, 3
// 2, 2
// 1, 1  

// For each row (two except one)
foreach (DataRowView dr in twoOne.DefaultView)
{
    string id = dr["ID"].ToString();
    string pct = dr["PCT"].ToString();
}

// Returns four rows
// 5, 5
// 4, 4
// 2, 2.1
// 1, 1  


// Another Solution
// Or you can just do a WHERE
DataTable three = two.AsEnumerable().Where(r2 => !one.AsEnumerable().Any(r1 => r1.Field<int>("ID") == r2.Field<int>("ID") &&
                                                                                r1.Field<double>("PCT") == r2.Field<double>("PCT")))
                                    .CopyToDataTable();


three.DefaultView.Sort = "PCT DESC";

// For each row (based on where)
foreach (DataRowView dr in three.DefaultView)
{
    string id = dr["ID"].ToString();
    string pct = dr["PCT"].ToString();
}

// Returns two rows
// 4, 4  
// 2, 2.1

Thanks for the prompt answer. I 'm almost there, please take a look at my updated post. Hope you can help me solve this. — Vinod Kumar, May 22 '15 at 16:07
Make sure to only add rows, or data bind rows, do not do both. — Carl Prothman, May 22 '15 at 16:12
Here is a complete solution: https://msdn.microsoft.com/en-us/library/bb386998(v=vs.110).aspx — Carl Prothman, May 22 '15 at 16:18
can you pls correct my mistake. I am sorry to bug as I'm learning. — Vinod Kumar, May 22 '15 at 16:22
When I give values in PCT as follows ("0.1",, "0.1", "0.9") I should get only last two rows means (0.1 and 0.9 )but I am getting all rows from datatable two. — Vinod Kumar, May 22 '15 at 16:43
This method returns those elements in first that do not appear in second. It does not also return those elements in second that do not appear in first. Since all three rows are different, you get three results. Make sure read about Except. https://msdn.microsoft.com/en-us/library/vstudio/bb300779%28v=vs.100%29.aspx — Carl Prothman, May 22 '15 at 16:46
the rows can be different, but can I sort the column `ID` before we compare? — Vinod Kumar, May 22 '15 at 17:04
I've updated the solution to include a sort. Does this work for you? If so, please mark this solution an the answer. :) — Carl Prothman, May 22 '15 at 17:42

compare datatable and return unmatched rows

2 Answers2