摘要： Billy Hollis 解釋了在復雜系統中使用存儲過程的好處，使存儲過程超出了演示軟件的范疇，并提供了有關如何訪問存儲過程并在您自己的應用程序中開始使用這些存儲過程的實用示例。
我們作者通常將軟件分為兩類 - 真實軟件和演示軟件。真實軟件是在真實環境中使用的軟件。演示軟件用于闡釋編程概念。
您在文章和書籍中看到的大部分代碼都是演示軟件。它必須比真實軟件簡單，否則讀者將為那些與闡釋的概念無關的細節而費神。但有時演示軟件又過于極端。追求簡單會忽略開發人員編寫真實軟件所需的細節。
最近我在數據訪問方面就曾遇到這樣一個問題。實際上，我見過的每個數據訪問示例都使用 SQL 語句從關系數據庫（例如，Microsoft SQL Server?）中讀取或向其中寫入。然而，在真實環境中，除了對有限的小型系統適用以外，這是很不可取的編程方法。結構合理的 n 層應用程序使用存儲過程代替 SQL 語句進行數據訪問。
存儲過程在概念上類似于程序中的函數。它們獲取輸入參數，以黑盒模式運行并返回相應信息。與函數不同的是，存儲過程由數據庫引擎執行，而不是在程序中執行。也就是說，將信息輸入到存儲過程或從中輸出信息都必須通過與數據庫交互的技術來完成。在 Microsoft Visual Basic? 6.0 中，該技術就是傳統的 ADO。在 Visual Basic .NET 中，我們可以使用 ADO.NET 完成該任務。
對于許多編程任務而言，Visual Basic .NET 使得通過存儲過程訪問數據比使用 Visual Basic 6.0 容易得多。其中有一些用來幫助該過程的向導，一旦您學會如何避免一些錯誤之后，即使使用 ADO.NET 從頭編寫這些邏輯也并不復雜。
本文介紹了一些在 ADO.NET 中使用存儲過程的基本技巧，并從只讀操作開始，一直到如何使用存儲過程進行數據插入、刪除和更新。
您無需精通存儲過程的編寫也可從本文受益。許多大型編程小組的開發人員需要使用他人編寫的存儲過程。我們的示例之一需要將存儲過程插入到示例數據庫中，但我們將逐步介紹這個任務。
ADO.NET 簡介本文假設您已經了解了 ADO.NET 的基礎知識。如果您在工作中從未使用過 ADO.NET 中的 DataAdapter 、 DataSet 和 Command 對象，則應閱讀一些介紹 ADO.NET 的文章，包括 Rocky 為本專欄撰寫的名為 ADO.NET 與您一文。
簡而言之， DataSet 在 ADO.NET 中用作數據容器，并在與數據庫斷開連接時使用。 DataSet 包含一個或多個 DataTable ，每個 DataTable 都包含行集合。對于那些熟悉傳統 ADO 環境的用戶來說， DataTable 可被看作是斷開連接的 Recordset。
DataAdapter 在連接到數據庫時工作。單個 DataAdapter 的作用是使用數據庫中的數據填充某個 DataTable ，或將 DataTable 中的更改寫回到數據庫，或者二者兼而有之。
DataAdapter 要求 Command 對象執行各種數據庫操作。 Command 對象存放 SQL 語句或指定數據訪問實現方法的存儲過程名稱。每個 DataAdapter 有四個屬性，指定用于四種數據訪問類型之一的命令對象。
- SelectCommand ：此 Command 對象用于從數據庫中選擇數據。
- UpdateCommand ：此 Command 對象用于更新數據庫中的現有記錄。
- InsertCommand ：此 Command 對象用于向數據庫中插入新記錄。
- DeleteCommand ：此 Command 對象用于刪除數據庫中的現有記錄。
圖 1 闡釋了這些對象及其關系。
圖 1：用于訪問存儲過程的主要 ADO.NET 類以及它們之間的關系
到目前為止，您所看到的演示軟件示例可能將其 Command 對象配置為使用 SQL 語句進行數據訪問。實際上，某些示例可能完全跳過了 Command 對象的創建，這是因為 DataAdapter 的某個構造函數允許 Command 對象選擇后臺創建的數據。在使用存儲過程之前，讓我們運行這樣一個示例進行比較。
本文中的所有示例都使用 SQL Server 附帶的 Northwind 示例數據庫。我們還使用專門為 SQL Server 創建的 ADO.NET 類，而不是普通的 OLE DB 類。為了便于訪問這些 SQL Server 類，所有示例都需要在應用程序的代碼頂部加上以下代碼行：
Imports System.Data.SQLClient
現在，讓我們看看不使用存儲過程執行數據訪問的第一個示例。在此示例中，我們將在 Northwind 數據庫 Products 表中檢索所有產品。創建一個新 Windows 應用程序，在出現的空白 Form1 上，放置一個按鈕和一個 DataGrid。將 DataGrid 的 Anchor 屬性設置為全部四個邊，使之隨表單的擴展而擴展。在按鈕的 Click 事件中，放置以下代碼：
|
Dim sConnectionString As String = _
"server=localhost;uid=sa;pwd=;database=Northwind" Dim sSQL As String = "SELECT * FROM Products" Dim daGetProducts As New SqlDataAdapter(sSQL, sConnectionString) Dim dsProducts As New DataSet() daGetProducts.Fill(dsProducts, "Products") DataGrid1.DataSource = dsProducts.Tables("Products") |
根據計算機配置的不同，可能需要更改連接字符串。建立數據庫連接后，其余代碼應該可以正常運行。此演示軟件說明了填入和使用 DataSet 的最簡單方法。
請注意，代碼并不創建 Connection 對象或 Command 對象。事實上，沒有這些對象，ADO.NET 便無法工作，但它們是在后臺創建并使用的。實例化 SqlDataAdapter 的代碼行傳入 SQL 字符串（用于配置后臺 Command 對象）和連接字符串（用于配置后臺 Connection 對象）。
我們可以將此代碼更改為使用顯式 Connection 和 Command 對象，以便稍稍遠離演示軟件。在表單上再放置一個按鈕，并將以下代碼放到 Click 事件中：
|
Dim sConnectionString As String = _
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim daGetProducts As New SqlDataAdapter(cmdProducts)
|
此代碼通過顯式創建 Connection 和 Command 對象，并將這些對象附加到 DataAdapter，說明了 DataAdapters 的常用性。通過在實例化 DataAdapter 時傳入 cmdProducts，DataAdapter 的 SelectCommand 將自動設置。然后，可以立即使用 DataAdapter 訪問數據庫。
此代碼的結果與前一示例中的結果相同。盡管它有點接近真實軟件，但由于數據訪問是通過 SQL 語句實現的，因此仍然屬于演示軟件。
使用簡單存儲過程獲取數據
如何將此演示軟件更改為使用存儲過程？只需更改幾行代碼。在表單上再放置一個按鈕，并將以下代碼放到 Click 事件中：
|
Dim sConnectionString As String = _
Dim daGetProducts As New SqlDataAdapter(cmdProducts)
|
實例化 Command 對象時，此代碼不使用 SQL 語句并替換為要使用的存儲過程名稱。此外，Command 對象的 CommandType 屬性必須設置為 StoredProcedure。
此后的代碼與上一個示例非常相似，但它返回不同的數據。存儲過程查找十件最貴的產品，并只返回每個產品的名稱和價格。
帶輸入參數的存儲過程
此示例很簡單，因為存儲過程不需要任何輸入參數。也就是說，查找十件最貴的產品不需要任何外部信息。無需外界幫助，存儲過程即可完成此操作。然而，多數存儲過程都需要輸入參數來執行其功能。在下一個示例中，讓我們看看如何向存儲過程傳遞輸入參數。我們將使用 CustomerID 來獲取相關客戶的所有訂單，并使用名為 CustOrderHist 的存儲過程（已存在于 Northwind 數據庫中）。
在已使用的表單上再創建一個按鈕，并將以下代碼行放到按鈕的 Click 事件后面：
|
Dim sConnectionString As String = _
cmdOrders.Parameters.Add(prmCustomerID)
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
|
此代碼與上一個示例中的代碼非常相似，不同之處在于創建 Command 對象之后，為其配置了 Parameter 對象并將此對象添加到 Command 的參數集合中。在此示例中（更接近于演示軟件）將對客戶 ID 進行硬編碼，參數的 Value 屬性通常會設置為某些用戶輸入數據。但是，參數的其他屬性可以完全象此示例中那樣設置。
此示例中的所有參數設置都是顯式設置。某些開發人員喜歡這種樣式，因為它便于說明。但某些開發人員喜歡使用代碼行較少的等價方法：
|
Dim sConnectionString As String = _
cmdOrders.Parameters.Add(New _
Dim daGetOrders As New SqlDataAdapter(cmdOrders)
|
此代碼與上一示例的作用完全相同。但每個參數只需要兩行代碼，而不是六行。如果存儲過程包含大量參數（如后面某些示例所示），所需代碼行的多少就會有明顯區別，因此在后面部分，我們將使用此表單。
在Visual Basic .NET中使用存儲過程(2)
使用存儲過程更新數據庫
以上示例使用存儲過程從數據庫中提取信息。在復雜應用程序中使用存儲過程更新、插入和刪除記錄也很常見。讓我們看看如何使用 ADO.NET 完成該操作。
在第一個示例中，我們將使用 Visual Studio? .NET 中的向導編寫一個存儲過程集合，并創建使用這些過程的代碼。盡管我們只需在此示例中編寫最少量的代碼，但檢查向導創建的代碼有助于我們理解除獲取數據以外，有關與存儲過程交互操作的過程。
在此示例中，我們將使用 Northwind 示例數據庫中的 Customers 表。安裝后的 Northwind 數據庫中不包含用于更新、插入或刪除客戶的存儲過程，但 Visual Studio .NET 中的 DataAdapter Configuration Wizard（數據適配器配置向導）可輕松地為我們編寫一些存儲過程。
啟動新的 Windows Application （Windows 應用程序）項目。在空白的 Form1 上，放置一個 DataGrid 和兩個按鈕。和先前一樣，更改 DataGrid 的 Anchor 屬性使之錨定到全部四個邊。將按鈕命名為 btnFill 和 btnUpdate ，并分別將其 Text 屬性更改為 Fill 和 Update 。
轉到 Toolbox （工具箱）的 Data （數據）選項卡，將 SqlDataAdapter 控件拖動到窗體上，然后釋放鼠標。這將啟動 DataAdapter Configuration Wizard（數據適配器配置向導）。單擊 Next （下一步）按鈕開始向向導中輸入信息。
首先，需要選擇一個到 Northwind 數據庫的連接；如果列表中未顯示所需連接，則單擊 New Connection （新建連接）按鈕創建一個連接。然后單擊 Next （下一步）按鈕。
下一屏幕上將出現三種數據訪問方法。其外觀與圖 2 類似。
圖 2：選擇用于 DataAdapter 的數據訪問類型
此時，多數演示軟件示例選擇第一個選項來使用 SQL 語句。但是，我們將使用第二個選項，并讓向導為我們生成一些存儲過程。選擇 Create new stored procedures （創建新存儲過程）選項，然后單擊 Next （下一步）按鈕。
下一屏幕將請求 SQL 語句，指示最初從數據庫中提取的數據。但并不直接使用此 SQL 語句。SQL 語句中的信息將用于構造存儲過程，以便執行實際數據訪問。為使示例簡單起見，請輸入 SQL 語句
SELECT * FROM Customers
，然后按
Next
（下一步）按鈕。
此時，向導會請求要創建的存儲過程的名稱。操作共有四種 - Select、Update、Insert 和 Delete 操作。按以下方法對其命名：
- Select： MSDNSelectCustomers
- Update： MSDNUpdateCustomer
- Insert： MSDNInsertCustomer
- Delete： MSDNDeleteCustomer
選擇 Yes, create them in the database for me （是的，在數據庫中創建它們。）選項。此時，向導屏幕應該如圖 3 所示。
圖 3：命名要由 DataAdapter 向導創建的存儲過程
單擊 Next （下一步）按鈕。向導將創建存儲過程并在狀態欄屏幕上指示其進度。完成后，可單擊 Finish （完成）按鈕退出向導。
向導創建了配置完整的 DataAdapter ，但未創建 DataSet 來存放數據。這是我們下一步要做的。在 Toolbox （工具框）的 Data （數據）選項卡中，拖動 DataSet 控件。出現配置屏幕時，選擇 Untyped dataset （無類型的數據集）。
現在我們準備使用 DataAdapter 填充數據集。在 btnFill 的 Click 事件中，放入以下兩行代碼：
|
SqlDataAdapter1.Fill(DataSet1, "Customers")
DataGrid1.DataSource = DataSet1.Tables("Customers") |
在 btnUpdate 的 Click 事件中，放入以下代碼行：
SqlDataAdapter1.Update(DataSet1, "Customers")
現在我們有了一段使用存儲過程進行數據訪問的有效演示軟件。可以運行程序并單擊 Fill 按鈕獲取網格中的客戶列表。然后，可在窗格中編輯客戶記錄并選擇 Update 按鈕將更改放回到數據庫中。
注意： 編輯第一列（即 CustomerID ）時將出現異常，因為在 SQL Server 中不能更新數據庫記錄中的主鍵。
查看由向導生成的代碼會有所幫助，所有這些代碼最初都隱藏在 Windows Form Designer generated code（Windows 窗體設計器生成的代碼）區域中。單擊該區域的加號可展開代碼。注意以下代碼，它對所需的 SQLDataAdapter 和四個命令對象進行了實例化：
|
Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter()
Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand() Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand() Me.SqlUpdateCommand1 = New System.Data.SqlClient.SqlCommand() Me.SqlDeleteCommand1 = New System.Data.SqlClient.SqlCommand() |
此后的代碼配置每個命令對象并為其創建參數集合。此代碼與上一個示例相似，它們都使用帶參數的存儲過程。但向導生成的代碼使用參數的某些附加屬性，以使其與更改數據的存儲過程協同工作。例如，用于創建 SQLInsertCommand1 的 CompanyName 參數的代碼：
|
Me.SqlInsertCommand1.Parameters.Add(New _
System.Data.SqlClient.SqlParameter("@CompanyName", _ System.Data.SqlDbType.NVarChar, 40, "CompanyName")) |
在上一個示例中，我們只為參數名稱、數據類型和長度等設置了屬性
此代碼還會將參數的 SourceColumn 屬性設置為值 CompanyName 。該屬性指示 DataSet 的 Customers DataTable 中與此參數對應的字段。這使 DataTable 中的值在插入操作期間自動插入到參數的 Value 屬性中。讓我們來詳細介紹一下。
調用 SQLDataAdapter 的 Update 方法時，該方法將更新 DataSet 中的單個 DataTable 。當逐行檢查 DataTable 時，會查找需要更新、插入或刪除的行。當找到需要插入到數據庫中的行后， SQLDataAdapter 將使用由其 InsertCommand 屬性設置的 Command 對象。這種情況下， Command 對象將訪問 MSDNInsertCustomer 存儲過程。
在該存儲過程運行前，每個參數的 Value 屬性都必須從插入的行中導入。配置 SQLDataAdapter1 的代碼將存儲過程的每個參數與 DataTable 中的相應字段相關聯。這樣，新 DataTable 行中的數據將自動傳輸到存儲過程的參數。
其他存儲過程參數的配置方法與此相似。但有一個不同之處值得注意。其他存儲過程傳入 DataTable 中數據的原始值，這些值用于檢查數據是否在您不知情的情況下發生了更改。也就是說，如果您提取了某些數據，而其他人在您嘗試更新前更改了該數據，您將收到并發異常。啟動以上程序、提取客戶，然后使用工具（例如 SQL Enterprise Manager）更改記錄中的內容，便可以看到這種情況的發生。如果您在示例程序中更改同一記錄并嘗試進行更新，則會收到并發異常。
在Visual Basic .NET中使用存儲過程(3)
從存儲過程返回值
以上示例有一個不足之處。Northwind Customers 表使用數字字母形式的主鍵，并且必須由插入數據的應用程序生成。也就是說，如果使用以上程序插入新記錄，則必須為 CustomerID 自行創建由五個字符組成的值。
在真實軟件中，為新記錄自動生成主鍵更為常見。主鍵通常是按順序分配的長整數。
為新記錄設置主鍵有兩種基本技術。應用程序可調用生成下一個可用 ID 的存儲過程，然后將此 ID 直接放到 DataSet 的新行中。或者，用于插入記錄的存儲過程可以為記錄派生新 ID，然后將其作為返回值傳遞回應用程序。
第一種技術需要一點額外的邏輯來獲取新 ID 并將其放到新記錄的相應位置。使用存儲過程執行插入操作與以上示例類似。
但第二種技術要求在存儲過程中使用一種新型參數。到目前為止我們見到的所有參數都是默認類型，即輸入參數。實際上參數分四種類型：
Input InputOutput Output ReturnValue
| 此參數只用于將信息從應用程序傳輸到存儲過程。 |
| 此參數可將信息從應用程序傳輸到存儲過程，并將信息從存儲過程傳輸回應用程序。 |
| 此參數只用于將信息從存儲過程傳輸回應用程序。 |
| 此參數表示存儲過程的返回值。SQL Server 的存儲過程參數列表中不顯示該參數。它只與存儲過程的 RETURN 語句中的值相關聯。 |
存儲過程為主鍵生成新值后，通常使用存儲過程中的 RETURN 語句返回該值，因此用來訪問該值的參數類型是 ReturnValue 參數。
ReturnValue 參數與其他類型的參數有一個重要的區別。通常，在 ADO.NET 中為 Command 對象配置的參數的順序并不重要。參數名稱只用來與存儲過程中相應的參數相匹配。但是，對于 ReturnValue 參數，它必須是列表中的第一個參數。
也就是說，為 Command 對象配置 ReturnValue 參數時，必須首先在代碼中配置該參數，這樣它才能獲取集合中的第一個數字索引。如果先配置任何其他參數， ReturnValue 參數將不能正常工作。
為了說明帶返回值的存儲過程的用法，我們編寫一個在 Northwind Products 表中插入記錄的示例。此表被設置為使用 Identity 列自動創建新產品 ID。遺憾的是，Northwind 示例數據庫不包含執行所需操作的存儲過程，所以在完成示例其余部分之前，我們需要向數據庫插入一個這樣的存儲過程。
轉到 Visual Studio .NET 中的 Server Explorer （服務器資源管理器）。打開 SQL Server 的節點，打開 SQL Server 實例的節點，然后打開 Northwind 數據庫的節點。
右鍵單擊 Stored Procedures（存儲過程）節點，選擇 New Stored Procedure（新建存儲過程）。在出現的編輯窗口中，用以下文本替換其中的所有文本：
|
ALTER PROCEDURE dbo.MSDNInsertProduct
SET NOCOUNT OFF;
RETURN @ProductID |
現在關閉編輯窗口，當系統詢問您是否要保存更改時，單擊 Yes（是）。現在存儲過程就已保存到數據庫中，并被命名為 MSDNInsertProduct。
現在便可以編寫代碼來使用此存儲過程。新建 Windows 應用程序，在空白 Form1 上，放置錨定到所有四個邊的 DataGrid，還需添加名為 btnFill 和 btnInsertProduct 的兩個按鈕。將 btnFill 的 Text 屬性設置為 Fill，將 btnInsertProduct 的 Text 屬性設置為 Insert Product。
在 btnFill 的 Click 事件中，放置以下代碼：
|
Dim sConnectionString As String = _
"server=localhost;uid=sa;pwd=;database=Northwind" Dim sSQL As String = "SELECT * FROM Products" Dim daGetProducts As New SqlDataAdapter(sSQL, sConnectionString) Dim dsProducts As New DataSet() daGetProducts.Fill(dsProducts, "Products") DataGrid1.DataSource = dsProducts |
它與本文前面所講的代碼大致相同，所以我們不再贅述。不要忘記必要時更改連接字符串，并在項目代碼的頂部為 SQLClient 命名空間放置 Imports 語句。然后在 btnInsertProduct 的 Click 事件中放置以下代碼：
|
Dim sConnectionString As String = _
cmdInsertProduct.Parameters.Add(New SqlParameter("@ProductName", _
Dim daInsertProduct As New SqlDataAdapter()
Dim drNewProduct As DataRow
daInsertProduct.Update(dsProducts.Tables("Products")) MsgBox(drNewProduct.Item("ProductID")) |
此代碼與如上所示的代碼類似，只是為返回值配置參數的代碼行不同。請注意，它是第一個參數，并被設置為將返回值放回到 ProductID 字段中。
用于向數據集中插入新行的代碼是標準 ADO.NET 代碼，所以我們就不再贅述。它為產品記錄創建一行新的適當結構（使用產品 DataTable 的 NewRow 方法），然后將數據放入行中，最后向產品 DataTable 的 Rows 集合中添加行。
現在運行程序進行測試。單擊 Fill 按鈕，但不對網格中的數據進行任何更改。然后按 Insert Product 按鈕。將插入 Billy's Sesame Oil 的新產品記錄，并且出現的消息框會通知您為其返回的 ProductID。還可以打開網格中的 Products 表，滾動到底部，并看到已添加了新行。
使用 Server Explorer（服務器資源管理器）編寫參數代碼
以上代碼編寫起來既冗長又繁瑣。但是，DataAdapter Configuration Wizard（數據適配器配置向導）提示可以使用 Visual Studio 為我們編寫此代碼。DataAdapter Configuration Wizard（數據適配器配置向導）為完整配置所需的四個存儲過程（分別是 Select、Update、Insert 和 Delete）生成了代碼。假設您象以上示例一樣只需要一個存儲過程的代碼，可以將其截短。要獲得只與一個存儲過程通信的預先編寫好的代碼，只需展開 Server Explorer（服務器資源管理器）以顯示需要訪問的存儲過程，然后將該存儲過程拖到設計界面上。將看到為該存儲過程創建的 DataAdapter 和 Command 對象，代碼的設計器部分包含為該存儲過程配置參數所需的所有代碼。可以按原樣使用該代碼，也可以根據需要復制并調整后使用。
小結
本文中的示例仍是演示軟件，但至少足以向您說明如何訪問存儲過程，以便您開始編寫自己的真實軟件。當然，您需要了解要訪問的存儲過程，并且可能需要向數據庫管理員 (DBA) 或其他組員咨詢以獲取該信息。
對于復雜系統，存儲過程有許多優勢。希望您在本文中學到了足夠的知識，可以不必擔心如何開始使用它們。第一次嘗試編寫代碼時，您可能希望使用 DataAdapter Wizard（DataAdapter 向導）或 Server Explorer（服務器資源管理器）。但如果您能在必要時自行編寫訪問代碼，則可以更有效地使用存儲過程。
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
