How Visual Studio .NET Generates SQL Statements for Concurrency Control
Visual Studio Team
Summary: This paper examines the SQL statements Visual Studio® .NET generates for different kinds of concurrency control, how to modify them for better performance, and how to generate a statement that does not use concurrency control. (5 printed pages)
Any application that might have multiple users simultaneously attempting to access and modify data needs some form of concurrency control. Otherwise, one user's changes could inadvertently overwrite modifications from other users. The design tools in Visual Studio .NET can create SQL statements that use the "check all values" approach to optimistic concurrency or the "last-in wins" approach to updating data. This paper will explain:
- How each of these statement types are generated.
- How to modify the generated SQL statement for better performance.
You should have an understanding of:
- Fundamental ADO.NET data concepts, including datasets and data adapters. For more information, see Introduction to Data Access with ADO.NET.
- Concurrency control basics and the options available in Visual Studio .NET. For more information, see Introduction to Data Concurrency in ADO.NET.
SQL statements are located in the CommandText property of command objects. SQL commands are automatically generated at design time when configuring data adapters, and at run time when using command builder objects. For more information, see Concurrency and Command Builder Objects.
Configuring Data Adapters
- Drag a data adapter from the Data tab of the Toolbox
- Drag a table from Server Explorer
- Modifying an existing adapter, by selecting a data adapter and clicking the Configure Data Adapter link at the bottom of the Properties window.
Command Builder objects
- Command builder objects are created programmatically at run time. For more information, see (SqlCommandBuilder or OleDbCommandBuilder)
When configuring data adapters with the Data Adapter Configuration Wizard, you can decide whether to use optimistic concurrency for the generated Update and Delete statements.
Considerations and Caveats
- Your data source must have a primary key in order for the SQL statements to be generated to use optimistic concurrency.
- When creating data adapters by dragging tables from Server Explorer, the data adapter creates Update and Delete statements that are automatically configured for optimistic concurrency. If you do not want to use optimistic concurrency, you can reconfigure the data adapter: Right-click the adapter and select Configure Data Adapter from the shortcut menu, then clear the Use optimistic concurrency option of the Advanced SQL Generation Options Dialog Box. The wizard will recreate the statements without the additional code to check for concurrency violations.
- When reconfiguring an existing data adapter, note that the advanced settings all revert to their default state. For example, if you cleared the Use optimistic concurrency option when the adapter was originally configured, it will automatically be selected if you reconfigure it, even if you do not access the Advanced SQL Generation Options dialog box.
- If you select the Use existing stored procedures option in the Choose a Query Type section of the Data Adapter Configuration Wizard, the option to use optimistic concurrency is not available. The stored procedures will execute as is, and any desired concurrency checking must be done within the stored procedure, or programmatically built into your application.
- When commands are generated to use optimistic concurrency, no verification will be performed on binary columns to determine whether concurrent changes have been made. The resources to perform a bit-by-bit comparison of a large binary record would be extremely inefficient.
SQL Statements Generated by the Wizard
To understand how Visual Studio .NET constructs SQL statements that use optimistic concurrency, let us inspect the Update statement generated by the Data Adapter Configuration Wizard. We will look at the same statement generated both with and without the Use optimistic concurrency option selected in the Advanced SQL Generation Options dialog box of the wizard.
You will notice the differences between statements that either use optimistic concurrency or not are located in the Where clause.
Note The following examples use the Update command that is generated by running the Data Adapter Configuration Wizard, and selecting several columns from the Customers table in the Northwind sample database.
Update Statement Using Optimistic Concurrency
This example uses the default settings of the Data Adapter Configuration Wizard, which has the Use optimistic concurrency option selected.
Note When using optimistic concurrency, the commands are generated with a second set of parameters. This second set of parameters (the ones with the
@Original_prefix) store the values that are initially read from the data source.
Examining the Where clause in the following statement reveals that all fields are inspected to make sure the current value for each field in the database is equal to the value that was originally read into the dataset (for example,
WHERE City = @Original_City). By comparing each field in the database with the original value, it is easy to determine if a concurrent user has modified a field. If the Where clause is not satisfied, no records are updated and a DBConcurrencyException is raised. If a field in the data source contains a null value, the statement also verifies the original record contained a null value.
UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle, City = @City WHERE (CustomerID = @Original_CustomerID) AND (City = @Original_City OR @Original_City IS NULL AND City IS NULL) AND (CompanyName = @Original_CompanyName) AND (ContactName = @Original_ContactName OR @Original_ContactName IS NULL AND ContactName IS NULL) AND (ContactTitle = @Original_ContactTitle OR @Original_ContactTitle IS NULL AND ContactTitle IS NULL); SELECT CustomerID, CompanyName, ContactName, ContactTitle, City FROM Customers WHERE (CustomerID = @CustomerID)
Update Statement Without Optimistic Concurrency
This example modifies the advanced settings of the Data Adapter Configuration Wizard and clears the Use optimistic concurrency option.
Examining the following statement reveals that all fields will be updated as long as a record exists in the database where
CustomerID = @Original_CustomerID. No matter what values exist in this record, they will all be set to the values passed through this statement. There is no verification to check if a concurrent user has modified the record. This is called the "last-in wins" approach, because no matter what modifications have been performed on the record, the update will still be performed.
UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle, City = @City WHERE (CustomerID = @Original_CustomerID); SELECT CustomerID, CompanyName, ContactName, ContactTitle, City FROM Customers WHERE (CustomerID = @CustomerID)
Visual Studio .NET generates SQL statements that use the "check all values" approach to optimistic concurrency. Although this may not generate the most efficient statement, it does create a statement that can check for concurrency violations on any data source containing a primary key.
If the "check all values" approach to optimistic concurrency proves inefficient, you can modify the generated command text so it does not have to check every original value against the values in the data source. The most common way to accomplish this is with a timestamp or version field. If your data contains a timestamp field that is updated every time the data changes, you need only check the timestamp in your application's record against the timestamp in the data source to determine if a concurrent user has changed the record.
The following SQL statement has been modified to check the timestamp.
Note This example presumes the timestamp has been generated in the database.
UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName, ContactName = @ContactName, ContactTitle = @ContactTitle, City = @City WHERE (CustomerID = @Original_CustomerID) AND (TimeStamp = @Original_TimeStamp); SELECT CustomerID, CompanyName, ContactName, ContactTitle, City, TimeStamp FROM Customers WHERE (CustomerID = @CustomerID)
If your application uses SqlCommandBuilder or OleDbCommandBuilder, the command text of the Update and Delete statements is automatically configured for optimistic concurrency. If you do not want to use optimistic concurrency, you can programmatically modify the CommandText property of the data adapter's Update and Delete commands. For more information, see OleDbCommand.CommandText property or SqlCommand.CommandText property.
The SQL statements that are automatically generated by the design tools in Visual Studio .NET or by command builder objects use the "check all values" method of optimistic concurrency. Although this may not be the most efficient approach for all situations, it generates a concurrency-checking statement on any data source that contains a primary key. If your data uses version numbers or timestamps, you can modify the generated SQL statements for better performance.