用vb.net设置access存储过程（2）

在第一部分，我们已经知道了如何利用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 " _& "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

……