how to compare 2 Datatables(with same columns) by one column(which is key column)

Question

hi

I have 2 DataTables with same columns and there is one key columns.

Something like

TableA Id Name

         1 Jack

         2 Rose

         3 Bill

TableB Id Name

        1 Jack

        2 Luke

        5 Martin

I need to compare them, to know for Id=1, nothing changed, for id=2, the name is changed ,for Id=3, it is deleted and for id=5, it is added.

I know in sql server, there is "merge" for 2 tables, but for DataTable of C#, how to do it? Thank you

Answer 1

Hi @Martin Wang ,

You can refer to the following code, using linq to find out the difference between Datatable A and Datatable B.

        static void CompareTables(DataTable tableA, DataTable tableB)
        {
            Console.WriteLine("Changes in Table B relative to Table A:");

            var modifiedRows = from rowA in tableA.AsEnumerable()
                               join rowB in tableB.AsEnumerable() on rowA["Id"] equals rowB["Id"]
                               where rowA["Name"].ToString() != rowB["Name"].ToString()
                               select new { Id = rowA["Id"], OldName = rowA["Name"], NewName = rowB["Name"] };

            foreach (var row in modifiedRows)
            {
                Debug.WriteLine($"Name modified for ID {row.Id}: '{row.OldName}' -> '{row.NewName}'");
            }

            var deletedRows = from rowA in tableA.AsEnumerable()
                              where !tableB.AsEnumerable().Any(rowB => rowB["Id"].ToString() == rowA["Id"].ToString())
                              select new { Id = rowA["Id"] };

            foreach (var row in deletedRows)
            {
                Debug.WriteLine($"Row deleted with ID {row.Id}");
            }

            var addedRows = from rowB in tableB.AsEnumerable()
                            where !tableA.AsEnumerable().Any(rowA => rowA["Id"].ToString() == rowB["Id"].ToString())
                            select new { Id = rowB["Id"], Name = rowB["Name"] };

            foreach (var row in addedRows)
            {
                Debug.WriteLine($"Row added with ID {row.Id}, Name: '{row.Name}'");
            }
        }

Best Regards.

Jiachen Li

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.