1、<p><b>　　英文翻譯</b></p><p>　　Visual Studio .NET如何為并發(fā)控制生成SQL語(yǔ)句</p><p>　　原作者：Steve SteinVisual Studio TeamMicrosoft Corporation</p><p><b>　　2002年2月</b><

2、;/p><p>　　摘要：這篇文章研究Visual Studio® .NET為不同的并發(fā)控制方式所產(chǎn)生的SQL語(yǔ)句，如何對(duì)它們進(jìn)行修改可以提高執(zhí)行效率，以及如何生成不帶并發(fā)控制的SQL語(yǔ)句。</p><p><b>　　目錄</b></p><p><b>　　引言</b></p><p>　

3、　閱讀此文章時(shí)應(yīng)具備的一些知識(shí)</p><p>　　生成的SQL語(yǔ)句在哪里</p><p>　　并發(fā)與數(shù)據(jù)適配器(DataAdapter)</p><p>　　優(yōu)化生成的SQL語(yǔ)句</p><p>　　并發(fā)與CommandBuilder對(duì)象</p><p><b>　　結(jié)論</b></p&g

4、t;<p><b>　　引言</b></p><p>　　任何可能同時(shí)被多個(gè)用戶訪問(wèn)或修改數(shù)據(jù)的應(yīng)用程序，都需要進(jìn)行并發(fā)控制。否則，一個(gè)用戶更改記錄時(shí)可能不經(jīng)意的覆蓋了其他用戶的更改。Visual Studio .NET的設(shè)計(jì)工具可以生成“保持所有值”方式的開(kāi)放式并發(fā)SQL語(yǔ)句或生成“最后的更新生效”方式的SQL語(yǔ)句來(lái)更新數(shù)據(jù)。這篇文章將解釋：</p><p

5、>　　不同的SQL語(yǔ)句是如何生成的</p><p>　　如何修改自動(dòng)生成的SQL語(yǔ)句可以提高執(zhí)行效率</p><p>　　閱讀此文章時(shí)應(yīng)具備的一些知識(shí)</p><p>　　你需要具備以下知識(shí)：</p><p>　　基本的ADO.NET概念，包括數(shù)據(jù)集(DataSet)以及數(shù)據(jù)適配器(DataAdapters)。更多信息請(qǐng)參見(jiàn)ADO.NE

6、T 數(shù)據(jù)訪問(wèn)介紹（Introduction to Data Access with ADO.NET）。</p><p>　　數(shù)據(jù)并發(fā)機(jī)制以及會(huì)操作Visual Studio .NET。更多內(nèi)容請(qǐng)參見(jiàn) 介紹 ADO.NET 中的數(shù)據(jù)并發(fā)(Introduction to Data Concurrency in ADO.NET）。</p><p>　　自動(dòng)生成的SQL語(yǔ)句在哪里</p

7、><p>　　自動(dòng)生成的SQL語(yǔ)句在command對(duì)象的CommandText屬性里。在設(shè)計(jì)階段配置DataAdapter對(duì)象時(shí)或使用CommandBuilder對(duì)象時(shí)SQL命令被自動(dòng)生成。更多信息，請(qǐng)參見(jiàn) 并發(fā)與CommandBuilder對(duì)象（Concurrency and Command Builder Objects）。</p><p>　　配置DataAdapter對(duì)象</p&

8、gt;<p>　　從工具箱的數(shù)據(jù)選項(xiàng)卡中拖一個(gè)DataAdapter對(duì)象</p><p>　　從服務(wù)器資源管理器拖一個(gè)數(shù)據(jù)表</p><p>　　選中已有的DataAdapter對(duì)象，然后單擊在屬性窗口底部的“配置數(shù)據(jù)適配器”鏈接</p><p>　　CommandBuilder對(duì)象</p><p>　　CommandBuild

9、er對(duì)象在運(yùn)行時(shí)刻被創(chuàng)建，更多信息請(qǐng)參閱 SqlCommandBuilder 或 OleDbCommandBuilder。</p><p>　　并發(fā)控制與數(shù)據(jù)適配器(DataAdapter)</p><p>　　使用“數(shù)據(jù)適配器配置向?qū)А迸渲脭?shù)據(jù)適配器時(shí)，你可以選擇是否使用開(kāi)放式并發(fā)來(lái)生成Update和Delete語(yǔ)句。</p><p><b>　　一些思

