Handling Concurrent Updates to Datasets

[Guest]

I have a form based on a single master record stored in a dataset table.
The master record references many other tables (about 12) in the dataset.
I use combo boxes to display the current values and to enable value lookup's for editing.
Unfortunately, most of these references refer to sql tables which may be updated by other users.
If I refill the main table with a new record then it is possible that it makes reference to some value that I don't have cached in the Dataset.
Is there any way to detect this situation and to cause a refill for the related table(s)?

It is essential that I don't have to reload every related table for each main record read as this could entail nearly 600 reads.

Ideally only the values for the current master record and referenced values would be present in the dataset.
A combobox show event would refill that table on demand. Any Ideas?

 

[Cor Ligthert]

Hi CarlT,

If I understand you well, than that would be the good approach. Than you
need only to fill the needed data using the WHEN clause and use for that the
selected values from your comboboxes.

Your dataset holds only the tables, the can be refreshed at any time.

And of course check the data on concurrency errors when updating it again,
that stays.

I hope that was what you meant?

Cor

I have a form based on a single master record stored in a dataset table.
The master record references many other tables (about 12) in the dataset.
I use combo boxes to display the current values and to enable value lookup's for editing.
Unfortunately, most of these references refer to sql tables which may be updated by other users.
If I refill the main table with a new record then it is possible that it

makes reference to some value that I don't have cached in the Dataset.

Is there any way to detect this situation and to cause a refill for the related table(s)?

It is essential that I don't have to reload every related table for each

main record read as this could entail nearly 600 reads.

Ideally only the values for the current master record and referenced

values would be present in the dataset.

 

[Guest]

Thank you for the reply.
Scenario:
User 1 Wishes to create a new detail record but finds that a required referenced value does not exist. User 1 then creates the value (using another form) and refills their dataset tables and proceeds to create the new detail record.
User 2 Reads the newly created detail record to edit it, but retains their current reference tables in their dataset. User 2 fails - the new reference item is not cached in their dataset.
Question how to detect this and refill the reference tables so that the edit can proceed without interuption? Also extend this problem to downloading multiple detail records - some of which may refer to non-cached values.

I suspect that ADO.NET is not designed for this kind of operation. Combo boxes in Access Projects (ADP) use a private database connection to dynamically fetch missing values to circumvent this problem. Perhaps I need to develop a similar type of combo box.
Is there a common work-around? Does anyone have a solution?

 

[Guest]

Cor,
I believe I have a solution.
1. Create a DataAdapter based on a SQL view which provides all the data for the detail record (including any values from joined tables). Use the option to use SQL Server stored procedures, this gets around the problem that the view does not have a defined key.

2. Convert the ComboBoxes to a new control type (Let's call it DynamicSelector).
The Dynamic Selector control looks like a ComboBox but has the following features:
2a. It uses the cached values in a dataset table (RefTab say). On first dropdown use it will fill RefTab from SQL Server.
2b. If the current detail record has a SelectedValue/DisplayedValue which is not in RefTab then those values are added and then the drop down is displayed. At this point RefTab may not contain all current records currently in the corresponding SQL
table, but it contains the majority of values including the values appearing on the current detail record.
2c. The user can select one of the displayed values or click a small button on the dropdown to refresh the list from SQL Server.

This has the advantage that data is only downloaded if/when needed.

If anyone has a better solution please resopond.

 

[Cor Ligthert]

Hi Carl,

In my opion you have to make your approach as simple as possible, and keep
in mind, a dataset is standard using optimistic concurrency.

As well is it possible to refresh data, however keep in mind the more you
load useless, the more change you have that there was another one who did a
change.

A sample of a most simple approach.

We want to buy a book
make three comboboxes
one with as datasource a datatable with kind of books
one with as datasource a datatable with selected authors by kind
one with as datasource a datatable with selected books by author and kind
Make a selection of the kind of book
Let the user make a choise
read all the authors in a dataset which have written those books
select the author in a combobox
after the choise of the user
read all the books of that author and that kind of books in a dataset
after that do what you have to do.

And when in the main time someone else adds a book of that kind, a pity when
it had been 5 minutes earlier it had been the same. (This is not when it is
about a banking account or something like that of course, there you should
add proper checking on that and make your correction routines or choose for
pesimistic concurrency)

And when someone deletes a book (or they are sold out) or whatever other
change on the data, make the proper correction procedures after the try to
update. Remember a dataset is disconnected using optimistic concurrency,
there can always happen something in the maintime.

I hope this helps?

Cor

Thank you for the reply.
Scenario:
User 1 Wishes to create a new detail record but finds that a required

referenced value does not exist. User 1 then creates the value (using
another form) and refills their dataset tables and proceeds to create the
new detail record.

User 2 Reads the newly created detail record to edit it, but retains

their current reference tables in their dataset. User 2 fails - the new
reference item is not cached in their dataset.

Question how to detect this and refill the reference tables so that the

edit can proceed without interuption? Also extend this problem to
downloading multiple detail records - some of which may refer to non-cached
values.

I suspect that ADO.NET is not designed for this kind of operation. Combo

boxes in Access Projects (ADP) use a private database connection to
dynamically fetch missing values to circumvent this problem. Perhaps I need
to develop a similar type of combo box.