How to get the differences between two DataSets in a thrid one?

[Andreas Huber]

Hi there

I have two typed DataSets. One represents the state of a certain DB
table some time ago, the other represents the current state of the
same DB table. Now I would like to get the changes in a third table,
each row with the appropriate row state Added, Deleted or Modified. Is
there any way to do that with .NET (except for hand-crafting the
comparison myself)?

I have tried to get the changes with setting
SqlDataAdapter.AcceptChangesDuringFill to false and then filling the
old data set. But this sets *all* rows to Modified, even if there was
no change at all!

I need this functionality because I only want to send the data that
has actually changed to the clients (transmitting the full DataSet is
not an option because it can very well be 60MB+ in size and network
bandwith is precious).

Any help is greatly appreciated.

Thanks,

Andreas

[Nicholas Paldino [.NET/C# MVP]]

Andreas,

Have the changes occured on the back end database, or have the changes
occured in the dataset (you manually added rows, etc, etc). If the case is
the latter, then you can call the GetChanges method on the DataSet and it
will give you a new DataSet with only the changes since the last time
AcceptChanges has been called on the dataset.

If the case is the former, then you will have to query the back end.
You have a list of unique ids that were in the table, so if you select out
the records from the table where the id is not in that set, those are the
adds. Then, you have to perform a select where the ids in that set are not
in the table. These are the deletes. Finally, you will have to do a
comparison (either field by field, or by a timestamp of some sort) for the
rows that still exist in the table (these are edits).

Hope this helps.
--
- Nicholas Paldino [.NET/C# MVP]
- ni**************@exisconsulting.com

"Andreas Huber" <ah****@gmx.net> wrote in message
news:3e**************************@posting.google.c om...

Hi there

I have two typed DataSets. One represents the state of a certain DB
table some time ago, the other represents the current state of the
same DB table. Now I would like to get the changes in a third table,
each row with the appropriate row state Added, Deleted or Modified. Is
there any way to do that with .NET (except for hand-crafting the
comparison myself)?

I have tried to get the changes with setting
SqlDataAdapter.AcceptChangesDuringFill to false and then filling the
old data set. But this sets *all* rows to Modified, even if there was
no change at all!

I need this functionality because I only want to send the data that
has actually changed to the clients (transmitting the full DataSet is
not an option because it can very well be 60MB+ in size and network
bandwith is precious).

Any help is greatly appreciated.

Thanks,

Andreas

[Morten Wennevik]

You can get changes made to a dataset with

DataSet dsChanges = dsChangedDataSet.GetChanges();

This returns only the rows that has changed.

If you use AcceptChanges you tell the dataset that you want to save the
changes and discard changedata. Which is something you want to do after
you update a database.

However, if you have two datasets with no changedata and you want to find
out what has changed by comparing the two ... I have no idea. Sounds like
hard work. Something involving foreach(DataRow in dsChangedDataSet.Rows).

--
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/

[Andreas Huber]

Nicholas Paldino [.NET/C# MVP] wrote:

Andreas,

Have the changes occured on the back end database, or have the
changes occured in the dataset (you manually added rows, etc, etc). [snip]

They have occured in the database. There is one server with DB and an
arbitrary number of (remote) clients. Clients can read from a given DB
table, modify the returned data set and send the changes back to the server,
which will write them back to the database. So far there is no problem, as
this can nicely be done with DataSet.GetChanges().
However, changes made with client A must also be visible on client B as soon
as possible. It's no problem to detect that a particular DB table has
changed, but it's not at all clear which (if any) of the rows that are
currently displayed on B have changed (A might have displayed a differently
sized page of rows). That's why I asked my original question.
If the case is the former, then you will have to query the back
end. You have a list of unique ids that were in the table, so if you
select out the records from the table where the id is not in that
set, those are the adds. Then, you have to perform a select where
the ids in that set are not in the table. These are the deletes.
Finally, you will have to do a comparison (either field by field, or
by a timestamp of some sort) for the rows that still exist in the
table (these are edits).

Hmmm, I would have guessed that I'm far from being the only one with this
problem. It's a bit disapointing to hear that I have to implement what
presumably a lot of people have written before :-(, but thanks anyway.

Regards,

Andreas