10、考和注意事項(xiàng)</b></p><p>　　你的數(shù)據(jù)源必須有一個(gè)主鍵才能以開(kāi)放式并發(fā)方式生成SQL語(yǔ)句</p><p>　　當(dāng)使用從“服務(wù)器資源管理器”拖放一個(gè)數(shù)據(jù)表的方式來(lái)創(chuàng)建DataAdapter對(duì)象時(shí)，DataAdapter對(duì)象自動(dòng)生成基于開(kāi)放式并發(fā)的Update和Delete語(yǔ)句。如果你不想使用開(kāi)放式并發(fā)，右擊DataAdapter對(duì)象，從快捷菜單中選擇“配置數(shù)據(jù)適配器”

11、，然后在“高級(jí)SQL生成選項(xiàng)”對(duì)話框中清除“使用開(kāi)放式并發(fā)”選項(xiàng)的選定。向?qū)t會(huì)重新創(chuàng)建不帶并發(fā)檢測(cè)的SQL語(yǔ)句。</p><p>　　當(dāng)重新配置現(xiàn)有的DataAdapter時(shí)，應(yīng)注意“高級(jí)SQL生成選項(xiàng)”對(duì)話框里的選項(xiàng)已經(jīng)全部恢復(fù)默認(rèn)。例如最初配置DataAdapter時(shí)沒(méi)有選定“使用開(kāi)放式并發(fā)”選項(xiàng)，但是當(dāng)重新配置DataAdapter時(shí)，“使用開(kāi)放式并發(fā)”選項(xiàng)卻會(huì)被選定，即便你根本沒(méi)有打開(kāi)過(guò)“高級(jí)SQL生成

12、選項(xiàng)”對(duì)話框。</p><p>　　如果你在“數(shù)據(jù)適配器配置向?qū)А钡摹斑x擇查詢類(lèi)型”頁(yè)面選擇“使用現(xiàn)有的存儲(chǔ)過(guò)程”，則“使用開(kāi)放式并發(fā)”選項(xiàng)將不可用。存儲(chǔ)過(guò)程仍按其原來(lái)的方式執(zhí)行。如果想使用并發(fā)檢測(cè)的話，必須將其包括到存儲(chǔ)過(guò)程中、或在你的應(yīng)用程序中編寫(xiě)相應(yīng)的代碼。</p><p>　　當(dāng)使用開(kāi)放式并發(fā)來(lái)創(chuàng)建SQL命令時(shí)，不會(huì)對(duì)二進(jìn)制數(shù)據(jù)列驗(yàn)證進(jìn)行并發(fā)處理。這將導(dǎo)致用這種方法對(duì)大的二進(jìn)制記錄

13、集執(zhí)行按位比較算法時(shí)的效率低下。</p><p>　　用向?qū)蒘QL語(yǔ)句</p><p>　　為了理解Visual Studio .NET如何使用開(kāi)放式并發(fā)來(lái)生成SQL語(yǔ)句，讓我們來(lái)看看用“數(shù)據(jù)適配器配置向?qū)А鄙傻腢pdate語(yǔ)句。我們將查看同一條語(yǔ)句在選擇“使用開(kāi)放式并發(fā)”選項(xiàng)和不選擇“使用開(kāi)放式并發(fā)”選項(xiàng)時(shí)的不同狀態(tài)。</p><p>　　你會(huì)注意到，選擇開(kāi)

14、放式并發(fā)與不選擇開(kāi)放式并發(fā)所生成SQL語(yǔ)句的區(qū)別只存在于Where子句上。</p><p>　　注：以下的例子使用用“數(shù)據(jù)適配器配置向?qū)А鄙傻腢pdate語(yǔ)句，并從NorthWind示例數(shù)據(jù)庫(kù)的Customers表中選擇了若干列。</p><p>　　使用開(kāi)放式并發(fā)的Update語(yǔ)句</p><p>　　這個(gè)例子使用了“數(shù)據(jù)適配器配置向?qū)А钡哪J(rèn)配置，即選中了“使

15、用開(kāi)放式并發(fā)”選項(xiàng)。</p><p>　　注： 當(dāng)使用開(kāi)放式并發(fā)時(shí)，生成的command的參數(shù)集里還存在一個(gè)參數(shù)副本。第二個(gè)參數(shù)集（帶@Original_前綴的那個(gè)）保存了最初從數(shù)據(jù)源里讀取的值。</p><p>　　檢查Where子句發(fā)現(xiàn)，每一個(gè)生成的語(yǔ)句都要檢測(cè)數(shù)據(jù)庫(kù)當(dāng)前的值是否等于最初讀取的值（例，WHERE City = @Original_City）。通過(guò)數(shù)據(jù)庫(kù)中的每個(gè)字段與最

