merge on datasets from join queries

[Glenn]

I have lookup/record selection grids bound to datasets with lists based on
join queries. Periodically, I want to update these datasets to reflect
changes made by other users. It seems the dataset.merge on the original
dataset with a dataset of the refreshed records just duplicates the records.
Although the list is a main table with PK and the other tables in the join
query are just lookup values (leaving only one record per PK), the dataset
merge does not work.

Is this possible or is the only way to "refresh" a dataset based on a joined
table to loop through and update each field? Note, I do not want to just
re-fill the original dataset as it is bound to a grid and so all the event
firing and other binding operations would be more difficult to handle.

Thanks.

Glenn

 

[William Ryan eMVP]

I'm not sure I follow you on the problem with using Merge - if the records
aren't changed nothing will happen. But are you using a Join query on the
back end? If so you can just use single table queries, kill the joins, use
separate adpaters for each table involved in the join - then bind them with
a DataRelation object. You can at any point thereafter fire an update on
each table individually. You can disable the constraints of the dataset
temporarily by setting the EnableConstraints property to false - you can
also set the BeginLoadData property of the given table to false (then set it
to true when you're done) which will stop those events from firing during
the load - this should provide a modest performance boost as a side effect.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

Have an opinion on the effectiveness of Microsoft Embedded newsgroups?
Let Microsoft know!
https://www.windowsembeddedeval.com/community/newsgroups

 

[Sahil Malik]

Glenn,

If I understood correctly, Problem statement - "You want to refresh the
contents of your dataset with changes happening in the database".

I believe you are on the right track for the solution. Consider the
following --

a) Join queries are in the database, Dataset.Merge is in ADO.NET -- Two
different animals. We can simplify this situation by, "You are filling the
dataset somehow".
b) Have you specified a primary key on the two Datatables (in ADO.NET) you
are trying to merge within your datasets? More so, are the Datatable names
the same? Dataadapter.fill, will not by default pull in primary key
information etc. There is a fillschema, method but my recommendation is not
to use that in a production application since that executes a fairly heavy
query on your d/b.

Dataset.Merge has pretty clearly laid out rules of how it will merge. And
yes it can be used to refresh data from the database (or anywhere else).

- Sahil Malik
You can reach me thru my blog -
http://www.dotnetjunkies.com/weblog/sahilmalik

 

[Sahil Malik]

Glenn,

When you do A.Merge(B), it will not handle deleted rows out of the d/b,
because ... no information in B tells A that certain rows have been deleted.
You have to do that part manually.

- Sahil Malik
You can reach me thru my blog -
http://www.dotnetjunkies.com/weblog/sahilmalik

 

[Glenn]

Sahil:

The source and destination datasets are being filled by the same stored
procedure and have the same
table name. Your comment that "Dataadapter.fill, will not by default pull
in primary key
information etc" pointed me in a helpful direction: the query is an outer
join and Help said that you need to add a primary key for outer joins (they
are not pulled in)

This helped somewhat, now changes made by others are reflected and rows
added by others are added, but for some reason rows not in the source
dataset (either deleted by others or modifired so they do not meet the
criteria of the original dataset) are not removed from the destination
dataset.

My scenario is that a user has a list of items, usually filtered by a
status, from which he picks a record to load and edit. This list is
refreshed periodically to reflect changes by other users. If another user
has edited a displayed record so that it no longer meets the filter for the
list, when the refresh is run and the record is no longer in the source
dataset, I want it removed from the list. This is not happening on the
merge.

I guess I have to loop through and handle manually.

Thanks for your help.

Glenn