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

2024-07-10 13:02:50

字体：大中小

来源：转载

供稿：网友

在第一部分，我们已经知道了如何利用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国内最大的酷站演示中心!

上一篇：用vb.net操作ms access存储过程（1）

下一篇：在vs.net bate 2中的ado.net简单编程（傻瓜版）