16、初讀取的值相比較，我們很容易確定是否同時(shí)有其他用戶修改了某個(gè)字段。如果Where子句不成立，就沒(méi)有記錄會(huì)被修改，與此同時(shí)還引發(fā)了一個(gè)“數(shù)據(jù)庫(kù)并發(fā)”異常。如果數(shù)據(jù)源的某個(gè)字段為空值（NULL），生成的SQL語(yǔ)句同樣驗(yàn)證最初讀取的記錄是否也為空值。</p><p>　　UPDATE Customers</p><p>　　SET CustomerID = @CustomerID, Compan

17、yName = @CompanyName, ContactName </p><p>　　= @ContactName, ContactTitle = @ContactTitle, City = @City</p><p>　　WHERE (CustomerID = @Original_CustomerID) AND (City = @Original_City </p>&

18、lt;p>　　OR @Original_City IS NULL AND City IS NULL) AND (CompanyName = </p><p>　　@Original_CompanyName) AND (ContactName = @Original_ContactName OR </p><p>　　@Original_ContactName IS NULL AND

19、ContactName IS NULL) AND (ContactTitle = </p><p>　　@Original_ContactTitle OR @Original_ContactTitle IS NULL AND </p><p>　　ContactTitle IS NULL);</p><p>　　SELECT CustomerID, CompanyN

20、ame, ContactName, ContactTitle, City </p><p>　　FROM Customers WHERE (CustomerID = @CustomerID)</p><p>　　不使用開(kāi)放式并發(fā)的Update語(yǔ)句</p><p>　　這個(gè)例子更改了“數(shù)據(jù)適配器配置向?qū)А钡母呒?jí)選項(xiàng)，沒(méi)有選中“使用開(kāi)放式并發(fā)”選項(xiàng)。</p>

21、<p>　　以下的語(yǔ)句表明：只要數(shù)據(jù)庫(kù)中一條記錄滿足CustomerID = @Original_CustomerID ，則所有的字段都會(huì)被更新。不管這條記錄現(xiàn)在是什么樣的值，它都將被設(shè)置為通過(guò)SQL語(yǔ)句傳遞到數(shù)據(jù)源的值。在這里沒(méi)有任何關(guān)于并發(fā)的檢測(cè)，也無(wú)法得知是否同時(shí)有其它用戶在更改這條記錄。 這種方式稱為“最后的更新生效”方式。無(wú)論以前對(duì)這條記錄進(jìn)行過(guò)什么樣的修改，更新操作都會(huì)執(zhí)行。</p><p&

22、gt;　　UPDATE Customers</p><p>　　SET CustomerID = @CustomerID, CompanyName = @CompanyName, </p><p>　　ContactName = @ContactName, ContactTitle = @ContactTitle, City = @City</p><p>　　WH

23、ERE (CustomerID = @Original_CustomerID);</p><p>　　SELECT CustomerID, CompanyName, ContactName, ContactTitle, City </p><p>　　FROM Customers WHERE (CustomerID = @CustomerID)</p><p>　　

24、優(yōu)化生成的SQL語(yǔ)句</p><p>　　Visual Studio .NET生成“保持所有值”方式的SQL語(yǔ)句來(lái)實(shí)現(xiàn)開(kāi)放式并發(fā)。雖然這可能沒(méi)有生成最高效的SQL語(yǔ)句，但是它的卻生成了可以對(duì)數(shù)據(jù)源所有列（包括主鍵）進(jìn)行并發(fā)檢測(cè)的SQL語(yǔ)句。</p><p>　　使用“保持所有值”方式實(shí)現(xiàn)開(kāi)放式并發(fā)，當(dāng)執(zhí)行效率非常低下時(shí)，你可以手工修改生成的SQL語(yǔ)句以使它們不檢查數(shù)據(jù)源的所有列。最常見(jiàn)的方

25、式是使用時(shí)間戳或版本號(hào)字段。如果你的數(shù)據(jù)源包含一個(gè)每次修改記錄時(shí)都會(huì)更新的時(shí)間戳字段，你只需要驗(yàn)證數(shù)據(jù)源中的時(shí)間戳和你程序中的時(shí)間戳二者是否匹配，就可以知道是否同時(shí)有其他用戶修改了記錄。</p><p>　　下面這條SQL語(yǔ)句使用檢查時(shí)間戳模式。</p><p>　　注：  這個(gè)例子假設(shè)數(shù)據(jù)庫(kù)已經(jīng)設(shè)置了時(shí)間戳字段</p><p>　　UPDATE

