《计算机-外文翻译-外文文献-英文文献-统一建模语言-Visual-Studio-.NET如何为并发控制生成SQL语句.doc》由会员分享，可在线阅读，更多相关《计算机-外文翻译-外文文献-英文文献-统一建模语言-Visual-Studio-.NET如何为并发控制生成SQL语句.doc（7页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、本科科设计（论文）外文翻译原文：How Visual Studio .NET Generates SQL Statements for Concurrency ControlAuthor: Steve SteinVisual Studio TeamAbstract: 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
2、generate a statement that does not use concurrency control. (5 printed pages).IntroductionAny application that might have multiple users simultaneously attempting to access and modify data needs some form of concurrency control. Otherwise, one users changes could inadvertently overwrite modification
3、s 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 S
4、QL statement for better performance. PrerequisitesYou 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 .N
5、ET. 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 comman
6、d 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 e
7、xisting 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
8、 and Data AdaptersWhen 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 g
9、enerated 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 d
10、ata 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.
11、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 ac
12、cess 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 desir
13、ed 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 p
14、erform 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 WizardTo understand how Visual Studio .NET constructs SQL statements that use optimistic concurrency, let us inspect the Update statement generated by the Da
15、ta 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
16、 or not are located in the Where clause.NoteThe 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 ConcurrencyThis exam
17、ple uses the default settings of the Data Adapter Configuration Wizard, which has the Use optimistic concurrency option selected.NoteWhen 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
18、 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
19、= 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 val
20、ue, the statement also verifies the original record contained a null value.UPDATE CustomersSET CustomerID = CustomerID, CompanyName = CompanyName, ContactName = ContactName, ContactTitle = ContactTitle, City = CityWHERE (CustomerID = Original_CustomerID) AND (City = Original_City OR Original_City IS
21、 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, Conta
22、ctName, ContactTitle, City FROM Customers WHERE (CustomerID = CustomerID)Update Statement Without Optimistic ConcurrencyThis example modifies the advanced settings of the Data Adapter Configuration Wizard and clears the Use optimistic concurrency option.Examining the following statement reveals that
23、 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.
24、 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 CustomersSET CustomerID = CustomerID, CompanyName = CompanyName, ContactName = ContactName, ContactTitle = ContactTitle, City = CityWHERE (Cust
25、omerID = Original_CustomerID);SELECT CustomerID, CompanyName, ContactName, ContactTitle, City FROM Customers WHERE (CustomerID = CustomerID)Optimizing the Generated SQL StatementVisual Studio .NET generates SQL statements that use the check all values approach to optimistic concurrency. Although thi
26、s 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 ha
27、ve 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 applications record agai
28、nst the timestamp in the data source to determine if a concurrent user has changed the record.NoteThis example presumes the timestamp has been generated in the database.UPDATE Customers SET CustomerID = CustomerID, CompanyName = CompanyName,ContactName = ContactName, ContactTitle = ContactTitle, Cit
29、y = CityWHERE (CustomerID = Original_CustomerID) AND (TimeStamp = Original_TimeStamp);SELECT CustomerID, CompanyName, ContactName, ContactTitle, City, TimeStamp FROM Customers WHERE (CustomerID = CustomerID)Concurrency and Command-Builder ObjectsIf your application uses SqlCommandBuilder or OleDbCom
30、mandBuilder, 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 adapters Update and Delete commands. For more information,
31、see OleDbCommand.CommandText property or SqlCommand.CommandText property.ConclusionThe 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
32、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 Cor
33、poration. All rights reserved.译文：Visual Studio .NET如何为并发控制生成SQL语句作者：史蒂夫斯坦的Visual Studio团队 时间：2002年2月摘要：这篇文章研究Visual Studio .NET为不同的并发控制方式所产生的SQL语句，如何对它们进行修改可以提高执行效率，以及如何生成不带并发控制的SQL语句。引言任何可能同时被多个用户访问或修改数据的应用程序，都需要进行并发控制。否则，一个用户更改记录时可能不经意的覆盖了其他用户的更改。Visual Studio .NET的设计工具可以生成“保持所有值”方式的开放式并发SQL语句或生成“
34、最后的更新生效”方式的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 i
35、n ADO.NET）。自动生成的SQL语句在哪里自动生成的SQL语句在command对象的CommandText属性里。在设计阶段配置DataAdapter对象时或使用CommandBuilder对象时SQL命令被自动生成。更多信息，请参见 并发与CommandBuilder对象（Concurrency and Command Builder Objects）。配置DataAdapter对象l 从工具箱的数据选项卡中拖一个DataAdapter对象l 从服务器资源管理器拖一个数据表l 选中已有的DataAdapter对象，然后单击在属性窗口底部的“配置数据适配器”链接CommandBuilde
36、r对象l CommandBuilder对象在运行时刻被创建，更多信息请参阅 SqlCommandBuilder 或 OleDbCommandBuilder。并发控制与数据适配器(DataAdapter)使用“数据适配器配置向导”配置数据适配器时，你可以选择是否使用开放式并发来生成Update和Delete语句。一些思考和注意事项l 你的数据源必须有一个主键才能以开放式并发方式生成SQL语句l 当使用从“服务器资源管理器”拖放一个数据表的方式来创建DataAdapter对象时，DataAdapter对象自动生成基于开放式并发的Update和Delete语句。如果你不想使用开放式并发，右击Data
37、Adapter对象，从快捷菜单中选择“配置数据适配器”，然后在“高级SQL生成选项”对话框中清除“使用开放式并发”选项的选定。向导则会重新创建不带并发检测的SQL语句。l 当重新配置现有的DataAdapter时，应注意“高级SQL生成选项”对话框里的选项已经全部恢复默认。例如最初配置DataAdapter时没有选定“使用开放式并发”选项，但是当重新配置DataAdapter时，“使用开放式并发”选项却会被选定，即便你根本没有打开过“高级SQL生成选项”对话框。l 如果你在“数据适配器配置向导”的“选择查询类型”页面选择“使用现有的存储过程”，则“使用开放式并发”选项将不可用。存储过程仍按其原
38、来的方式执行。如果想使用并发检测的话，必须将其包括到存储过程中、或在你的应用程序中编写相应的代码。l 当使用开放式并发来创建SQL命令时，不会对二进制数据列验证进行并发处理。这将导致用这种方法对大的二进制记录集执行按位比较算法时的效率低下。用向导生成SQL语句为了理解Visual Studio .NET如何使用开放式并发来生成SQL语句，让我们来看看用“数据适配器配置向导”生成的Update语句。我们将查看同一条语句在选择“使用开放式并发”选项和不选择“使用开放式并发”选项时的不同状态。你会注意到，选择开放式并发与不选择开放式并发所生成SQL语句的区别只存在于Where子句上。注：以下的例子使
39、用用“数据适配器配置向导”生成的Update语句，并从NorthWind示例数据库的Customers表中选择了若干列。使用开放式并发的Update语句这个例子使用了“数据适配器配置向导”的默认配置，即选中了“使用开放式并发”选项。注： 当使用开放式并发时，生成的command的参数集里还存在一个参数副本。第二个参数集（带Original_前缀的那个）保存了最初从数据源里读取的值。检查Where子句发现，每一个生成的语句都要检测数据库当前的值是否等于最初读取的值（例，WHERE City = Original_City）。通过数据库中的每个字段与最初读取的值相比较，我们很容易确定是否同时有其他
40、用户修改了某个字段。如果Where子句不成立，就没有记录会被修改，与此同时还引发了一个“数据库并发”异常。如果数据源的某个字段为空值（NULL），生成的SQL语句同样验证最初读取的记录是否也为空值。UPDATE CustomersSET CustomerID = CustomerID, CompanyName = CompanyName, ContactName = ContactName, ContactTitle = ContactTitle, City = CityWHERE (CustomerID = Original_CustomerID) AND (City = Original_
41、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 Custom
42、erID, CompanyName, ContactName, ContactTitle, City FROM Customers WHERE (CustomerID = CustomerID)不使用开放式并发的Update语句这个例子更改了“数据适配器配置向导”的高级选项，没有选中“使用开放式并发”选项。以下的语句表明：只要数据库中一条记录满足CustomerID = Original_CustomerID ，则所有的字段都会被更新。不管这条记录现在是什么样的值，它都将被设置为通过SQL语句传递到数据源的值。在这里没有任何关于并发的检测，也无法得知是否同时有其它用户在更改这条记录。 这种方式
43、称为“最后的更新生效”方式。无论以前对这条记录进行过什么样的修改，更新操作都会执行。UPDATE CustomersSET CustomerID = CustomerID, CompanyName = CompanyName, ContactName = ContactName, ContactTitle = ContactTitle, City = CityWHERE (CustomerID = Original_CustomerID);SELECT CustomerID, CompanyName, ContactName, ContactTitle, City FROM Customers
44、 WHERE (CustomerID = CustomerID)优化生成的SQL语句Visual Studio .NET生成“保持所有值”方式的SQL语句来实现开放式并发。虽然这可能没有生成最高效的SQL语句，但是它的却生成了可以对数据源所有列（包括主键）进行并发检测的SQL语句。使用“保持所有值”方式实现开放式并发，当执行效率非常低下时，你可以手工修改生成的SQL语句以使它们不检查数据源的所有列。最常见的方式是使用时间戳或版本号字段。如果你的数据源包含一个每次修改记录时都会更新的时间戳字段，你只需要验证数据源中的时间戳和你程序中的时间戳二者是否匹配，就可以知道是否同时有其他用户修改了记录。下
45、面这条SQL语句使用检查时间戳模式。注：这个例子假设数据库已经设置了时间戳字段UPDATE Customers SET CustomerID = CustomerID, CompanyName = CompanyName,ContactName = ContactName, ContactTitle = ContactTitle, City = CityWHERE (CustomerID = Original_CustomerID) AND (TimeStamp = Original_TimeStamp);SELECT CustomerID, CompanyName, ContactName,
46、 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语句来获取更高效的执行。