C# - How to find unmatching rows from two data table

Hi,

I have two datatable and i want to find unmatching rows from two datatable.

The below example shows,

i want to compare datatable1 and datatable2.

if any unmatching rows found then return the rows as datatable.

Note : Check both column as combination.

Pls. find screen shot for reference table.
ScreenShot.png
Ganesh STech Lead cum developerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Eric GreeneDirector of TechnologyCommented:
First of all, without knowing the application, I don't know if I'm giving you the best advice. Below is the code that answers your question as you have presented it. However, please read past it to get a better idea of more efficient methods.
        private DataTable getChanges(DataTable Datatable1, DataTable Datatable2)
        {

            //Create the table to be returned
            DataTable ResultDatatable = new DataTable();

            //Create Column SubA for the return table and apply type of int.
            DataColumn dc = new DataColumn("SubA", typeof(int));
            ResultDatatable.Columns.Add(dc);
            ResultDatatable.AcceptChanges(); //I always accept the changes after each column. I'm weird that way.

            //Create column SubB for the return table and apply type of string.
            dc = new DataColumn("SubB", typeof(string));
            ResultDatatable.Columns.Add(dc);
            ResultDatatable.AcceptChanges();

            try
            {
                //Move through each of the source rows (Datatable1) and compare it to Datatable2
                foreach (DataRow dr in Datatable2.Rows)
                {
                    /*Use the Select method to find column SubA value in Datatable1 based on the same value in Datatable2
                    If it is not found, then we know there is a new record to add.*/
                    DataRow[] found = Datatable1.Select($"SubA = {dr[0]}");

                    if (found == null || found.Count() == 0)
                    {
                        //Create an empty row to be added to ResultDatatable1 later.
                        DataRow change = ResultDatatable.NewRow();

                        /*Presumably you will always know the number and name of fields in a table. However
                        when structures change, you want to minimize the code you have to modify, so it's good
                        to use the column ordinals; hence the "for" instead of the foreach.*/
                        for (int i = 0; i < Datatable2.Columns.Count; i++)
                        {
                            //Assign the value of the new row to the row we will add to ResultDatatable1
                            change[i] = dr[i];
                        }

                        //Add the new row to ResultDatatable1
                        ResultDatatable.Rows.Add(change);
                    }
                }
            }
            catch (Exception ex)
            {
                return null;
                //Do error trapping here
            }
            return ResultDatatable;
        }

Open in new window


Now, if you are trying to compare tables in memory, that's the best way I know to do it. That will return a DataTable for you that is the result of comparing 1 and 2. However, if you have pulled Datatable1 from a SQL database, and Datatable2 is the result of user edits, then there is another way to go about inserting the changes. You can use the MERGE statement and only the changes will be applied. I would read this article: A Good Article on MERGE in SQL.
0
Dirk StraussSenior Full Stack DeveloperCommented:
This article might help: Compare Two DataTables for Non-Matching Records C#. From the article:

Using LINQ would be most natural, but you will need to convert away from the DataTable to use Except.
var In_dt1_only = dt1.AsEnumerable().Select(r => new { first = r.Field<string>("First"), last = r.Field<string>("Last")}).Except(dt2.AsEnumerable().Select(r => new { first = r.Field<string>("First"), last = r.Field<string>("Last")}));

Open in new window


If you need the original DataRows, you can use a Where instead:
var datarows_in_dt1_only = dt1.AsEnumerable().Where(dr1 => !dt2.AsEnumerable().Any(dr2 => dr1.Field<string>("First") == dr2.Field<string>("First") && dr1.Field<string>("Last") == dr2.Field<string>("Last")));

Open in new window

0
it_saigeDeveloperCommented:
You could also use the LINQ Except method:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EE_Q29074474
{
    class Program
    {
        static void Main(string[] args)
        {
            var table1 = (new[] { new { SubA = 101, SubB = "A" }, new { SubA = 103, SubB = "C" }, new { SubA = 104, SubB = "D" }, new { SubA = 106, SubB = "F" } }).ConvertToDataTable();
            var table2 = (new[] { new { SubA = 101, SubB = "A" }, new { SubA = 102, SubB = "B" }, new { SubA = 103, SubB = "C" }, new { SubA = 104, SubB = "D" }, new { SubA = 105, SubB = "E" }, new { SubA = 106, SubB = "F" } }).ConvertToDataTable();
            var missing = table2.AsEnumerable().Except(table1.AsEnumerable(), DataRowComparer.Default).CopyToDataTable();

            missing.PrintToConsole();
            Console.ReadLine();
        }
    }

    static class Extensions
    {
        public static DataTable ConvertToDataTable<T>(this IEnumerable<T> source)
        {
            DataTable table = new DataTable();
            var properties = TypeDescriptor.GetProperties(typeof(T));
            foreach (PropertyDescriptor property in properties)
            {
                if (property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
                    table.Columns.Add(property.Name, property.PropertyType.GetGenericArguments()[0]);
                else
                    table.Columns.Add(property.Name, property.PropertyType);
            }

            object[] values = new object[properties.Count];
            foreach (var item in source)
            {
                for (int i = 0; i < properties.Count; i++)
                    values[i] = properties[i].GetValue(item);
                table.Rows.Add(values);
            }
            return table;
        }

        static void DrawHorizontalSeperator(this int width, char seperator)
        {
            Console.WriteLine(new string(seperator, width));
        }

        public static void PrintToConsole(this DataTable table)
        {
            var width = (25 * table.Columns.Count) + table.Columns.Count;
            Console.WriteLine("Table Name: {0}", table.TableName);
            width.DrawHorizontalSeperator('=');
            Console.WriteLine("|{0}|", string.Join("|", table.Columns.Cast<DataColumn>().Select(x => string.Format("  {0}  ", x.ColumnName).PadRight(25))));
            width.DrawHorizontalSeperator('=');
            foreach (var row in table.Rows.Cast<DataRow>())
                Console.WriteLine("|{0}|", string.Join("|", row.ItemArray.Select(x => string.Format("  {0}  ", x.ToString()).PadRight(25))));
            width.DrawHorizontalSeperator('-');
        }
    }
}

Open in new window

Produces the following output -Capture.PNG
-saige-
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ganesh STech Lead cum developerAuthor Commented:
Best Solution
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.