在VB.NET中使用MS Access存储过程-技术开发专区

在VB.NET中使用MS Access存储过程

作者：晓熊编辑：李博（泡泡） 2008-07-16 21:27

【IT168 技术教程】

介绍

　　存储过程，一个通常和企业数据库系统例如SQLServer相关的功能，也可以在Access里找到。自从Access2000以来，Access已经具备了存储过程的功能，而且是Jet 4 Database Engine自带的。如果你习惯于在SQL SERVER里使用存储过程，那么你就会很熟悉它们如何在Access里使用。但是要记住有一些限制。

存储过程如何在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

　　数据库层的主要目的是通过类模块提供一个访问数据库的网关。这个类模块将充当数据库和应用程序之间的粘合剂。利用数据库访问层来访问数据库有2个优点：你可以有改变你的后台数据库技术（从Access改到SQL Server)而不影响应用系统的能力。你还能够通过在应用程序和数据库访问层之间增加一个控制层，来保证传过去的数据是“纯净”的。在.NET里，数据库访问层通常会包括一个遵循面向对象规范的类模块，而Visual Basic 的较早的版本会使用一个标准模块来处理。

数据库访问层 - 代码

　　现在该是我们卷起袖子来看一些代码的时候了。在添加一个空类以后的第一件事情，就是列出这里需要使用的.NET类库，如下所示：

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

　　System 库对大多数程序来说是标准的, 但是我把它作为一个习惯，在所有的代码中都包含这个类库。而System.Data 库则是一个对于大多数数据库访问程序都需要的库。System.Data.OleDb 将用在访问Access所需要的OLE DB Provider。如果我们需要使用SQL Server，则我们最好使用定制的SQL Provider System.Data.SqlClient.
下一行开始了类的定义
Public Class DBTier

　　这里我们定义类名为 DBTier，并且给他Public 修饰符, 因此它将可以被其他代码模块访问。在类定义以后，将声明所有要用到的属性。

Shared connectionString As String = _
    "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program " _
    & "Files\Microsoft Office\Office10\Samples\Northwind.mdb"

　　这里只声明了一个String属性,connectionString. 这个变量保存了 Northwind Access数据库的连接字符串。声明这个变量为Shared,说明它是一个“类变量（Class Variable）”，一个class variable是和类关联的,二不是和这个类产生的每个对象相关联。（VB.Net的Shared 修饰符相当于C++或C#的Static修饰符）
　　在连接字符串的定义之后，你可以看到这里有3个过程和1个函数。函数返回了一个包含所有产品列表的dataset。它调用了在第一部分已经创建的存储过程 procProductsList。
　　然后你可以看到3个过程。他们对应于每个存储过程，用于增加、删除、修改产品；它们都有类似的结构；每个使用了一个command,并声明了连接对象和必须的参数。作为一个例子, 我们来分开讨论ProductsDeleteItem过程。理解了这个过程，其他2个就很容易消化了。
　　一开始，这个过程使用了一个参数, ProductID, 表示需要删除的产品的ID。

Sub ProductsDeleteItem(ByVal ProductID As Integer)

　　接着，声明了所有的变量。分别用于存储过程将要使用的connection，command和parameter。这个参数就是需要删除的那格产品ID。

Dim con As OleDbConnection
Dim cmd As OleDbCommand = New OleDbCommand()
Dim paramProductID As New OleDbParameter()
Command和connection的初始化:
con = New OleDbConnection(connectionString)
cmd.Connection = con

　　确认了paramProductID 参数的属性，然后这个参数被添加到Command对象. 在这个例子中，要用到存储过程里的参数名字是inProductID, 它是一个整型变量，并用函数的参数进行赋值。

With paramProductID
    .ParameterName = "inProductID"
    .OleDbType = OleDbType.Integer
    .Size = 4
    .Value = ProductID
End With
cmd.Parameters.Add(paramProductID)

　　最后一步是真正调用存储过程。

