ASP.NET, SQL Server, WPF, C#, VB.NET

One topic that keeps coming up in our forums and in newsgroups could be phrased as “how can I prevent users from updating data that another user is editing”. Usually this revolves (incorrectly) around the idea that you should have some way to “lock” a table or tables while a user is editing same. However, the “disconnected data” paradigm of ADO.NET makes this approach both unrealistic and in many cases, unfeasible.

For example, in an ASP.NET web application, how do you handle concurrency so that you don’t accidentally overwrite another user's changes? How do you handle null values in your concurrency checks?

There are a number of ways to handle this, but the most elegant one, in my opinion, is to take advantage of a built in datatype in SQL Server – the Timestamp or "rowversion" type. (NOTE: Do not confuse this native SQL Server DataType with the DataRowView.RowVersion ADO.NET property.)

You can use the timestamp (rowversion) column of a row to easily determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read. To return the current timestamp value for a database, use @@DBTS.

You can simplify the WHERE clause of your query-based updates by relying on timestamp columns. The SQL Server timestamp column does not actually contain date and time information; it contains binary data that is unique within the database. By simply including the rowversion column value in your SELECT queries, you have all the ammunition you need to handle concurrency updates correctly and accurately.

You can define a rowversion  column on your SQL Server table, and any time the contents of a row changes, SQL Server will modify the value of the rowversion column for that row. We can add a rowversion column to the Northwind Customers table and change our typical update query to look like this:

UPDATE Customers

    SET CustomerID = 'ABCDE', CompanyName = 'OriginalCompanyName', 

        ContactName = 'NewContactName', Phone = '916-559-1219'

    WHERE CustomerID = 'ABCDE' AND 

          RowversionColumn = 0x00000000000000FF

Since the server will generate a new value for the rowversion column each time it updates a row, you can use a combination of the primary key and rowversion columns in the WHERE clause of your query-based updates to ensure that you don’t overwrite another user’s changes. You can examine the return value of an ExecuteNonQuery statement to see if a row was actually updated, and inform the user appropriately. Most database systems support a similar data type. Some use a unique binary value, and others use a date/time value.

Working with Null Values

Example: The Customers table in the Northwind database has a Region column that accepts strings of up to 15 characters and also accepts Null values. A number of rows in the Region column have a Null value. Many developers will try to use a query such as the following to retrieve those rows:

SELECT CustomerID, CompanyName, ContactName

    FROM Customers WHERE Region = NULL

If you use this query in ADO.NET or run this query in SQL Query Analyzer, you’ll find that it returns zero rows.
Null values are a special case in the database world, especially when it comes to comparing Null values in a query. According to ANSI standards, you can’t compare Null values using the = operator. Instead, you must use IS NULL in your query. The following query returns the rows in the Customers table that have Null values for the Region column:

SELECT CustomerID, CompanyName, ContactName

    FROM Customers WHERE Region IS NULL

How does NULL relate to  submitting changes to your database using a DataAdapter? Take a look at this SQL statement for a  query to submit modified rows in the Order Details table:

UPDATE [Order Details]

    SET OrderID = @orderid, ProductID = @productid, Quantity = @quantity, UnitPrice = @unitprice

    WHERE OrderID = @orderid AND ProductID = @productid AND 

          Quantity = @quantity AND UnitPrice = @unitprice

None of the columns referenced in this query accepts Null values. Because of this, the WHERE clause for the query is relatively simple. But what if the Quantity and UnitPrice columns were to allow Null values? Say you have a row that currently has a Null value in the Quantity column and you want to change that value to 40. If we replace the parameters with actual values, we get a query that looks like this:

UPDATE [Order Details]

    SET OrderID = 12398, ProductID = 2, Quantity = 40, UnitPrice = 32

    WHERE OrderID = 12398 AND ProductID = 2 AND

          Quantity = Null AND UnitPrice = 32

In this scenario, the query will modify zero rows because of the Quantity = Null portion of the WHERE clause. The Quantity column for the desired row in the database is Null, but Null = Null evaluates to false, so the database does not modify the row.

So how do we change the WHERE clause of the query to accommodate Null values in our concurrency check? If a particular column accepts Null values, we can replace the following portion of a query

ColumnName = @parameter

with

(ColumnName = @parameter OR ((ColumnName IS NULL) AND (@parameter IS NULL)))

We want the clause to evaluate to true if the column and the parameter equate to the same non-Null value or if both the column and the parameter are Null.

Let’s say your DataAdapter will query the Customers table for the CustomerID, CompanyName, ContactName, and Phone columns. Neither the CustomerID nor CompanyName columns accepts Null, but the ContactName and Phone columns do. As a result, you must perform Null checks in the WHERE clauses of your query-based updates. If you build your updating logic using the Data Adapter Configuration Wizard, you’ll find that the wizard generates the generic equivalent of the following query to submit modified rows, complete with the appropriate Null checks:

UPDATE Customers

    SET CustomerID = @customerid, CompanyName = @companyname, ContactName = @contactname, Phone = @phone

    WHERE (CustomerID = @customerid) AND (CompanyName = @companyname) 

AND

          (ContactName = @contactname OR ((@contactname IS NULL) AND 

(ContactName IS NULL)))

          AND (Phone = @phone OR ((@phone IS NULL) AND (Phone IS NULL)))

ADO.NET gives you a number of options for submitting changes. You can generate updating logic at run time using CommandBuilder objects. You can supply your own updating logic in code, submitting changes via INSERT, UPDATE, or DELETE queries or preferably via stored procedure calls. You can also use the Data Adapter Configuration Wizard to generate such code  at design time.

The best method to use depends on the parameters of your application. You can get the best performance by configuring your DataAdapter objects to submit updates via stored procedure calls. However, if your application must work with databases such as Microsoft Access that don’t support stored procedures, that solution isn’t appropriate. You’d be better off using INSERT, UPDATE, and DELETE queries. You need to consider such factors when deciding what is appropriate for your application.

Generally I recommend not only submitting changes but performing all database operations via stored procedures whenever possible.