Click here to Skip to main content
15,945,119 members
Please Sign up or sign in to vote.
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
Updated 24-Sep-13 3:08am
v3
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

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
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900