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

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

2、它們進行修改可以提高執(zhí)行效率，以及如何生成不帶并發(fā)控制的SQL語句。</p><p><b>　　引言</b></p><p>　　任何可能同時被多個用戶訪問或修改數(shù)據(jù)的應用程序，都需要進行并發(fā)控制。否則，一個用戶更改記錄時可能不經意的覆蓋了其他用戶的更改。Visual Studio .NET的設計工具可以生成“保持所有值”方式的開放式并發(fā)SQL語句或生成“最后的更新

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

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

5、ET）。</p><p>　　自動生成的SQL語句在哪里</p><p>　　自動生成的SQL語句在command對象的CommandText屬性里。在設計階段配置</p><p>　　DataAdapter對象時或使用CommandBuilder對象時SQL命令被自動生成。更多信息，請參見 并發(fā)與CommandBuilder對象（Concurrency and C

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

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

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

9、創(chuàng)建DataAdapter對象時，DataAdapter對象自動生成基于開放式并發(fā)的Update和Delete語句。如果你不想使用開放式并發(fā)，右擊DataAdapter對象，從快捷菜單中選擇“配置數(shù)據(jù)適配器”，然后在“高級SQL生成選項”對話框中清除“使用開放式并發(fā)”選項的選定。向導則會重新創(chuàng)建不帶并發(fā)檢測的SQL語句。</p><p>　　* 當重新配置現(xiàn)有的DataAdapter時，應注意“高級SQL生成選項

10、”對話框里的選項已經全部恢復默認。例如最初配置DataAdapter時沒有選定“使用開放式并發(fā)”選項，但是當重新配置DataAdapter時，“使用開放式并發(fā)”選項卻會被選定，即便你根本沒有打開過“高級SQL生成選項”對話框。</p><p>　　* 如果你在“數(shù)據(jù)適配器配置向導”的“選擇查詢類型”頁面選擇“使用現(xiàn)有的存儲過程”，則“使用開放式并發(fā)”選項將不可用。存儲過程仍按其原來的方式執(zhí)行。如果想使用并發(fā)檢測的

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

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

13、Customers表中選擇了若干列。</p><p>　　使用開放式并發(fā)的Update語句</p><p>　　這個例子使用了“數(shù)據(jù)適配器配置向導”的默認配置，即選中了“使用開放式并發(fā)”選項。</p><p>　　注： 當使用開放式并發(fā)時，生成的command的參數(shù)集里還存在一個參數(shù)副本。第二個參數(shù)集（帶@Original_前綴的那個）保存了最初從數(shù)據(jù)源里讀取的值

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

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語句</p><p>　　這個例子更改了“數(shù)據(jù)適配器配置向導”的高級選項，沒有選中“使用開放式并發(fā)”選項。</p><p>　　以下的語句表明：只要數(shù)據(jù)庫中一條記錄滿足CustomerID = @Original_CustomerID ，則所有的字段都會被更新。不管這條記錄現(xiàn)在是什么樣的值，它都將被設置為通過SQL語句傳

20、遞到數(shù)據(jù)源的值。在這里沒有任何關于并發(fā)的檢測，也無法得知是否同時有其它用戶在更改這條記錄。 這種方式稱為“最后的更新生效”方式。無論以前對這條記錄進行過什么樣的修改，更新操作都會執(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語句</p><p>　　Visual Studio .NET生成“保持所有值”方式的SQL語句來實現(xiàn)開放式并發(fā)。雖然這可能沒</p><p>　　有生成最

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

24、>　　下面這條SQL語句使用檢查時間戳模式。</p><p>　　注：這個例子假設數(shù)據(jù)庫已經設置了時間戳字段</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對象</p><p>　　當應用程序使用SqlCommandBuilder或者OleDbCommandBuil

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

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