计算机-外文翻译-外文文献-英文文献-统一建模语言-Visual-Studio-.NET如何为并发控制生成SQL语句.doc

本科科设计（论文）外文翻译
原文：
How Visual Studio .NET Generates SQL Statements for Concurrency Control
Author: Steve SteinVisual Studio Team
Abstract: 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).
Introduction
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.
Prerequisites
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.
Where Are the SQL Statements?
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 Objets .before us have addressed overlay network programming issues. Even early overlay network 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)
Concurrency and Data Adapters
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.can be used as a key to 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)
Optimizing the Generated SQL Statement
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.
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)
Concurrency and Command-Builder Objects
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.
Conclusion
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.
Send feedback on this topic Microsoft
© Microsoft Corporation. All rights reserved.
译文：
Visual Studio .NET如何为并发控制生成SQL语句
作者：史蒂夫斯坦的Visual Studio团队 时间：2002年2月
摘要：这篇文章研究Visual Studio® .NET为不同的并发控制方式所产生的SQL语句，
如何对它们进行修改可以提高执行效率，以及如何生成不带并发控制的SQL语句。
引言
任何可能同时被多个用户访问或修改数据的应用程序，都需要进行并发控制。否则，
一个用户更改记录时可能不经意的覆盖了其他用户的更改。Visual Studio .NET的设计工具可以生成“保持所有值”方式的开放式并发SQL语句或生成“最后的更新生效”方式的SQL语句来更新数据。这篇文章将解释：
l 不同的SQL语句是如何生成的
l 如何修改自动生成的SQL语句可以提高执行效率
阅读此文章时应具备的一些知识
你需要具备以下知识：
l 基本的ADO.NET概念，包括数据集(DataSet)以及数据适配器(DataAdapters)。更多信息请参见ADO.NET 数据访问介绍（Introduction to Data Access with ADO.NET）。
l 数据并发机制以及会操作Visual Studio .NET。更多内容请参见介绍 ADO.NET 中的数据并发(Introduction to Data Concurrency in ADO.NET）。
自动生成的SQL语句在哪里
自动生成的SQL语句在command对象的CommandText属性里。在设计阶段配置
DataAdapter对象时或使用CommandBuilder对象时SQL命令被自动生成。更多信息，请参见 并发与CommandBuilder对象（Concurrency and Command Builder Objects）。
配置DataAdapter对象
l 从工具箱的数据选项卡中拖一个DataAdapter对象
l 从服务器资源管理器拖一个数据表
l 选中已有的DataAdapter对象，然后单击在属性窗口底部的“配置数据适配器”链接
CommandBuilder对象
l CommandBuilder对象在运行时刻被创建，更多信息请参阅 SqlCommandBuilder 或 OleDbCommandBuilder。
并发控制与数据适配器(DataAdapter)
使用“数据适配器配置向导”配置数据适配器时，你可以选择是否使用开放式并发来生
成Update和Delete语句。
一些思考和注意事项
l 你的数据源必须有一个主键才能以开放式并发方式生成SQL语句
l 当使用从“服务器资源管理器”拖放一个数据表的方式来创建DataAdapter对象时，DataAdapter对象自动生成基于开放式并发的Update和Delete语句。如果你不想使用开放式并发，右击DataAdapter对象，从快捷菜单中选择“配置数据适配器”，然后在“高级SQL生成选项”对话框中清除“使用开放式并发”选项的选定。向导则会重新创建不带并发检测的SQL语句。
l 当重新配置现有的DataAdapter时，应注意“高级SQL生成选项”对话框里的选项已经全部恢复默认。例如最初配置DataAdapter时没有选定“使用开放式并发”选项，但是当重新配置DataAdapter时，“使用开放式并发”选项却会被选定，即便你根本没有打开过“高级SQL生成选项”对话框。
l 如果你在“数据适配器配置向导”的“选择查询类型”页面选择“使用现有的存储过程”，则“使用开放式并发”选项将不可用。存储过程仍按其原来的方式执行。如果想使用并发检测的话，必须将其包括到存储过程中、或在你的应用程序中编写相应的代码。
l 当使用开放式并发来创建SQL命令时，不会对二进制数据列验证进行并发处理。这将导致用这种方法对大的二进制记录集执行按位比较算法时的效率低下。
用向导生成SQL语句
为了理解Visual Studio .NET如何使用开放式并发来生成SQL语句，让我们来看看用“数
据适配器配置向导”生成的Update语句。我们将查看同一条语句在选择“使用开放式并发”选项和不选择“使用开放式并发”选项时的不同状态。
你会注意到，选择开放式并发与不选择开放式并发所生成SQL语句的区别只存在于Where子句上。注：以下的例子使用用“数据适配器配置向导”生成的Update语句，并从NorthWind示例数据库的Customers表中选择了若干列。
使用开放式并发的Update语句
这个例子使用了“数据适配器配置向导”的默认配置，即选中了“使用开放式并发”选项。
注： 当使用开放式并发时，生成的command的参数集里还存在一个参数副本。第二个参数集（带@Original_前缀的那个）保存了最初从数据源里读取的值。
检查Where子句发现，每一个生成的语句都要检测数据库当前的值是否等于最初读取的值（例，WHERE City = @Original_City）。通过数据库中的每个字段与最初读取的值相比较，我们很容易确定是否同时有其他用户修改了某个字段。如果Where子句不成立，就没有记录会被修改，与此同时还引发了一个“数据库并发”异常。如果数据源的某个字段为空值（NULL），生成的SQL语句同样验证最初读取的记录是否也为空值。
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语句
这个例子更改了“数据适配器配置向导”的高级选项，没有选中“使用开放式并发”选项。
以下的语句表明：只要数据库中一条记录满足CustomerID = @Original_CustomerID ，则所有的字段都会被更新。不管这条记录现在是什么样的值，它都将被设置为通过SQL语句传递到数据源的值。在这里没有任何关于并发的检测，也无法得知是否同时有其它用户在更改这条记录。 这种方式称为“最后的更新生效”方式。无论以前对这条记录进行过什么样的修改，更新操作都会执行。
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)
优化生成的SQL语句
Visual Studio .NET生成“保持所有值”方式的SQL语句来实现开放式并发。虽然这可能没
有生成最高效的SQL语句，但是它的却生成了可以对数据源所有列（包括主键）进行并发检测的SQL语句。使用“保持所有值”方式实现开放式并发，当执行效率非常低下时，你可以手工修改生成的SQL语句以使它们不检查数据源的所有列。最常见的方式是使用时间戳或版本号字段。如果你的数据源包含一个每次修改记录时都会更新的时间戳字段，你只需要验证数据源中的时间戳和你程序中的时间戳二者是否匹配，就可以知道是否同时有其他用户修改了记录。
下面这条SQL语句使用检查时间戳模式。
注：  这个例子假设数据库已经设置了时间戳字段
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)
并发与CommandBuilder对象
当应用程序使用SqlCommandBuilder或者OleDbCommandBuilder时，生成的Update
和Delete语句的CommandText属性被自动的以开放式并发方式创建。如果你不想用开放式并发，则可以通过修改DataAdapter对象的Update和Delete命令的CommandText属性来实现。更多内容，请参见OleDbCommand.CommandText属性 或 SqlCommand.CommandText属性。
结论
当使用开放式并发的“保存所有值”方法时，SQL语句在设计时由Visual Studio .NET的设计工具自动生成或在运行时由CommandBuilde自动生成。它把数据库所有字段（包括主键）当前值与初始值进行比较，这可能不是一种最高效的方式。如果你的数据使用版本号或者时间戳方式控制并发，则可以通过修改生成的SQL语句来获取更高效的执行。