In any disconnected environment the problem of concurrent updates is obvious. Update via Dataset is no exception. There are various solutions to tackle with this issue and which one to use depends upon your application, requirements and database schema.
The actual problem with the disconnected environment is that the data at the time of update is not the same as it was at the time you fetched it. This situation is referred as concurrency violation and the concurrency is said to be "Optimistic".
There are few points that you should think of:
- Is it OK with your application to overwrite such data (which is modified by somebody else)?
- Do you want to cancel your updates if such violation is observed?
- Do you want to ask the user whether he/she wants to overwrite the data?
Once the user has been notified of the concurrency issue, you could leave it up to her to decide how to handle it. Another alternative is to code a specific way to deal with concurrency, such as always handling the exception to let the user know (but refreshing the data from the database). Let the user decide what to do next. The user can cancel changes or cancel and reload from the database or save the changes or save anyway.
Cancel changes : The option to cancel changes simply calls the RejectChanges method of the DataSet and rebinds the DataSet to the controls in the ASP.NET page. The RejectChanges method reverts the changes that the user made back to its original state by setting all of the current field values to the original field values.
Cancel and reload from the database :The option to cancel changes and reload the data from the database also rejects the changes but additionally goes back to the database via the Employee class in order to get a fresh copy of the data before rebinding to the control on the ASP.NET page.
Save Changes: The option to save changes attempts to save the changes but will fail if a concurrency violation is encountered.
"Save anyway": "Save anyway" option takes the values the user attempted to save and uses the last-in wins technique, overwriting whatever is in the database. It does this by calling a different command object associated with a stored procedure that only uses the primary key field (EmployeeID) in the WHERE clause of the UPDATE statement. This technique should be used with caution as it will overwrite the record. If you want a more automatic way of dealing with the changes, you could get a fresh copy from the database. Then overwrite just the fields that the current user modified. Use this with caution as well, however, because if the same field was modified by both users, you may want to just back out or ask the user what to do next.
What is obvious here is that there are several ways to deal with concurrency violations, each of which must be carefully weighed before you decide on the one you will use in your application.
Any solution to concurrency problem should make sure that the data at the time of update is the same as it was when you fetched it. Here are some common solutions that you can implement:
- Update using ALL the fields in WHERE clause: In this approach you include all the fields from SELECT statement in the WHERE condition of UPDATE statement. This can be achieved in two ways either configuring the DataAdapter manually or using CommandBuilder.
- Updating using SOME fields in WHERE clause: In this approach only few fields from SELECT statement are included in WHERE condition of UPDATE statement.
- Updating based on a TIMESTAMP column: In this approach you add a timestamp field to your table and compare its values before updating the row.