IDC资讯中心
在第一部分，我们已经知道了如何利用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
