計(jì)算機(jī)專業(yè)外文翻譯--visual studio .net如何為并發(fā)控制生成sql語(yǔ)句

1、<p><b>　　譯文：</b></p><p>　　Visual Studio .NET如何為并發(fā)控制生成SQL語(yǔ)句</p><p>　　作者：史蒂夫斯坦的Visual Studio團(tuán)隊(duì) 時(shí)間：2002年2月</p><p>　　摘要：這篇文章研究Visual Studio? .NET為不同的并發(fā)控制方式所產(chǎn)生的SQL語(yǔ)句，如何對(duì)

2、它們進(jìn)行修改可以提高執(zhí)行效率，以及如何生成不帶并發(fā)控制的SQL語(yǔ)句。</p><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ì)工具可以生成“保持所有值”方式的開放式并發(fā)SQL語(yǔ)句或生成“最后的更新

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

4、括數(shù)據(jù)集(DataSet)以及數(shù)據(jù)適配器(DataAdapters)。更多信息請(qǐng)參見(jiàn)ADO.NET 數(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.N

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

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

7、接</p><p>　　CommandBuilder對(duì)象</p><p>　　* CommandBuilder對(duì)象在運(yùn)行時(shí)刻被創(chuàng)建，更多信息請(qǐng)參閱 SqlCommandBuilder 或 OleDbCommandBuilder。</p><p>　　并發(fā)控制與數(shù)據(jù)適配器(DataAdapter)</p><p>　　使用“數(shù)據(jù)適配器配置向?qū)А?/p>

8、配置數(shù)據(jù)適配器時(shí)，你可以選擇是否使用開放式并發(fā)來(lái)生</p><p>　　成Update和Delete語(yǔ)句。</p><p><b>　　一些思考和注意事項(xiàng)</b></p><p>　　* 你的數(shù)據(jù)源必須有一個(gè)主鍵才能以開放式并發(fā)方式生成SQL語(yǔ)句</p><p>　　* 當(dāng)使用從“服務(wù)器資源管理器”拖放一個(gè)數(shù)據(jù)表的方式來(lái)

9、創(chuàng)建DataAdapter對(duì)象時(shí)，DataAdapter對(duì)象自動(dòng)生成基于開放式并發(fā)的Update和Delete語(yǔ)句。如果你不想使用開放式并發(fā)，右擊DataAdapter對(duì)象，從快捷菜單中選擇“配置數(shù)據(jù)適配器”，然后在“高級(jí)SQL生成選項(xiàng)”對(duì)話框中清除“使用開放式并發(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)

10、”對(duì)話框里的選項(xiàng)已經(jīng)全部恢復(fù)默認(rèn)。例如最初配置DataAdapter時(shí)沒(méi)有選定“使用開放式并發(fā)”選項(xiàng)，但是當(dāng)重新配置DataAdapter時(shí)，“使用開放式并發(fā)”選項(xiàng)卻會(huì)被選定，即便你根本沒(méi)有打開過(guò)“高級(jí)SQL生成選項(xiàng)”對(duì)話框。</p><p>　　* 如果你在“數(shù)據(jù)適配器配置向?qū)А钡摹斑x擇查詢類型”頁(yè)面選擇“使用現(xiàn)有的存儲(chǔ)過(guò)程”，則“使用開放式并發(fā)”選項(xiàng)將不可用。存儲(chǔ)過(guò)程仍按其原來(lái)的方式執(zhí)行。如果想使用并發(fā)檢測(cè)的

11、話，必須將其包括到存儲(chǔ)過(guò)程中、或在你的應(yīng)用程序中編寫相應(yīng)的代碼。</p><p>　　* 當(dāng)使用開放式并發(fā)來(lái)創(chuàng)建SQL命令時(shí)，不會(huì)對(duì)二進(jìn)制數(shù)據(jù)列驗(yàn)證進(jìn)行并發(fā)處理。這將導(dǎo)致用這種方法對(duì)大的二進(jìn)制記錄集執(zhí)行按位比較算法時(shí)的效率低下。</p><p>　　用向?qū)蒘QL語(yǔ)句</p><p>　　為了理解Visual Studio .NET如何使用開放式并發(fā)來(lái)生成SQL語(yǔ)

12、句，讓我們來(lái)看看用“數(shù)</p><p>　　據(jù)適配器配置向?qū)А鄙傻腢pdate語(yǔ)句。我們將查看同一條語(yǔ)句在選擇“使用開放式并發(fā)”選項(xiàng)和不選擇“使用開放式并發(fā)”選項(xiàng)時(shí)的不同狀態(tài)。</p><p>　　你會(huì)注意到，選擇開放式并發(fā)與不選擇開放式并發(fā)所生成SQL語(yǔ)句的區(qū)別只存在于Where子句上。注：以下的例子使用用“數(shù)據(jù)適配器配置向?qū)А鄙傻腢pdate語(yǔ)句，并從NorthWind示例數(shù)據(jù)庫(kù)的

