Dataset Merge Duplicates

I gotta be doing something wrong:

i'm Using the SqlClient namespace

I need to detect changes in a SQL SERVER 2000 database.table

I am, initially, setting a DataSet with the table (ds as Dataset)

Then, at specified intervals, i'm setting another (different) dataSet with the Live data with the same SQL connection, and SQL query as the initial (dsLive as Dataset).

I then MERGE the newly created dataset with the saved, "cached" dataset:
ds.Merge(dsLive)

I then, create ANOTHER dataset with changes:
dsChanges = ds.GetChanges()

I have 2 problems:

1) After MERGE, duplicate rows are present! I have a primarykey on the table. I thought this MERGE handled this that way.

2) dsChanges is nothing even though there were changes made from the dsLive dataset (rows were added.)

In summary:

   ds = a dataset from a SQL 2000 database table
   dsLive = a dataset from the same SQL 2000 database table at a later time (using timer control)
   ds.Merge(dsLive) = dsLive into ds should have flagged ds with changes.
   dsChanged = ds.GetChanges() results in nothing.

more info:
- sqlconnection is opened only when used (dont know if that matters).
- rows were ADDED (none were changes, though ideally I would like the ds to dsLive merge to detect changes as well.)

any help appreciated.

Answer this question

Dataset Merge Duplicates

L&#233;o Gagn&#233;
Hi,
I too have a same requirement. I have two datasets with same schema. I want to get the difference between these datasets. ( newly added or changed rows )

I have seen a lot of samples with looping through entire data. cant use it becuase it will afffect the performance.

Thanks
Anz
SMaste
Hi Eric,

How do you create the primary keys on both DataSets Are they AutoIncrement
If you don't have pks you'll end up with duplicate rows.

Every row in a datatable has a state, and that plays an important role on Merge.
Read how it works here, here and here.

This approach to detect differences may have perf issues if you deal with large nb of rows, because fetching from SQL, transporting over the network and essentially duplicating and then comparing them takes some time.

Other options to consider:
* DB triggers / sps that would add records to some activity table, storing the pk and type of change + timestamp
* if you just want to know whether there were changes or not, you can use CHECKSUM_AGG.

--VV [MSFT]
Tomi B.
Hi Eric,

"It seems like i'll have to understand these row states as I am not using (as far as I know)."

DataSet is an in memory cache, and as such states are very important for change tracking. Personally, i had a similar reaction to yours (why do i need to know this... ) but once i realized how important the row state is in a lot of scenarios, i investigated it and it's honestly not that hard to grasp.

"Yes, they are UniqueIdentifier with a default set to NewID() constraint. That is what you meant by Autoincrement "
I mean on the DataSet, not on SQL Server. Look at the article for AutoIncrement, which should actually exhibit a similar behavior to UniqueIdentifier, and explain why you end up with duplicates.

Regarding your issue, if you give a more complete description of the problem you're trying to solve, maybe we can come up with better approaches.

--VV [MS]

The Elk Mechanic

Vasco Veiga wrote:

How do you create the primary keys on both DataSets Are they AutoIncrement
If you don't have pks you'll end up with duplicate rows.

Yes, they are UniqueIdentifier with a default set to NewID() constraint. That is what you meant by Autoincrement

Vasco Veiga wrote:

Every row in a datatable has a state, and that plays an important role on Merge.
Read how it works here, here and here.

This approach to detect differences may have perf issues if you deal with large nb of rows, because fetching from SQL, transporting over the network and essentially duplicating and then comparing them takes some time.

It seems like i'll have to understand these row states as I am not using (as far as I know).
As for performance, I will defintely be open to your suggestions as this will need to be as quick as possible.

Vasco Veiga wrote:

Other options to consider:
* DB triggers / sps that would add records to some activity table, storing the pk and type of change + timestamp
* if you just want to know whether there were changes or not, you can use CHECKSUM_AGG.

--VV [MSFT]

Thanks VV, I am just getting started and look forward to trying these suggestions.

Thanks again,
eric

nielsb
Hello VV,

The issue was because the dataset was not properly representing the underlying schema.

I will need to remember this dual feature of datasets (of setting up a schema when setting up a dataset).

So my issue, I think for now, is resolved by mimicking the schema's between the dataset and the SQL Server database table.

I'll keep you posted but will mark the question as replied.

Thanks again,

Eric
Dataset Merge Duplicates

Answer this question