在 Visual Basic .NET 中使用預存程序(1)

來源:互聯網
上載者:User
visual|預存程序 在 Visual Basic .NET 中使用預存程序
Billy Hollis
2002年9月14日

從 MSDN Code Center 下載 StoredProcVB.NET.exe 樣本檔案(英文)。(http://msdn.microsoft.com/code/default.asp?url=/code/sample.asp?url=/msdn-files/026/002/872/msdncompositedoc.xml)

摘要: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 = _
    "server=localhost;uid=sa;pwd=;database=Northwind"
Dim sSQL As String = "SELECT * FROM Products"

Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdProducts As New SqlCommand(sSQL, cnNorthwind)

Dim daGetProducts As New SqlDataAdapter(cmdProducts)
Dim dsProducts As New DataSet()
daGetProducts.Fill(dsProducts, "Products")
DataGrid1.DataSource = dsProducts.Tables("Products")

此代碼通過顯式建立 Connection 和 Command 對象,並將這些對象附加到 DataAdapter,說明了 DataAdapters 的常用性。通過在執行個體化 DataAdapter 時傳入 cmdProducts,DataAdapter 的 SelectCommand 將自動化佈建。然後,可以立即使用 DataAdapter 訪問資料庫。

此代碼的結果與前一樣本中的結果相同。儘管它有點接近真實軟體,但由於資料訪問是通過 SQL 陳述式實現的,因此仍然屬於示範軟體。

使用簡單預存程序擷取資料
如何將此示範軟體更改為使用預存程序?只需更改幾行代碼。在表單上再放置一個按鈕,並將以下代碼放到 Click 事件中:

Dim sConnectionString As String = _
    "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdProducts As New _
    SqlCommand("十件最貴的產品", cnNorthwind)
cmdProducts.CommandType = CommandType.StoredProcedure

Dim daGetProducts As New SqlDataAdapter(cmdProducts)
Dim dsProducts As New DataSet()
daGetProducts.Fill(dsProducts, "Products")
DataGrid1.DataSource = dsProducts.Tables("Products")

執行個體化 Command 對象時,此代碼不使用 SQL 陳述式並替換為要使用的預存程序名稱。此外,Command 對象的 CommandType 屬性必須設定為 StoredProcedure。

此後的代碼與上一個樣本非常相似,但它返回不同的資料。預存程序尋找十件最貴的產品,並只返回每個產品的名稱和價格。

帶輸入參數的預存程序
此樣本很簡單,因為預存程序不需要任何輸入參數。也就是說,尋找十件最貴的產品不需要任何外部資訊。無需外界協助,預存程序即可完成此操作。然而,多數預存程序都需要輸入參數來執行其功能。在下一個樣本中,讓我們看看如何向預存程序傳遞輸入參數。我們將使用 CustomerID 來擷取相關客戶的所有訂單,並使用名為 CustOrderHist 的預存程序(已存在於 Northwind 資料庫中)。

在已使用的表單上再建立一個按鈕,並將以下程式碼放到按鈕的 Click 事件後面:

Dim sConnectionString As String = _
    "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure
' 為預存程序設定參數
Dim prmCustomerID As New SqlParameter()
prmCustomerID.ParameterName = "@CustomerID"
prmCustomerID.SqlDbType = SqlDbType.VarChar
prmCustomerID.Size = 5
prmCustomerID.Value = "ALFKI"

cmdOrders.Parameters.Add(prmCustomerID)

Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

此代碼與上一個樣本中的代碼非常相似,不同之處在於建立 Command 對象之後,為其配置了 Parameter 對象並將此對象添加到 Command 的參數集合中。在此樣本中(更接近於示範軟體)將對客戶 ID 進行寫入程式碼,參數的 Value 屬性通常會設定為某些使用者輸入資料。但是,參數的其他屬性可以完全象此樣本中那樣設定。

此樣本中的所有參數設定都是顯式設定。某些開發人員喜歡這種樣式,因為它便於說明。但某些開發人員喜歡使用程式碼較少的等價方法:

Dim sConnectionString As String = _
    "server=localhost;uid=sa;pwd=;database=Northwind"
Dim cnNorthwind As New SqlConnection(sConnectionString)
Dim cmdOrders As New SqlCommand("CustOrderHist", cnNorthwind)
cmdOrders.CommandType = CommandType.StoredProcedure

cmdOrders.Parameters.Add(New _
    SqlParameter("@CustomerID", SqlDbType.VarChar, 5))
cmdOrders.Parameters("@CustomerID").Value = "ALFKI"

Dim daGetOrders As New SqlDataAdapter(cmdOrders)
Dim dsOrders As New DataSet()
daGetOrders.Fill(dsOrders, "Orders")
DataGrid1.DataSource = dsOrders.Tables("Orders")

此代碼與上一樣本的作用完全相同。但每個參數只需要兩行代碼,而不是六行。如果預存程序包含大量參數(如後面某些樣本所示),所需程式碼的多少就會有明顯區別,因此在後面部分,我們將使用此表單。

使用預存程序更新資料庫
以上樣本使用預存程序從資料庫中提取資訊。在複雜應用程式中使用預存程序更新、插入和刪除記錄也很常見。讓我們看看如何使用 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)更改記錄中的內容,便可以看到這種情況的發生。如果您在樣本程式中更改同一記錄並嘗試進行更新,則會收到並發異常。



相關文章

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。