cmd.CommandText = "EXECUTE procProductsDeleteItem"
con.Open()
cmd.ExecuteNonQuery()
con.Close()

　　注意connection对象这里只在需要执行存储过程的时候保留，然后就马上关闭了。这将减少可能有的资源占用。
　　虽然这个例子中使用的DBTier类已经清楚的介绍了如何使用Access存储过程, 它的功能仍然需要更多的增强来达到产品级的水平。因为没有错误处理。他仍然需要更多的强化。
　　本文的源代码包括了DBTier.vb,这个文件同时包含了一些简单的Form来测试类的实现。

　　总而言之，我希望您至少通过这些文章获得了2个信息：一个是在Microsoft Access中存储过程是存在的并且是不错的, 虽然有不足。第二个你需要同时理解需要把应用程序的数据库访问分解到独立的类、函数、过程里面，这将使软件的维护和升级变得更加容易。

完整的DBTier.vb：

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

' Functions and subroutines for executing Stored Procedures in Access.
Public Class DBTier

    ' Change Data Source to the location of Northwind.mdb on your local
    ' system.
    Shared connectionString As String = _
        "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program " _
        & "Files\Microsoft Office\Office10\Samples\Northwind.mdb"
    ' This function returns a dataset containing all records in
    ' the Products Table.
    Function ProductsList() As DataSet
        Dim con As OleDbConnection
        Dim da As OleDbDataAdapter
        Dim ds As DataSet
        Dim sSQL As String

sSQL = "EXECUTE procProductsList"

        con = New OleDbConnection(connectionString)
        da = New OleDbDataAdapter(sSQL, con)
        ds = New DataSet()
        da.Fill(ds, "Products")

Return ds

End Function

    ' This Function adds one record to the Products table.
    Sub ProductsAddItem(ByVal ProductName As String, _
        ByVal SupplierID As Integer, ByVal CategoryID As Integer)
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand()
        Dim paramProductName As New OleDbParameter()
        Dim paramSupplierID As New OleDbParameter()
        Dim paramCategoryID As New OleDbParameter()

con = New OleDbConnection(connectionString)
cmd.Connection = con

        With paramProductName
            .ParameterName = "inProductName"
            .OleDbType = OleDbType.VarChar
            .Size = 40
            .Value = ProductName
        End With
        cmd.Parameters.Add(paramProductName)

        With paramSupplierID
            .ParameterName = "inSupplierID"
            .OleDbType = OleDbType.Integer
            .Size = 4
            .Value = SupplierID
        End With
        cmd.Parameters.Add(paramSupplierID)

        With paramCategoryID
            .ParameterName = "inCategoryID"
            .OleDbType = OleDbType.Integer
            .Size = 4
            .Value = CategoryID
        End With
        cmd.Parameters.Add(paramCategoryID)

        cmd.CommandText = "EXECUTE procProductsAddItem"
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()

End Sub

    ' This function Updates a specific JobTitle Record with new data.
    Sub ProductsUpdateItem(ByVal ProductID As Integer, _
        ByVal ProductName As String)
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand()
        Dim paramProductName As New OleDbParameter()
        Dim paramProductID As New OleDbParameter()

con = New OleDbConnection(connectionString)
cmd.Connection = con

        With paramProductID
            .ParameterName = "inProductID"
            .OleDbType = OleDbType.Integer
            .Size = 4
            .Value = ProductID
        End With
        cmd.Parameters.Add(paramProductID)

        cmd.CommandText = "EXECUTE procProductsUpdateItem"
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()

End Sub

    ' This function deletes one record from the Products table.
    Sub ProductsDeleteItem(ByVal ProductID As Integer)
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand = New OleDbCommand()
        Dim paramProductID As New OleDbParameter()

con = New OleDbConnection(connectionString)
cmd.Connection = con

        cmd.CommandText = "EXECUTE procProductsDeleteItem"
        con.Open()
        cmd.ExecuteNonQuery()
        con.Close()

End Sub

End Class

关注我们