Compare 2 DataTables to update RowState

[anyMouse]

I have the following scenario, and would appreciate some advice on the
best approach to give the results I require:

Simple example:
------------------------
2 Datasets, the second cloned from the first:
dsOriginal.Tables[0] has 2 rows (string, boolean)

row 1: red, true
row 2: blue, true

The second cloned Dataset table is populated as follows:
dsNewData.Tables[0] has 2 rows (string, boolean)
row 1: red, false
row 2: green, true

I want to compare the dsNewData to dsOriginal to give the following
result:
dsOriginal.Tables[0].Rows[0] has been changed (from "red, true" to
"red, false")
dsOriginal.Tables[0].Rows[1] has been deleted (its not in
dsNewData.Tables[0])
dsNewData.Tables[0].Rows[1] has been added ("green, false" did not
exist in dsOriginal)

I have been trying to get this result using the DataSet.Merge method,
but the RowState does not reflect the results I have listed above. Any
other suggestions would be greatly appreciated!

 

[NuTcAsE]

For DataSet.Merge to work, the two data tables must contain an Id
column (used as Primary Keys) and matching rows in both data sets must
have the same Id. For example:

DataTable 1:
ID COLOR Bool
=============
1 red true
2 blue true

DataTable 2
ID COLOR Bool
=============
1 red false
2 blue true /*Deleted*/
3 green true

When you call merge the two data sets, the merge operation will check
matching rows using the primary keys of both data tables, and the
resulting data set should contain:

DataTable 1
ID COLOR Bool
=============
1 red false
2 blue true /*Deleted*/
3 green true


Hope this helps...
NuTcAsE

 

[anyMouse]

The real issue I am trying to figure out is how to programmatically set
the rowstate of unmatched rows to deleted (I see ADO.NET v2 has a
method which would allow this, but how can this be done in v1?)

So in the example above, when comparing the 2 dataset tables, "blue" no
longer exists in the 2nd table. How can I therefore set its rowstate to
deleted?

 

[NuTcAsE]

Umm.. ok... then...delete the row. When you call Row.Delete() it marks
the row as being deleted and is not displayed in the databinding and is
not available while enumerating the data table. Also, if the row was
added it will removed from the table.

Hope this helps...
NuTcAsE

 

[anyMouse]

I've found a way of getting the result I was after. I was hoping that
doing a DataSet.Merge would be "clever" enough to automatically set the
rowstate to deleted for any rows that did not match between the two
datasets being merged - this does not happen, so to get round this I
had to:

1 - Create a clone of the DataSet (or DataTable) to compare to
2 - manually add the new data to compare, and call AcceptChanges()
3 - loop through the new cloned dataset, and do a Find() for each PK
value. If a match found, check each field value with that of the
original. If all same, do nothing. If any differences, update values in
original dataset (thus setting rowstate to modified) - if no match
found, add row to original dataset, thus setting rowstate to added. So
now my original dataset contains rows marked as added and modified (and
unchanged)
4 - now need to find which rows from the original need to be marked as
deleted; to do this I reverse loop through the original DataSet (NOT
using enumerator), and do a row Find() against the newly cloned
dataset. Again, if no match found I call Rows[counter].Delete(), thus
marking the rowstate as deleted.
5 - finally, to get the added, modified and deleted rows, I create a
dataview for each of the DataViewRowState (ModifiedCurrent, Added and
Deleted) - to view the values, get an enumerator on the dataviews and
get hold of the DataRow views (with delete need to pass the optional
DataRowVersion.Original arg in the field index to avoid exception being
thrown)

So, I manage to get there in the end - I suppose I was hoping that
DataSet.Merge() would automatically update, add and delete rows - but
no. ADO.NET 2 also has some useful new methods which let you
programatically set the rowstate value which would have simplified the
above steps somewhat.