26、 Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName,</p><p>　　ContactName = @ContactName, ContactTitle = @ContactTitle, City = @City</p><p>　　WHERE (CustomerID = @Original_Custom

27、erID) AND </p><p>　　(TimeStamp = @Original_TimeStamp);</p><p>　　SELECT CustomerID, CompanyName, ContactName, ContactTitle, City, </p><p>　　TimeStamp FROM Customers WHERE (CustomerID

28、 = @CustomerID)</p><p>　　并發(fā)與CommandBuilder對(duì)象</p><p>　　當(dāng)應(yīng)用程序使用SqlCommandBuilder或者OleDbCommandBuilder時(shí)，生成的Update和Delete語(yǔ)句的CommandText屬性被自動(dòng)的以開(kāi)放式并發(fā)方式創(chuàng)建。如果你不想用開(kāi)放式并發(fā)，則可以通過(guò)修改DataAdapter對(duì)象的Update和Delete

29、命令的CommandText屬性來(lái)實(shí)現(xiàn)。更多內(nèi)容，請(qǐng)參見(jiàn)OleDbCommand.CommandText屬性 或 SqlCommand.CommandText屬性。</p><p><b>　　結(jié)論</b></p><p>　　當(dāng)使用開(kāi)放式并發(fā)的“保存所有值”方法時(shí)，SQL語(yǔ)句在設(shè)計(jì)時(shí)由Visual Studio .NET的設(shè)計(jì)工具自動(dòng)生成或在運(yùn)行時(shí)由CommandB

30、uilde自動(dòng)生成。它把數(shù)據(jù)庫(kù)所有字段（包括主鍵）當(dāng)前值與初始值進(jìn)行比較，這可能不是一種最高效的方式。如果你的數(shù)據(jù)使用版本號(hào)或者時(shí)間戳方式控制并發(fā)，則可以通過(guò)修改生成的SQL語(yǔ)句來(lái)獲取更高效的執(zhí)行。</p><p><b>　　英文原文</b></p><p>　　How Visual Studio .NET Generates SQL Statements for

31、Concurrency Control</p><p>　　Steve SteinVisual Studio TeamMicrosoft Corporation</p><p>　　February 2002</p><p>　　Summary: This paper examines the SQL statements Visual Studio®

32、 .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)</p><p><b>

33、;　　Contents</b></p><p>　　Introduction PrerequisitesWhere Are the SQL Statements?Concurrency and Data AdaptersOptimizing the Generated SQL Statement Concurrency and Command-Builder ObjectsConclusi

34、on</p><p>　　Introduction</p><p>　　Any application that might have multiple users simultaneously attempting to access and modify data needs some form of concurrency control. Otherwise, one user&#

35、39;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

36、the "last-in wins" approach to updating data. This paper will explain: </p><p>　　How each of these statement types are generated. </p><p>　　How to modify the generated SQL statement fo

37、r better performance. </p><p>　　Prerequisites</p><p>　　You should have an understanding of: </p><p>　　Fundamental ADO.NET data concepts, including datasets and data adapters. For mo

38、re information, see Introduction to Data Access with ADO.NET. </p><p>　　Concurrency control basics and the options available in Visual Studio .NET. For more information, see Introduction to Data Concurrency

39、in ADO.NET. </p><p>　　Where Are the SQL Statements?</p><p>　　SQL statements are located in the CommandText property of command objects. SQL commands are automatically generated at design time wh

40、en configuring data adapters, and at run time when using command builder objects. For more information, see Concurrency and Command Builder Objects.</p><p>　　Configuring Data Adapters </p><p>　　

41、Drag a data adapter from the Data tab of the Toolbox </p><p>　　Drag a table from Server Explorer </p><p>　　Modifying an existing adapter, by selecting a data adapter and clicking the Configure D

42、ata Adapter link at the bottom of the Properties window. </p><p>　　Command Builder objects </p><p>　　Command builder objects are created programmatically at run time. For more information, see (

43、SqlCommandBuilder or OleDbCommandBuilder) </p><p>　　Concurrency and Data Adapters</p><p>　　When configuring data adapters with the Data Adapter Configuration Wizard, you can decide whether to us

44、e optimistic concurrency for the generated Update and Delete statements.</p><p>　　Considerations and Caveats</p><p>　　Your data source must have a primary key in order for the SQL statements to

45、be generated to use optimistic concurrency. </p><p>　　When creating data adapters by dragging tables from Server Explorer, the data adapter creates Update and Delete statements that are automatically configu

46、red 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