13、Customers表中選擇了若干列。</p><p>　　使用開放式并發(fā)的Update語(yǔ)句</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ù)源里讀取的值

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

15、讀取的記錄是否也為空值。</p><p>　　UPDATE Customers</p><p>　　SET CustomerID = @CustomerID, CompanyName = @CompanyName, ContactName </p><p>　　= @ContactName, ContactTitle = @ContactTitle, City =

16、@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>　　@Original_Comp

17、anyName) AND (ContactName = @Original_ContactName OR </p><p>　　@Original_ContactName IS NULL AND ContactName IS NULL) AND (ContactTitle = </p><p>　　@Original_ContactTitle OR @Original_ContactTit

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

19、;/p><p>　　不使用開放式并發(fā)的Update語(yǔ)句</p><p>　　這個(gè)例子更改了“數(shù)據(jù)適配器配置向?qū)А钡母呒?jí)選項(xiàng)，沒(méi)有選中“使用開放式并發(fā)”選項(xiàng)。</p><p>　　以下的語(yǔ)句表明：只要數(shù)據(jù)庫(kù)中一條記錄滿足CustomerID = @Original_CustomerID ，則所有的字段都會(huì)被更新。不管這條記錄現(xiàn)在是什么樣的值，它都將被設(shè)置為通過(guò)SQL語(yǔ)句傳

20、遞到數(shù)據(jù)源的值。在這里沒(méi)有任何關(guān)于并發(fā)的檢測(cè)，也無(wú)法得知是否同時(shí)有其它用戶在更改這條記錄。 這種方式稱為“最后的更新生效”方式。無(wú)論以前對(duì)這條記錄進(jìn)行過(guò)什么樣的修改，更新操作都會(huì)執(zhí)行。</p><p>　　UPDATE Customers</p><p>　　SET CustomerID = @CustomerID, CompanyName = @CompanyName, </p&g

21、t;<p>　　ContactName = @ContactName, ContactTitle = @ContactTitle, City = @City</p><p>　　WHERE (CustomerID = @Original_CustomerID);</p><p>　　SELECT CustomerID, CompanyName, ContactName, Co

22、ntactTitle, City </p><p>　　FROM Customers WHERE (CustomerID = @CustomerID)</p><p>　　優(yōu)化生成的SQL語(yǔ)句</p><p>　　Visual Studio .NET生成“保持所有值”方式的SQL語(yǔ)句來(lái)實(shí)現(xiàn)開放式并發(fā)。雖然這可能沒(méi)</p><p>　　有生成最

23、高效的SQL語(yǔ)句，但是它的卻生成了可以對(duì)數(shù)據(jù)源所有列（包括主鍵）進(jìn)行并發(fā)檢測(cè)的SQL語(yǔ)句。使用“保持所有值”方式實(shí)現(xiàn)開放式并發(fā)，當(dāng)執(zhí)行效率非常低下時(shí)，你可以手工修改生成的SQL語(yǔ)句以使它們不檢查數(shù)據(jù)源的所有列。最常見(jiàn)的方式是使用時(shí)間戳或版本號(hào)字段。如果你的數(shù)據(jù)源包含一個(gè)每次修改記錄時(shí)都會(huì)更新的時(shí)間戳字段，你只需要驗(yàn)證數(shù)據(jù)源中的時(shí)間戳和你程序中的時(shí)間戳二者是否匹配，就可以知道是否同時(shí)有其他用戶修改了記錄。</p><p

24、>　　下面這條SQL語(yǔ)句使用檢查時(shí)間戳模式。</p><p>　　注：這個(gè)例子假設(shè)數(shù)據(jù)庫(kù)已經(jīng)設(shè)置了時(shí)間戳字段</p><p>　　UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName,</p><p>　　ContactName = @ContactName, Con

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

26、ContactName, ContactTitle, City, </p><p>　　TimeStamp FROM Customers WHERE (CustomerID = @CustomerID)</p><p>　　并發(fā)與CommandBuilder對(duì)象</p><p>　　當(dāng)應(yīng)用程序使用SqlCommandBuilder或者OleDbCommandBuil

27、der時(shí)，生成的Update</p><p>　　和Delete語(yǔ)句的CommandText屬性被自動(dòng)的以開放式并發(fā)方式創(chuàng)建。如果你不想用開放式并發(fā)，則可以通過(guò)修改DataAdapter對(duì)象的Update和Delete命令的CommandText屬性來(lái)實(shí)現(xiàn)。更多內(nèi)容，請(qǐng)參見(jiàn)OleDbCommand.CommandText屬性 或 SqlCommand.CommandText屬性。</p><p

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

