外文翻譯--visual studio .net如何為并發(fā)控制生成sql語句

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

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

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

4、/p><p><b>　　結(jié)論</b></p><p><b>　　引言</b></p><p>　　任何可能同時(shí)被多個(gè)用戶訪問或修改數(shù)據(jù)的應(yīng)用程序，都需要進(jìn)行并發(fā)控制。否則，一個(gè)用戶更改記錄時(shí)可能不經(jīng)意的覆蓋了其他用戶的更改。Visual Studio .NET的設(shè)計(jì)工具可以生成“保持所有值”方式的開放式并發(fā)SQL語句或生成

5、“最后的更新生效”方式的SQL語句來更新數(shù)據(jù)。這篇文章將解釋：</p><p>　　不同的SQL語句是如何生成的</p><p>　　如何修改自動(dòng)生成的SQL語句可以提高執(zhí)行效率</p><p>　　閱讀此文章時(shí)應(yīng)具備的一些知識(shí)</p><p>　　你需要具備以下知識(shí)：</p><p>　　基本的ADO.NET概念，包

6、括數(shù)據(jù)集(DataSet)以及數(shù)據(jù)適配器(DataAdapters)。更多信息請參見ADO.NET 數(shù)據(jù)訪問介紹（Introduction to Data Access with ADO.NET）。</p><p>　　數(shù)據(jù)并發(fā)機(jī)制以及會(huì)操作Visual Studio .NET。更多內(nèi)容請參見 介紹 ADO.NET 中的數(shù)據(jù)并發(fā)(Introduction to Data Concurrency in ADO

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

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

9、mandBuilder對象</p><p>　　CommandBuilder對象在運(yùn)行時(shí)刻被創(chuàng)建，更多信息請參閱 SqlCommandBuilder 或 OleDbCommandBuilder。</p><p>　　并發(fā)控制與數(shù)據(jù)適配器(DataAdapter)</p><p>　　使用“數(shù)據(jù)適配器配置向?qū)А迸渲脭?shù)據(jù)適配器時(shí)，你可以選擇是否使用開放式并發(fā)來生成Upd

10、ate和Delete語句。</p><p><b>　　一些思考和注意事項(xiàng)</b></p><p>　　你的數(shù)據(jù)源必須有一個(gè)主鍵才能以開放式并發(fā)方式生成SQL語句</p><p>　　當(dāng)使用從“服務(wù)器資源管理器”拖放一個(gè)數(shù)據(jù)表的方式來創(chuàng)建DataAdapter對象時(shí)，DataAdapter對象自動(dòng)生成基于開放式并發(fā)的Update和Delete語

11、句。如果你不想使用開放式并發(fā)，右擊DataAdapter對象，從快捷菜單中選擇“配置數(shù)據(jù)適配器”，然后在“高級SQL生成選項(xiàng)”對話框中清除“使用開放式并發(fā)”選項(xiàng)的選定。向?qū)t會(huì)重新創(chuàng)建不帶并發(fā)檢測的SQL語句。</p><p>　　當(dāng)重新配置現(xiàn)有的DataAdapter時(shí)，應(yīng)注意“高級SQL生成選項(xiàng)”對話框里的選項(xiàng)已經(jīng)全部恢復(fù)默認(rèn)。例如最初配置DataAdapter時(shí)沒有選定“使用開放式并發(fā)”選項(xiàng)，但是當(dāng)重新配置

12、DataAdapter時(shí)，“使用開放式并發(fā)”選項(xiàng)卻會(huì)被選定，即便你根本沒有打開過“高級SQL生成選項(xiàng)”對話框。</p><p>　　如果你在“數(shù)據(jù)適配器配置向?qū)А钡摹斑x擇查詢類型”頁面選擇“使用現(xiàn)有的存儲(chǔ)過程”，則“使用開放式并發(fā)”選項(xiàng)將不可用。存儲(chǔ)過程仍按其原來的方式執(zhí)行。如果想使用并發(fā)檢測的話，必須將其包括到存儲(chǔ)過程中、或在你的應(yīng)用程序中編寫相應(yīng)的代碼。</p><p>　　當(dāng)使用開放

13、式并發(fā)來創(chuàng)建SQL命令時(shí)，不會(huì)對二進(jìn)制數(shù)據(jù)列驗(yàn)證進(jìn)行并發(fā)處理。這將導(dǎo)致用這種方法對大的二進(jìn)制記錄集執(zhí)行按位比較算法時(shí)的效率低下。</p><p>　　用向?qū)蒘QL語句</p><p>　　為了理解Visual Studio .NET如何使用開放式并發(fā)來生成SQL語句，讓我們來看看用“數(shù)據(jù)適配器配置向?qū)А鄙傻腢pdate語句。我們將查看同一條語句在選擇“使用開放式并發(fā)”選項(xiàng)和不選擇“使

14、用開放式并發(fā)”選項(xiàng)時(shí)的不同狀態(tài)。</p><p>　　你會(huì)注意到，選擇開放式并發(fā)與不選擇開放式并發(fā)所生成SQL語句的區(qū)別只存在于Where子句上。</p><p>　　注：以下的例子使用用“數(shù)據(jù)適配器配置向?qū)А鄙傻腢pdate語句，并從NorthWind示例數(shù)據(jù)庫的Customers表中選擇了若干列。</p><p>　　使用開放式并發(fā)的Update語句</

