在VB.NET中使用MS Access存儲過程 — 第一部份


作者： DavidWasserman，MCP
2/27/2002
譯者：Qianqian
(thousandvb@yeah.net)3/8/2002

文章源代碼: msaccess_ sp.zip

介紹

最新發布的MS Access中，微軟已經盡努力使這種產品成為一個真正全功能的關系數據庫系統。 存儲過程，一個通常和企業數據庫系統例如SQLServer相關的功能，現在也可以在Access里找到了。 自從Access2000以來，Access已經具備了存儲過程的功能，而且是Jet 4 Database Engine自帶的。如果你習慣于在SQL SERVER里使用存儲過程，那么你就會很熟悉它們如何在Access里使用。 但是要記住有一些限制。我會稍后作討論。

這篇文章分成兩個部份。 第一部份會詳細描述如何在使用ADO.NET和Visual Basic.NET在Access里創建存儲過程。 第二部份則會示范如何利用在第一部分創建的存儲過程來創建一個數據訪問層，并且在你自己的應用程序中使用。 這篇文章中的代碼已經在Access2002種測試通過，即使如此，它也應該能運行于Access2000。

存儲過程如何在Access里工作?

不像在Access里的其它的對象，存儲過程沒有用戶界面，并且不能在Access的界面里創建。 要建立它們的方法只有編碼。我將示范如何在ADO.NET中實現這些代碼。

當一個存儲過程被添加到Access數據庫時，JET Engine會把存儲過程轉換到一個查詢對象。 對一個Access開發者而言，這就象編寫一個簡單的查詢，是不必要的工作。 然而，它確實有它的優點。考慮一下，一個應用系統必須為了分開Access版和SQL SERVER版而分成2個系統的情況吧。 使用存儲過程會使為應用程序的數據庫訪問層寫代碼變得更容易，程序在不同的版本之間區別很小。

創建存儲過程

為了示范，我會先顯示如何使用SQL語句來創建存儲過程。 在文章末尾我會給列出需要在數據庫執行的所有語句的代碼。 當使用和Access包含的Northwind數據庫時，它會創建4個存儲過程。 它們都集中于Prodcut表，讓我們從最容易的那個開始: 選擇表格里所有行的數據。 為了創建這個存儲過程，在數據庫執行下列SQL語句:

"CREATE PROC procProductsList AS SELECT * FROM Products;"

語句“CREATE PROC procCustomerList”是實際上創建存儲過程的部份。 在”AS”下面的部分可以為任何有效的SQL語句。

經常性的，你會需要傳遞參數到存儲過程去用于查詢。 例如你可能想要根據某一個ProductID刪除一個記錄。 下列存儲過程顯示如何實現這個目的:

"CREATE PROC procProductsDeleteItem(inProductsID LONG)" & _
"AS DELETE FROM Products WHERE ProductsID = inProductsID;"

在第一行，注意CREATE PROC聲明右邊的括號。 有一個定義為長整型的參數。 這是需要你輸入的變量，用于刪除記錄。

下項兩個分開的語句顯示了如何為Product表創建一個add和一個update儲存過程。注意，為了文章的簡短，過程中沒有包括所有的字段:

"CREATE PROC procProductsAddItem(inProductName VARCHAR(40), " & _
"inSupplierID LONG, inCategoryID LONG) " & _
"AS INSERT INTO Products (ProductName, SupplierID, CategoryID) " & _
"Values (inProductName, inSupplierID, inCategoryID);"

"CREATE PROC procProductsUpdateItem(inProductID LONG, " & _
" inProductName VARCHAR(40)) " & _
"AS UPDATE Products SET ProductName = inProductName " & _
"WHERE ProductID = inProductID;

注意：當參數數量超過一個的時候，用逗號分開。

限制

在這里，你可能會碰到一些限制，尤其是如果當你已經非常習慣于SQL SERVER的強大功能的時候。

• 
  不能使用輸出參數。
• 
  不要使用@字符。 @字符通常用于Transaction SQL( SQL SERVER)，代表一個本地變量 。 Access不總是會轉換這個字符， 有時會將它省略。這個問題可能導致很難找的BUG, 會使你多掉幾把頭發。
• 
  Access無法存取臨時表。
  
• 
  我懷疑在很多Transaction SQL里有的選項在Access里是沒有的，因為它不是Transaction SQL兼容的。

Imports System
Imports System.Data
Imports System.Data.OleDb

Module CreateSP

Sub Main()

ProductsProcs()

End Sub

' Products Stored Procs to be added to the db.
Sub ProductsProcs()
Dim sSQL As String

' procProductsList - Retrieves entire table
sSQL = "CREATE PROC procProductsList AS SELECT * FROM Products;"
CreateStoredProc(sSQL)

' procProductsDeleteItem - Returns the details (one record) from the 
' JobTitle table
sSQL = "CREATE PROC procProductsDeleteItem(@ProductID LONG) AS " _
& "DELETE FROM Products WHERE ProductID = @ProductID;"
CreateStoredProc(sSQL)

' procProductsAddItem - Add one record to the JobTitle table
sSQL = "CREATE PROC procProductsAddItem(inProductName VARCHAR(40), " _
& "inSupplierID LONG, inCategoryID LONG) AS INSERT INTO " _
& "Products (ProductName, SupplierID, CategoryID) Values " _
& "(inProductName, inSupplierID, CategoryID);"
CreateStoredProc(sSQL)

' procProductsUpdateItem - Update one record on the JobTitle table
sSQL = "CREATE PROC procProductsUpdateItem(inProductID LONG, " _
& "inProductName VARCHAR(40)) AS UPDATE Products SET " _
& "ProductName = inProductName WHERE ProductID = inProductID;"
CreateStoredProc(sSQL)


End Sub

' Execute the creation of Stored Procedures
Sub CreateStoredProc(ByVal sSQL As String)
Dim con As OleDbConnection
Dim cmd As OleDbCommand = New OleDbCommand()
Dim da As OleDbDataAdapter

' Change Data Source to the location of Northwind.mdb on your local 
' system.
Dim sConStr As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data " _
& "Source=C:\Program Files\Microsoft " _
& "Office\Office10\Samples\Northwind.mdb"

con = New OleDbConnection(sConStr)

cmd.Connection = con
cmd.CommandText = sSQL

con.Open()
cmd.ExecuteNonQuery()
con.Close()

End Sub


End Module