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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

16、初讀取的值相比較，我們很容易確定是否同時有其他用戶修改了某個字段。如果Where子句不成立，就沒有記錄會被修改，與此同時還引發(fā)了一個“數(shù)據(jù)庫并發(fā)”異常。如果數(shù)據(jù)源的某個字段為空值（NULL），生成的SQL語句同樣驗證最初讀取的記錄是否也為空值。</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>　　不使用開放式并發(fā)的Update語句</p><p>　　這個例子更改了“數(shù)據(jù)適配器配置向?qū)А钡母呒夁x項，沒有選中“使用開放式并發(fā)”選項。</p>

21、<p>　　以下的語句表明：只要數(shù)據(jù)庫中一條記錄滿足CustomerID = @Original_CustomerID ，則所有的字段都會被更新。不管這條記錄現(xiàn)在是什么樣的值，它都將被設(shè)置為通過SQL語句傳遞到數(shù)據(jù)源的值。在這里沒有任何關(guān)于并發(fā)的檢測，也無法得知是否同時有其它用戶在更改這條記錄。 這種方式稱為“最后的更新生效”方式。無論以前對這條記錄進行過什么樣的修改，更新操作都會執(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語句</p><p>　　Visual Studio .NET生成“保持所有值”方式的SQL語句來實現(xiàn)開放式并發(fā)。雖然這可能沒有生成最高效的SQL語句，但是它的卻生成了可以對數(shù)據(jù)源所有列（包括主鍵）進行并發(fā)檢測的SQL語句。</p><p>　　使用“保持所有值”方式實現(xiàn)開放式并發(fā)，當(dāng)執(zhí)行效率非常低下時，你可以手工修改生成的SQL語句以使它們不檢查數(shù)據(jù)源的所有列。最常見的方

25、式是使用時間戳或版本號字段。如果你的數(shù)據(jù)源包含一個每次修改記錄時都會更新的時間戳字段，你只需要驗證數(shù)據(jù)源中的時間戳和你程序中的時間戳二者是否匹配，就可以知道是否同時有其他用戶修改了記錄。</p><p>　　下面這條SQL語句使用檢查時間戳模式。</p><p>　　注：  這個例子假設(shè)數(shù)據(jù)庫已經(jīng)設(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對象</p><p>　　當(dāng)應(yīng)用程序使用SqlCommandBuilder或者OleDbCommandBuilder時，生成的Update和Delete語句的CommandText屬性被自動的以開放式并發(fā)方式創(chuàng)建。如果你不想用開放式并發(fā)，則可以通過修改DataAdapter對象的Update和Delete

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

30、uilde自動生成。它把數(shù)據(jù)庫所有字段（包括主鍵）當(dāng)前值與初始值進行比較，這可能不是一種最高效的方式。如果你的數(shù)據(jù)使用版本號或者時間戳方式控制并發(fā)，則可以通過修改生成的SQL語句來獲取更高效的執(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