.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal

Home >> Articles >> .Net Framework >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Managing Concurrency

Posted By:Shashi Ray       Posted Date: November 27, 2009    Points: 25    Category: .Net Framework    URL: http://www.dotnetspark.com  


Managing Concurrency

The DataSet object is designed to encourage the use of optimistic concurrency for long-running activities, such as when you are remoting data and when users are interacting with data. When submitting updates from a DataSet to the database server, there are four main approaches to managing optimistic concurrency:

  • Including only the primary key columns
  • Including all columns in the WHERE clause
  • Including unique key columns and the timestamp columns
  • Including unique key columns and the modified columns

Note that the last three approaches maintain data integrity; the first does not.

Including Only the Primary Key Columns

This option creates a situation where the last update overrides all previous changes. The CommandBuilder does not support this option, but the Data Adapter Configuration Wizard does. To use it, go to the Advanced Options tab and clear the Use Concurrency check box.

This approach is not a recommended practice because it allows users to unknowingly overwrite other users' changes. It is never advisable to compromise the integrity of another user's update. (This technique is appropriate only for single-user databases.)

Including All Columns in the WHERE Clause

This option prevents you from overwriting changes made by other users between the time your code fetches the row and the time your code submits the pending change in the row. This option is the default behavior of both the Data Adapter Configuration Wizard and the SQL code generated by the SqlCommandBuilder.

This approach is not a recommended practice for the following reasons:

  • If an additional column is added to the table, the query will need to be modified.
  • In general, databases do not let you compare two BLOB values because their large sizes make these comparisons inefficient. (Tools such as the CommandBuilder and the Data Adapter Configuration Wizard should not include BLOB columns in the WHERE clause.)
  • Comparing all columns within a table to all the columns in an updated row can create excessive overhead.

Including Unique Key Columns and the Timestamp Columns

With this option, the database updates the timestamp column to a unique value after each update of a row. (You must provide a timestamp column in your table.) Currently, neither the CommandBuilder nor the Data Adapter Configuration Wizard supports this option.

Including Unique Key Columns and the Modified Columns

In general, this option is not recommended because errors may result if your application logic relies on out-of-date data fields or even fields that it does not update. For example, if user A changes an order quantity and user B changes the unit price, it may be possible for the order total (quantity multiplied by price) to be incorrectly calculated.

Correctly Updating Null Fields

When fields in a database do not contain data values, it is often convenient to think of these empty fields as containing a special null value. However, this mental picture can be the source of programming errors because database standards require special handling for null values.

The core issue with null fields is that the ordinary SQL = operator will always return false if one or both of the operands is a null value. The operator IS NULL is the only correct way to check for the presence of a null field in a SQL query.

If your application uses the technique described above to manage concurrency by specifying a WHERE clause, you must include explicit IS NULL expressions wherever it is possible that a field could be null. For example, the following query will always fail if OldLastName is null:

SET LastName = @NewLastName WHERE StudentID = @StudentID AND  LastName = @OldLastName

The query should be rewritten as:

SET LastName = @NewLastName WHERE (StudentID = @StudentID) AND
                                  ((LastName = @OldLastName) OR
                                   (OldLastName IS NULL AND LastName IS NULL))

A good way to understand how to write the kind of update logic shown above is to read the output generated by the CommandBuilder tool.



Shashi Ray

 Subscribe to Articles


Further Readings:


No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend