Dealing with Record Contention by Limiting Changes
There's code you can use to keep record changes from getting overwritten by ASP.NET DataSources.
You can avoid the problems that ASP.NET DataSources create when two people access the same record by writing a little code.
OK, a lot of code.
Imagine that Sue and Lou retrieve a Customer record at the same time. Sue changes the LastName field and saves her record. Sadly, her change is then wiped out by Lou who changes the CustomerCredit field before saving his record. Unfortunately, Sue's change to LastName is now lost because the ASP.NET DataSource, when it saves Lou's record, updates all the other fields in the record with the data that Lou retrieved before Sue's update. This is bad.
It's easy to avoid this problem: If Lou had only updated the CustomerCredit field he changed, then he wouldn't have overwritten the LastName change made by Sue. You can make this happen by writing your own update code.
But before moving forward (and in the interests of full disclosure), there's one scenario where updating only changed fields won't work.
Bad Data Design
Imagine that you have a record with two fields that must be consistent with each other but that, somehow, it's possible for a user to change one field but not the other. For instance, a person's salutation ("Mr.", "Miss", "Mrs.") and last name are two related fields. If Miss Jan Irvine gets married, you'll need to change both her salutation and last name. However, even though the two fields are related, it's also possible to imagine a scenario where you change only one of the fields (for instance, Jan changes her salutation to "Dr.").
In the two-related-fields scenario, Sue could update the first field with a value consistent with the original value in the second field; at the same time, Lou could change the second field with a value that's consistent with the original value in the first field. However, the two new values could still be inconsistent with each other and, when Lou and Sue save their versions of the record, you end up with bad data.
This is a very unusual scenario; I can't, for instance, imagine how simultaneous updates to each of the LastName and Salutation fields could create a problem. The reason that this scenario is unusual is because it violates third normal form (this is the rule that says all fields should be dependent only on the record's key field and not on each other).
Believe me, changing your database design is better for everyone than trying to prevent two people from accessing the record at the same time.
Changing Only What's Changed
So, ignoring that scenario, how do you handle updating only changed fields? First, keep your data in a DataSet because a DataSet will keep track of the state of your records. Second, recognize that data contention isn't a problem for deletes and inserts, and use the DataSet's Update method for them. This code does the job for deleted records:
Dim ds As MyDataSet
If MyDataSet.HasChanges(DataRowState.Deleted) Then
ds = MyDataSet.GetChanges(DataRowState.Deleted)
For updates, you're going to have to write your own code to generate your SQL statements. The DataSet automatically tracks the current and original values for any field so you can compare the current and original values to determine which fields have been changed.
You can probably imagine the code that will build the SQL statement so I'll omit that. (The code has lots of twiddly bits to handle delimiters and Nulls and data types. E-mail me and I'll send it to you.) The key part of the code is this function that, passed a row from a table and a field number, checks to see if the current version of the data in each field is different from the original data pulled from the database (see Listing 1).
As you can see, much of the code in this function deals with Nulls. That's a key point for my next column, where I'll look at an alternative solution built into the DataSource: Optimistic Concurrency (which isn't locking at all).
Peter Vogel is a system architect and principal in PH&V Information Services. PH&V provides full-stack consulting from UX design through object modeling to database design. Peter tweets about his VSM columns with the hashtag #vogelarticles. His blog posts on user experience design can be found at http://blog.learningtree.com/tag/ui/.