15、p><p>　　這個(gè)例子使用了“數(shù)據(jù)適配器配置向?qū)А钡哪J(rèn)配置，即選中了“使用開放式并發(fā)”選項(xiàng)。</p><p>　　注： 當(dāng)使用開放式并發(fā)時(shí)，生成的command的參數(shù)集里還存在一個(gè)參數(shù)副本。第二個(gè)參數(shù)集（帶@Original_前綴的那個(gè)）保存了最初從數(shù)據(jù)源里讀取的值。</p><p>　　檢查Where子句發(fā)現(xiàn)，每一個(gè)生成的語句都要檢測數(shù)據(jù)庫當(dāng)前的值是否等于最初讀

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

17、<p>　　SET CustomerID = @CustomerID, CompanyName = @CompanyName, ContactName </p><p>　　= @ContactName, ContactTitle = @ContactTitle, City = @City</p><p>　　WHERE (CustomerID = @Original_Cust

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

19、;<p>　　@Original_ContactName IS NULL AND ContactName IS NULL) AND (ContactTitle = </p><p>　　@Original_ContactTitle OR @Original_ContactTitle IS NULL AND </p><p>　　ContactTitle IS NULL);&l

20、t;/p><p>　　SELECT CustomerID, CompanyName, ContactName, ContactTitle, City </p><p>　　FROM Customers WHERE (CustomerID = @CustomerID)</p><p>　　不使用開放式并發(fā)的Update語句</p><p>　　這個(gè)

21、例子更改了“數(shù)據(jù)適配器配置向?qū)А钡母呒夁x項(xiàng)，沒有選中“使用開放式并發(fā)”選項(xiàng)。</p><p>　　以下的語句表明：只要數(shù)據(jù)庫中一條記錄滿足CustomerID = @Original_CustomerID ，則所有的字段都會(huì)被更新。不管這條記錄現(xiàn)在是什么樣的值，它都將被設(shè)置為通過SQL語句傳遞到數(shù)據(jù)源的值。在這里沒有任何關(guān)于并發(fā)的檢測，也無法得知是否同時(shí)有其它用戶在更改這條記錄。 這種方式稱為“最后的更新生效”方

22、式。無論以前對這條記錄進(jìn)行過什么樣的修改，更新操作都會(huì)執(zhí)行。</p><p>　　UPDATE Customers</p><p>　　SET CustomerID = @CustomerID, CompanyName = @CompanyName, </p><p>　　ContactName = @ContactName, ContactTitle = @Con

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

24、CustomerID = @CustomerID)</p><p>　　優(yōu)化生成的SQL語句</p><p>　　Visual Studio .NET生成“保持所有值”方式的SQL語句來實(shí)現(xiàn)開放式并發(fā)。雖然這可能沒有生成最高效的SQL語句，但是它的卻生成了可以對數(shù)據(jù)源所有列（包括主鍵）進(jìn)行并發(fā)檢測的SQL語句。</p><p>　　使用“保持所有值”方式實(shí)現(xiàn)開放式并

25、發(fā)，當(dāng)執(zhí)行效率非常低下時(shí)，你可以手工修改生成的SQL語句以使它們不檢查數(shù)據(jù)源的所有列。最常見的方式是使用時(shí)間戳或版本號字段。如果你的數(shù)據(jù)源包含一個(gè)每次修改記錄時(shí)都會(huì)更新的時(shí)間戳字段，你只需要驗(yàn)證數(shù)據(jù)源中的時(shí)間戳和你程序中的時(shí)間戳二者是否匹配，就可以知道是否同時(shí)有其他用戶修改了記錄。</p><p>　　下面這條SQL語句使用檢查時(shí)間戳模式。</p><p>　　注：  

26、這個(gè)例子假設(shè)數(shù)據(jù)庫已經(jīng)設(shè)置了時(shí)間戳字段</p><p>　　UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName,</p><p>　　ContactName = @ContactName, ContactTitle = @ContactTitle, City = @City</p>

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

28、>　　TimeStamp FROM Customers WHERE (CustomerID = @CustomerID)</p><p>　　并發(fā)與CommandBuilder對象</p><p>　　當(dāng)應(yīng)用程序使用SqlCommandBuilder或者OleDbCommandBuilder時(shí)，生成的Update和Delete語句的CommandText屬性被自動(dòng)的以開放式并發(fā)方式創(chuàng)

29、建。如果你不想用開放式并發(fā)，則可以通過修改DataAdapter對象的Update和Delete命令的CommandText屬性來實(shí)現(xiàn)。更多內(nèi)容，請參見OleDbCommand.CommandText屬性 或 SqlCommand.CommandText屬性。</p><p><b>　　結(jié)論</b></p><p>　　當(dāng)使用開放式并發(fā)的“保存所有值”方法時(shí)，SQL

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

