Compare 2 datatables to find difference

Question

0.00/5 (No votes)

See more:

I know this question has been asked multiple times and i have read them, but my question goes a bit differently.

I have 2 tables. (not sql tables, these are c# DataTables).

tab1 (the original table):
col A
col B
col C

and tab2 (copy of the original. this table's data may be modified by the user):
col A
col B
col C

what i want to do is compare tab2 to tab1 and find the diference.
but, what i want is a little more detailed:

1) Find all rows that are in tab2 but not in tab1 , ie., new rows generated by the user
and use these to preform insert on the DB.

2) Find all rows that are in tab1 but not in tab2, ie., rows that have been deleted by the user and use them to perform delete/soft delete on the DB.

3) Find all the rows excluding those selected in the above 2 steps and perform an update on the DB. here, it doesnt matter if these rows have indeed been updated. But it would be better if we could find only the updated rows...

Any ideas and help are appreciated.

Update 1:
A) The tables are C# DataTables, not sql/mysql DB tables. We find the difference in the DataTables and insert/update/delete those rows later from the DB tables. So I want to compare two c# DataTables.

B) AcceptChanges has already been called on the copy Table. This is because these tables are bound to a DataGridView. Changes in value of column A or B causes related changes in column C in case there are such related columns. For example if value of column C = column A + column B...
And these changes need to be reflected realtime to the DataGridView. So the comparision is between one original DataTable and another modified copy of the DataTable.

Posted 22-Sep-13 18:48pm

princektd

Updated 24-Sep-13 3:08am

v3

Add a Solution

Comments

superselector 23-Sep-13 1:36am

probably use can use JOIN

superselector 23-Sep-13 1:37am

http://forums.asp.net/t/1459790.aspx

princektd 24-Sep-13 9:01am

Sorry, i forgot to mention that the tables meant c# DataTables. I have updated the question accordingly. Thank you for the reply.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Orcun Iyigun** · Answer 1 · 2013-09-22T19:39:00

Solution 1

First of all what have you tried?[^]

I will not give you exact solution since I don't see enough effort? But you can solve your problems by several things such as JOINs,EXCEPT or EXIST.
An example will be

SQL

(   SELECT * FROM table1
    EXCEPT
    SELECT * FROM table2)
UNION ALL
(   SELECT * FROM table2
    EXCEPT
    SELECT * FROM table1)

For SQL joins: w3School SQL JOIN[^]

And some more reference links:
Link #1[^]
Link #2[^]
Link #3[^]

Good luck,
OI

Posted 22-Sep-13 19:39pm

Orcun Iyigun

Comments

princektd 24-Sep-13 9:00am

Sorry, I forgot to mention that by tables, i meant system datatables we use in c#, not sql/mysql tables... I have updated the question. Sorry again.

Is there any way i can use the 'except' on a System.DataTable?
Linq has anything similar?

I have tried using DataRelations on a DataSet and i got till finding rows which exist in both tables but have been modified. Here the DS contains the two DataTables to be compared and I set datarelations on it.

I already figured out a solution using foreach loops, but that is not an elegant looking solution.
What I am looking for is some way which can avoid the foreach/for loops and maybe Linq query the DataTables or use lambda expressions... or maybe dataset datarelation themselves... Though performance wise which would be better?

Thank you for the reply.