29、</p><p><b>　　原文：</b></p><p>　　How Visual Studio .NET Generates SQL Statements for Concurrency Control</p><p>　　Author: Steve SteinVisual Studio Team</p><p>

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

31、ncurrency control. (5 printed pages).</p><p>　　Introduction</p><p>　　Any application that might have multiple users simultaneously attempting to access and modify data needs some form of concurr

32、ency 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" app

33、roach 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>　　* How t

34、o 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 concepts, inc

35、luding 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 informa

36、tion, 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 commands ar

37、e 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 over

38、lay network programming issues. Even early overlay network Configuring Data Adapters </p><p>　　* Drag a data adapter from the Data tab of the Toolbox </p><p>　　* Drag a table from Server Explore

39、r </p><p>　　* Modifying an existing adapter, by selecting a data adapter and clicking the Configure Data Adapter link at the bottom of the Properties window. </p><p>　　Command Builder objects &l

40、t;/p><p>　　* Command builder objects are created programmatically at run time. For more information, see (SqlCommandBuilder or OleDbCommandBuilder) </p><p>　　Concurrency and Data Adapters</p>

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

42、tions and Caveats</p><p>　　* Your data source must have a primary key in order for the SQL statements to be generated to use optimistic concurrency. </p><p>　　* When creating data adapters by dr

43、agging 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 reconfigur

44、e 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 re

45、create the statements wit</p><p>　　* 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

46、 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. </p><p>　　* If you selec

47、t 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

48、, 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 perform

49、ed on binary columns to determine whether conc</p><p>　　To understand how Visual Studio .NET constructs SQL statements that use optimistic concurrency, let us inspect the Update statement generated by the Da

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

51、;<p>　　You will notice the differences between statements that either use optimistic concurrency or not are located in the Where clause.</p><p>　　Note???The following examples use the Update command t

52、hat is generated by running the Data Adapter Configuration Wizard, and selecting several columns from the Customers table in the Northwind sample database.</p><p>　　Update Statement Using Optimistic Concurre

53、ncy</p><p>　　This example uses the default settings of the Data Adapter Configuration Wizard, which has the Use optimistic concurrency option selected.</p><p>　　Note???When using optimistic conc

54、urrency, 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.</p><p>

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

56、(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 rec

57、ords are updated and a DBConcurrencyException is raised. If a </p><p>　　UPDATE Customers</p><p>　　SET CustomerID = @CustomerID, CompanyName = @CompanyName, ContactName </p><p>　　= @

58、ContactName, ContactTitle = @ContactTitle, City = @City</p><p>　　WHERE (CustomerID = @Original_CustomerID) AND (City = @Original_City </p><p>　　OR @Original_City IS NULL AND City IS NULL) AND (C

59、ompanyName = </p><p>　　@Original_CompanyName) AND (ContactName = @Original_ContactName OR </p><p>　　@Original_ContactName IS NULL AND ContactName IS NULL) AND (ContactTitle = </p><p&g

60、t;　　@Original_ContactTitle OR @Original_ContactTitle IS NULL AND ContactTitle IS NULL);</p><p>　　SELECT CustomerID, CompanyName, ContactName, ContactTitle, City </p><p>　　FROM Customers WHERE (C

61、ustomerID = @CustomerID)</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

62、 optimistic concurrency 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 matte

63、r 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&

64、quot; approach, because 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, Compa

65、nyName = @CompanyName, </p><p>　　ContactName = @ContactName, ContactTitle = @ContactTitle, City = @City</p><p>　　WHERE (CustomerID = @Original_CustomerID);</p><p>　　SELECT CustomerI

66、D, CompanyName, ContactName, ContactTitle, City </p><p>　　FROM Customers WHERE (CustomerID = @CustomerID)</p><p>　　Optimizing the Generated SQL Statement</p><p>　　Visual Studio .NET

67、 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 concurren

68、cy violations on any 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

69、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 ever

70、y 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 conc</p><p>　　Note???This example presumes the timestamp

71、 has been generated in the database.</p><p>　　UPDATE Customers SET CustomerID = @CustomerID, CompanyName = @CompanyName,</p><p>　　ContactName = @ContactName, ContactTitle = @ContactTitle, City =

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

73、</p><p>　　TimeStamp FROM Customers WHERE (CustomerID = @CustomerID)</p><p>　　Concurrency and Command-Builder Objects</p><p>　　If your application uses SqlCommandBuilder or OleDbComm

74、andBuilder, 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 p

75、roperty of the data adapter's Update and Delete commands. For more information, see OleDbCommand.CommandText property or SqlCommand.CommandText property.</p><p>　　Conclusion</p><p>　　The SQL

76、 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 mos

77、t 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 sta