47、 optimistic concurrency option of the Advanced SQL Generation Options Dialog Box. The wizard will recreate the statements witho</p><p>　　When reconfiguring an existing data adapter, note that the advanced se

48、ttings 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

49、 do not access the Advanced SQL Generation Options dialog box. </p><p>　　If you select the Use existing stored procedures option in the Choose a Query Type section of the Data Adapter Configuration Wizard, t

50、he 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 appli

51、cation. </p><p>　　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 perfo

52、rm a bit-by-bit comparison of a large binary record would be extremely inefficient. </p><p>　　SQL Statements Generated by the Wizard</p><p>　　To understand how Visual Studio .NET constructs SQL

53、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 co

54、ncurrency option selected in the Advanced SQL Generation Options dialog box of the wizard.</p><p>　　You will notice the differences between statements that either use optimistic concurrency or not are locate

55、d in the Where clause.</p><p>　　Note   The following examples use the Update command that is generated by running the Data Adapter Configuration Wizard, and selecting several columns from the

56、Customers table in the Northwind sample database.</p><p>　　Update Statement Using Optimistic Concurrency</p><p>　　This example uses the default settings of the Data Adapter Configuration Wizard,

57、 which has the Use optimistic concurrency option selected.</p><p>　　Note   When using optimistic concurrency, the commands are generated with a second set of parameters. This second set of par

58、ameters (the ones with the @Original_ prefix) store the values that are initially read from the data source.</p><p>　　Examining the Where clause in the following statement reveals that all fields are inspect

59、ed 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 th

60、e 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 </p><p>　　UPDATE

61、 Customers</p><p>　　SET CustomerID = @CustomerID, CompanyName = @CompanyName, ContactName </p><p>　　= @ContactName, ContactTitle = @ContactTitle, City = @City</p><p>　　WHERE (Custom

62、erID = @Original_CustomerID) AND (City = @Original_City </p><p>　　OR @Original_City IS NULL AND City IS NULL) AND (CompanyName = </p><p>　　@Original_CompanyName) AND (ContactName = @Original_Con

63、tactName OR </p><p>　　@Original_ContactName IS NULL AND ContactName IS NULL) AND (ContactTitle = </p><p>　　@Original_ContactTitle OR @Original_ContactTitle IS NULL AND </p><p>　　Con

64、tactTitle IS NULL);</p><p>　　SELECT CustomerID, CompanyName, ContactName, ContactTitle, City </p><p>　　FROM Customers WHERE (CustomerID = @CustomerID)</p><p>　　Update Statement With

65、out Optimistic Concurrency</p><p>　　This example modifies the advanced settings of the Data Adapter Configuration Wizard and clears the Use optimistic concurrency option.</p><p>　　Examining the

66、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 valu

67、es 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 perfo

68、rmed on the record, the update will still be performed.</p><p>　　UPDATE Customers</p><p>　　SET CustomerID = @CustomerID, CompanyName = @CompanyName, </p><p>　　ContactName = @Contact

69、Name, ContactTitle = @ContactTitle, City = @City</p><p>　　WHERE (CustomerID = @Original_CustomerID);</p><p>　　SELECT CustomerID, CompanyName, ContactName, ContactTitle, City </p><p>

70、;　　FROM Customers WHERE (CustomerID = @CustomerID)</p><p>　　Optimizing the Generated SQL Statement</p><p>　　Visual Studio .NET generates SQL statements that use the "check all values"

71、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.</p>

72、<p>　　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 th

73、e 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 a

74、pplication's record against the timestamp in the data source to determine if a conc</p><p>　　The following SQL statement has been modified to check the timestamp.</p><p>　　Note  &#

75、160;This example presumes the timestamp has been generated in the database.</p><p>　　UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName,</p><p>　　ContactName = @ContactNam

76、e, ContactTitle = @ContactTitle, City = @City</p><p>　　WHERE (CustomerID = @Original_CustomerID) AND </p><p>　　(TimeStamp = @Original_TimeStamp);</p><p>　　SELECT CustomerID, Company

77、Name, ContactName, ContactTitle, City, </p><p>　　TimeStamp FROM Customers WHERE (CustomerID = @CustomerID)</p><p>　　Concurrency and Command-Builder Objects</p><p>　　If your applicat

78、ion 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 pr

79、ogrammatically modify the CommandText property of the data adapter's Update and Delete commands. For more information, see OleDbCommand.CommandText property or SqlCommand.CommandText property.</p><p>　　C

80、onclusion</p><p>　　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 concur

81、rency. 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 timestamp