Problem with data synchronicity

[Veritas]

Hello!

I'm developing an application for a transportation company. This application is going to be useful for the company's 4 dispatchers. Each of them are going to work with records of transportation information in a datagridview and will of course need to update them. Here's my problem: If one dispatcher updates 1 record in the database, it goes without saying the data on each dispatcher's computer must be refreshed in order to avoid data conflict. (2 dispatchers may select the same record and accidentally overwrite the other's work) My first guess was to refresh the datagridview every 5 seconds or so to avoid errors. However, if a dispatcher is working on a record and the application refreshes the grid before he has time to complete his stuffs, the modified data will be resetted with the database original information.

Does anyone knows how to proceed in a situation such as this one? Thanks!

 

[picoflop]

I'd start by adding a "record_version" field to any table that might be affected. When a
record is read and displayed, the record_version is stored on the users client. Before updating, check if the value is still the same. If not,do some handling. If yes, increment the value by 1. The other clients can check the value of "their" records each xx seconds and if the value changes, you ... do some handling

If your database supports triggers, I'd also create "archive" tables with same layout as the normal table. Before each update, let the trigger write the actual values (including record_version) into the archive table. This allows for checking who did what and when (if you log the user and time of course) and also allows to check (see above) in which fields a record has changed.

I'm not a big fan of the automatisms in DataGridView etc, therefore I cannot say, if such stuff would be supported natively.

 

[Veritas]

The idea looks good to me. I'll try to apply it in my project. Thanks!

 

[cjard]

By default the dataset design tools in VS will make SQL update statements that are optimistically concurrent. This means if two people attempt to download, edit and update a record, one of them will experience a ConcurrencyViolationException

At that point you would handle the exception, requery the DB and show the user:

"Another user has changed this record, here are their changes and yours. Do you want to:
Keep Theirs
Overwrite Theirs
Merge, Preferring Yours
Merge, Preferring Theirs
Choose Exactly
"

The way these work is:
Keep Theirs - don't save yours, their changes are the live
Overwrite Theirs - save yours over the top of theirs (every record)
Merge, Preferring Yours - merge the two together but use yours where their data matches neither your original or current versions (i.e. they changed something and you also changed it) - this is subtly different to above because if there was two fields, and they changed both - you only changed one, Merge Prefer Yours keeps their change to the field you didnt change, but overwrites their field with yours. Overwite Theirs would take the field you didnt change and overwrite the one they did change, with it:

Field1, Field2, Field3
A, 1, X 'original

B, 2, X 'your changes
C, 1, Y 'their changes

C, 2, Y 'merge prefer theirs
B, 2, X 'overwrite theirs
B, 2, Y 'merge prefer yours


Merge, Preferring Theirs - as above but other way round
Choose Exactly - let the user pick which values of each to keep