用vb.net操作access存储过程（2）

在第一部分，我们已经知道了如何利用ado.net和vb.net建立aclearcase/" target="_blank" >ccess中的存储过程，这篇我们需要了解如何利用这些已经建立好的存储过程。
我们将程序做成公共类DBTier，这样就可以在其他程序中调用了。
首先，几个命名空间必不可少。
Imports System
Imports System.Data
Imports System.Data.OleDb
数据库链接字符串Shared connectionString As String = _    "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program " _    & "Files\Microsoft Office\Office10\Samples\Northwind.mdb"

ProductsList（）返回dataset类型( 存储过程执行结果)
ProductsAddItem()添加存储过程参数
完整代码：
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)

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

        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

        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()

    End Sub

End Class

原文转自：http://www.ltesting.net