用vb.net操作access存儲過程（2）

2024-07-10 13:02:50

字體：大中小

供稿：網(wǎng)友

在第一部分，我們已經(jīng)知道了如何利用ado.net和vb.net建立access中的存儲過程，這篇我們需要了解如何利用這些已經(jīng)建立好的存儲過程。
我們將程序做成公共類dbtier，這樣就可以在其他程序中調(diào)用了。
首先，幾個命名空間必不可少。
imports system
imports system.data
imports system.data.oledb
數(shù)據(jù)庫鏈接字符串

shared connectionstring as string = _    "provider=microsoft.jet.oledb.4.0;data source=c:/program " _    & "files/microsoft office/office10/samples/northwind.mdb"

productslist（）返回dataset類型( 存儲過程執(zhí)行結(jié)果)
productsadditem()添加存儲過程參數(shù)
完整代碼：
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國內(nèi)最大的酷站演示中心!

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

下一篇：在vs.net bate 2中的ado.net簡單編程（傻瓜版）