vb.net操作access2007類

Imports System.Data.OleDb

Public Class DataProcess

    Public Function getconn() As OleDbConnection

        Dim str As String

        str = "Provider=Microsoft.ACE.OLEDB.12.0;jet oledb:database password = 123;User ID=; Data Source=" & Application.StartupPath() & "\ProductInfo.accdb"

        Dim dbConnection As OleDbConnection = New OleDbConnection(str)

        Try

            dbConnection.Open()

        Catch Ex As Exception

            MsgBox(Err.Description, MsgBoxStyle.Information, "打開資料庫連接配接失敗！")

        End Try

        Return dbConnection

    End Function

    '執行update,delete,insert時的函數

    Public Sub trancactsql(ByVal Strsql As String)

        Dim dbConnection As OleDbConnection = getconn()

        If dbConnection.State.ToString = "Closed" Then

            MsgBox(Chr(13) & " access 資料庫連接配接失敗 " & Chr(13), MsgBoxStyle.Information, "警告")

            Exit Sub

        End If

        Dim oledbcmd As OleDbCommand = New OleDbCommand(Strsql, dbConnection)

        oledbcmd.ExecuteNonQuery()

        oledbcmd.Dispose()

        dbConnection.Close()

        dbConnection.Dispose()

    End Sub

    '傳回dataset資料集對象，用于select語句，myds.tables[“表名”]

    Public Function getds(ByVal sqlstr As String, ByVal strtable As String) As DataSet

        Dim dbConnection As OleDbConnection = getconn()

        If dbConnection.State.ToString = "Closed" Then

            MsgBox(Chr(13) & " access 資料庫連接配接失敗 " & Chr(13), MsgBoxStyle.Information, "警告")

            Return Nothing

        End If

        Dim sqlda As OleDbDataAdapter = New OleDbDataAdapter(sqlstr, dbConnection)

        Dim myds As DataSet = New DataSet()

        sqlda.Fill(myds, strtable)

        myds.Dispose()

        Return myds

    End Function

    '傳回sqldatareader對象；sqlread[字段名].tostring()讀取

    Public Function getreader(ByVal sqlstr As String) As OleDbDataReader

        Dim dbConnection As OleDbConnection = getconn()

        If dbConnection.State.ToString = "Closed" Then

            MsgBox(Chr(13) & " access 資料庫連接配接失敗 " & Chr(13), MsgBoxStyle.Information, "警告")

            Return Nothing

        End If

        Dim oledbcmd As OleDbCommand = New OleDbCommand(sqlstr, dbConnection)

        Dim sqlreader As OleDbDataReader = oledbcmd.ExecuteReader(CommandBehavior.CloseConnection)

        Return sqlreader

    End Function

End Class