Difference Between 2 DataTable

Question

I have 2 DataTable and I want to create a third DataTable that contais the difference between DataTable 1 and DataTable 2.

For example, DataTable1 has the original data, and the DataTable 2 is just a copy, like a replication. But when you insert a new row in DataTable1, the DataTable2 has just insert the same row. Nowaday my code do a compare between DataTable1 and DataTable2, if not equals (1 row or more was inserted), DataTable2 record all data from DataTable1 again.

How can I do a select command, that do this difference and record those datas in a third DataTable ?

You can't perform extensive queries on `DataTable`s with code. You can't, for example, simply write `SELECT * FROM TABLE;`. You can, however, use LINQ to write pseudo-queries. — qJake, Sep 26 '11 at 20:14
This one can help you. http://stackoverflow.com/questions/164144/c-how-to-compare-two-datatables-a-b-how-to-show-rows-which-are-in-b-but-not — Göktürk Solmaz, Sep 26 '11 at 20:35

score 1 · Answer 1 · answered Sep 26 '11 at 20:16

1

Try something like this:

table1.Merge(table2); 
DataTable changesTable = table1.GetChanges();

answered Sep 26 '11 at 20:16

James Johnson

45,496
8
73
110

The third DataTable, returns Null with GetChanges method. – Lucas_Santos Sep 26 '11 at 20:31
No, I think changesTable will be populate with all the records Table1 have after merging. – Scorpion Sep 29 '11 at 15:53
Duplicates will be discarded, and only the differences between `table1` and `table2` will be populated into the `changesTable`. – James Johnson Sep 29 '11 at 15:56
@JamesJohnson I have tested it, but its populating `changesTable` with all the records from Table1. I think linq is the only solution to solve this problem. – Scorpion Sep 29 '11 at 16:20
That doesn't make sense. If anything, it should populate the changesTable with the records from table2. – James Johnson Sep 29 '11 at 16:22
Better you try this code: `DataTable dt1 = new DataTable(); dt1.Columns.Add("ID", typeof(int)); dt1.Columns.Add("Name", typeof(string)); dt1.Rows.Add(1, "James"); dt1.Rows.Add(2, "Thom"); DataTable dt2 = new DataTable(); dt2.Columns.Add("ID", typeof(int)); dt2.Columns.Add("Name", typeof(string)); dt2.Rows.Add(1, "James"); dt2.Rows.Add(3, "Chris"); dt1.Merge(dt2); DataTable dt3 = dt1.GetChanges();` – Scorpion Sep 29 '11 at 16:28
I think its happening because I am populating `DataTables` mannually. I need to call the AcceptChanges `dt1.AcceptChanges();` before merging. – Scorpion Sep 29 '11 at 16:32

score 0 · Answer 2 · answered Sep 26 '11 at 20:23

Using only SQL you can use UNION to easily find differences, there is an excellent article on the subject here: http://weblogs.sqlteam.com/jeffs/archive/2004/11/10/2737.aspx

The query will return an empty row set when the tables match, otherwise the differing rows are returned.

score 0 · Accepted Answer · answered Sep 27 '11 at 08:25

I will consider that there are two columns to identify the tables(col1,col2)

var rowsOnlyInDt1 = dt1.AsEnumerable().Where(r => !dt2.AsEnumerable()
                    .Any(r2 => r["col1"].Trim().ToLower() == r2["col1"].Trim().ToLower() && r["col2"].Trim().ToLower() == r2["col2"].Trim().ToLower()));

DataTable result = rowsOnlyInDt1.CopyToDataTable();//The third table

Difference Between 2 DataTable

3 Answers3

Linked

Related