31、Visual Studio .NET Generates SQL Statements for Concurrency Control</p><p>　　Steve SteinVisual Studio TeamMicrosoft Corporation</p><p>　　February 2002</p><p>　　Summary: This paper

32、 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

33、. (5 printed pages)</p><p><b>　　Contents</b></p><p>　　Introduction PrerequisitesWhere Are the SQL Statements?Concurrency and Data AdaptersOptimizing the Generated SQL Statement

34、Concurrency and Command-Builder ObjectsConclusion</p><p>　　Introduction</p><p>　　Any application that might have multiple users simultaneously attempting to access and modify data needs some fo

35、rm 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 val

36、ues" approach to optimistic concurrency or the "last-in wins" approach to updating data. This paper will explain: </p><p>　　How each of these statement types are generated. </p><p&g

37、t;　　How to modify the generated SQL statement for better performance. </p><p>　　Prerequisites</p><p>　　You should have an understanding of: </p><p>　　Fundamental ADO.NET data concep

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

39、formation, see Introduction to Data Concurrency in ADO.NET. </p><p>　　Where Are the SQL Statements?</p><p>　　SQL statements are located in the CommandText property of command objects. SQL comman

40、ds 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.</p><p>　　C

41、onfiguring Data Adapters </p><p>　　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 selec

42、ting a data adapter and clicking the Configure Data Adapter link at the bottom of the Properties window. </p><p>　　Command Builder objects </p><p>　　Command builder objects are created programma

43、tically at run time. For more information, see (SqlCommandBuilder or OleDbCommandBuilder) </p><p>　　Concurrency and Data Adapters</p><p>　　When configuring data adapters with the Data Adapter Co

44、nfiguration Wizard, you can decide whether to use optimistic concurrency for the generated Update and Delete statements.</p><p>　　Considerations and Caveats</p><p>　　Your data source must have a

45、 primary key in order for the SQL statements to be generated to use optimistic concurrency. </p><p>　　When creating data adapters by dragging tables from Server Explorer, the data adapter creates Update and

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

47、apter 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 witho</p><p>　　When reconfiguring an

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

49、y be selected if you reconfigure it, even if you 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 sec

50、tion 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 proc

51、edure, or programmatically built into your application. </p><p>　　When commands are generated to use optimistic concurrency, no verification will be performed on binary columns to determine whether concurren

52、t changes have been made. The resources to perform 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 u

53、nderstand 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 gener

54、ated both with and without the Use optimistic concurrency option selected in the Advanced SQL Generation Options dialog box of the wizard.</p><p>　　You will notice the differences between statements that eit

55、her use optimistic concurrency or not are located in the Where clause.</p><p>　　Note   The following examples use the Update command that is generated by running the Data Adapter Configuration

56、 Wizard, and selecting several columns from the Customers table in the Northwind sample database.</p><p>　　Update Statement Using Optimistic Concurrency</p><p>　　This example uses the default se

57、ttings of the Data Adapter Configuration Wizard, which has the Use optimistic concurrency option selected.</p><p>　　Note   When using optimistic concurrency, the commands are generated with a

58、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.</p><p>　　Examining the Where clause in the followi

59、ng 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).

60、 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 DBConcurrencyExcepti

61、on is raised. If a </p><p>　　UPDATE Customers</p><p>　　SET CustomerID = @CustomerID, CompanyName = @CompanyName, ContactName </p><p>　　= @ContactName, ContactTitle = @ContactTitle,

62、City = @City</p><p>　　WHERE (CustomerID = @Original_CustomerID) AND (City = @Original_City </p><p>　　OR @Original_City IS NULL AND City IS NULL) AND (CompanyName = </p><p>　　@Origin

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

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

65、rID)</p><p>　　Update Statement Without Optimistic Concurrency</p><p>　　This example modifies the advanced settings of the Data Adapter Configuration Wizard and clears the Use optimistic concurre

66、ncy option.</p><p>　　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

67、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, beca

68、use no matter what modifications have been performed on the record, the update will still be performed.</p><p>　　UPDATE Customers</p><p>　　SET CustomerID = @CustomerID, CompanyName = @CompanyNam

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

70、actName, ContactTitle, City </p><p>　　FROM Customers WHERE (CustomerID = @CustomerID)</p><p>　　Optimizing the Generated SQL Statement</p><p>　　Visual Studio .NET generates SQL state

71、ments 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

72、 data source containing a primary key.</p><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 che

73、ck 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 chan

74、ges, you need only check the timestamp in your application'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 tim

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

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

77、lt;/p><p>　　SELECT CustomerID, CompanyName, ContactName, ContactTitle, City, </p><p>　　TimeStamp FROM Customers WHERE (CustomerID = @CustomerID)</p><p>　　Concurrency and Command-Builde

78、r Objects</p><p>　　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 no

79、t 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 SqlComma

80、nd.CommandText property.</p><p>　　Conclusion</p><p>　　The SQL statements that are automatically generated by the design tools in Visual Studio .NET or by command builder objects use the "ch

81、eck 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

82、. If your data uses version numbers or timestamps, you can modify the generated SQL statements for better performance.</p><p>　　Send feedback on this topic to Microsoft</p><p>　　© Microsoft