当前位置：首页 > 网络编程 > WEB编程 > ASP.net > 用vb.net操作access存储过程（2）

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

点击次数：35 次 发布日期：2008-11-27 01:25:08 作者：源代码网
源代码网推荐 在第一部分，我们已经知道了如何利用ado.net和vb.net建立access中的存储过程，这篇我们需要了解如何利用这些已经建立好的存储过程。
我们将程序做成公共类DBTier，这样就可以在其他程序中调用了。
首先，几个命名空间必不可少。
Imports System
Imports System.Data
Imports System.Data.OleDb
数据库链接字符串
Shared connectionString As String = _    "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:Program " _    & "FilesMicrosoft OfficeOffice10SamplesNorthwind.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 " _
        & "FilesMicrosoft OfficeOffice10SamplesNorthwind.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



源代码网供稿.
网友评论 (0)
我也要写评论
会员中心
网络编程
本站推荐
网络编程之精华