Datatable Merge query

21 Feb 2006 1:08 PM

Campbell Wild

Hi,

I am trying a simple approach to updating a datatable.

I have a set query that I run on the database. This populates a
datatable, 'dtMyData'.

Every X seconds, I re-run the query and store in a separate datatable
'dtUpdated'. I then call dtUpdated.Merge(dtMyData). This successfully
updates my dtMyData datatable with the new rows from the requery.

My problem is, I want to know which rows are updated/changed/deleted.
Everything in the updated dtMyData has RowState set to 'Unchanged'. Why
don't the added rows from the Merge operation get set to 'Added'?

If I set the preserveChanges parameter of the Merge operation to True,
it marks all the rows as 'Modified'. Neither of these approaches seems
very useful to me.

Is there any way to just mark the modified/added/deleted rows in my new
table, so I'm able to call dtMyData.GetChanges?

Or is there a better way to do this?

Thanks,
Campbell

21 Feb 2006 2:52 PM

W.G. Ryan - MVP

Show quote

"Campbell Wild" <goo***@adrift.org.uk> wrote in message
news:43fb10c0$0$82647$ed2619ec@ptn-nntp-reader03.plus.net...
> Hi,
>
> I am trying a simple approach to updating a datatable.
>
> I have a set query that I run on the database. This populates a
> datatable, 'dtMyData'.
>
> Every X seconds, I re-run the query and store in a separate datatable
> 'dtUpdated'. I then call dtUpdated.Merge(dtMyData). This successfully
> updates my dtMyData datatable with the new rows from the requery.
>
> My problem is, I want to know which rows are updated/changed/deleted.
> Everything in the updated dtMyData has RowState set to 'Unchanged'. Why
> don't the added rows from the Merge operation get set to 'Added'?

--Does the table have a key on it? Also, are you calling AcceptChanges
anywhere?

>
> If I set the preserveChanges parameter of the Merge operation to True,
> it marks all the rows as 'Modified'. Neither of these approaches seems
> very useful to me.
>
> Is there any way to just mark the modified/added/deleted rows in my new
> table, so I'm able to call dtMyData.GetChanges?

You can use a DataView and set the RowStateFilter as an example of how to
get just the specific rows. To do this, call the GetChanges() method
specificying a DataRowState
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatasetclassgetchangestopic.asp

DataTable NewData = OldDataTable.GetChanges(DataRowState.Added);// will
return only the added rows. Repeat for the other types of rowstate aas well

>
> Or is there a better way to do this?

--I'm not positive I understood the question but I answered what I thought
you were asking. If not, please let me know. Also, let me know about the PK
on the table, without one you can get some funky behavior
Show quote

>
> Thanks,
> Campbell

21 Feb 2006 5:53 PM

Campbell Wild

Basically, I have a number of client applications, and they all need to
update with the latest data from the database.

I have a set query that I run once per minute on each client. I
initially read this query into a datatable, but I need to update this
datatable whenever the data on the database changes. I also need to
know each datarow that has been added, deleted and modified.

I thought the best way would be to create a second datatable with the
updated query, then merge that data into the original datatable which I
hoped would give me the updated data (it does) and mark any changes in
the new table (it doesn't seem to) so I can then process the
new/changed/deleted rows in my client app before marking the changes in
the original table with AcceptChanges.

Both datatables have the same primary key which is not an ident field.

Thanks again for any assistance.
Campbell

W.G. Ryan - MVP wrote:
Show quote

> "Campbell Wild" <goo***@adrift.org.uk> wrote in message
> news:43fb10c0$0$82647$ed2619ec@ptn-nntp-reader03.plus.net...
>> Hi,
>>
>> I am trying a simple approach to updating a datatable.
>>
>> I have a set query that I run on the database. This populates a
>> datatable, 'dtMyData'.
>>
>> Every X seconds, I re-run the query and store in a separate datatable
>> 'dtUpdated'. I then call dtUpdated.Merge(dtMyData). This successfully
>> updates my dtMyData datatable with the new rows from the requery.
>>
>> My problem is, I want to know which rows are updated/changed/deleted.
>> Everything in the updated dtMyData has RowState set to 'Unchanged'. Why
>> don't the added rows from the Merge operation get set to 'Added'?
> --Does the table have a key on it? Also, are you calling AcceptChanges
> anywhere?
>> If I set the preserveChanges parameter of the Merge operation to True,
>> it marks all the rows as 'Modified'. Neither of these approaches seems
>> very useful to me.
>>
>> Is there any way to just mark the modified/added/deleted rows in my new
>> table, so I'm able to call dtMyData.GetChanges?
> You can use a DataView and set the RowStateFilter as an example of how to
> get just the specific rows. To do this, call the GetChanges() method
> specificying a DataRowState
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatasetclassgetchangestopic.asp
>
> DataTable NewData = OldDataTable.GetChanges(DataRowState.Added);// will
> return only the added rows. Repeat for the other types of rowstate aas well
>> Or is there a better way to do this?
> --I'm not positive I understood the question but I answered what I thought
> you were asking. If not, please let me know. Also, let me know about the PK
> on the table, without one you can get some funky behavior
>> Thanks,
>> Campbell
>
>

21 Feb 2006 3:04 PM

Cor Ligthert [MVP]

Campbell,

Why are you doing it this way.

If you update correct a complete dataset than the rowstates are set to
unchanged after that.
If you update correct a getchanged copy of a dataset than you can do after a
the update process on the originaldataset.acceptchanges after that.

Beside with an autoincrementkey will there never be done any changes in your
dataset than what I have written now. However if you than want to merge a
getchangedcopy, you are in problems because the keys are changed.

(autoincrementkey only by SQLclient not by OLEDB, with OleDB you have than
to do a refill)

Just my thought,

Cor