dbmanager.vb

vb.net写的图书管理系统,功能完备,能正常使用,可用于二次开发.

Imports System.Data.OleDb


Module DBManager
    Private OleDBConn As OleDb.OleDbConnection
    Private strConnect As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=BookBorrowMS.mdb"
    Private cmd As OleDb.OleDbCommand
    Dim trac As OleDb.OleDbTransaction
    Sub New()
        OleDBConn = New System.Data.OleDb.OleDbConnection(strConnect)
        cmd = New OleDb.OleDbCommand
    End Sub
    '根据指定条件从数据库表中取得某个字段的值
    Public Function getField(ByVal wantedField As String, ByVal condition As String, ByVal table As String) As String
        Dim dtSet As DataSet
        Dim strSQL As String
        '根据传入的参数，生成SQL语句
        strSQL = "select " & wantedField & " from " & table & " where " & condition
        '执行SQL语句，取出结果集
        dtSet = DBManager.executeSelectQuery(strSQL, "getField")
        '若结果集为空，返回空字符串
        If dtSet Is Nothing OrElse dtSet.Tables(0).Rows.Count = 0 Then
            Return ""
        Else
            '否则返回字段值
            Return dtSet.Tables("getField").Rows(0).Item(wantedField) & ""
        End If
    End Function
    '根据代码表表名得到代码值与代码描述
    Public Function getCodeByTable(ByVal code As String, ByVal description As String, ByVal table As String) As ArrayList
        Dim dtSet As DataSet
        Dim strSQL As String
        Dim i As Integer
        '记录结果
        Dim result As New ArrayList
        '根据传入的参数，生成SQL语句
        strSQL = "select " & code & "," & description & " from " & table
        '执行SQL语句，取出结果集
        dtSet = DBManager.executeSelectQuery(strSQL, "getField")
        '若结果集为空，返回空
        If dtSet Is Nothing OrElse dtSet.Tables(0).Rows.Count = 0 Then
            Return Nothing
        End If
        '将数据写入result中
        For i = 0 To dtSet.Tables(0).Rows.Count - 1
            With dtSet.Tables(0).Rows(i)
                Dim content(2) As String
                '代码值
                content(0) = .Item(code) & ""
                '代码描述
                content(1) = .Item(description) & ""
                result.Add(content)
            End With
        Next
        Return result
    End Function

    '********************************************
    'Public Function executeSelectQuery
    ' 
    '********************************************
    Public Function executeSelectQuery(ByVal SQLString As String, ByVal strTableName As String) As DataSet
        Dim dtset As New DataSet
        Dim cmd As New OleDbCommand
        Try
            cmd.Connection = OleDBConn
            cmd.CommandText = SQLString
            Dim oDataAdapter As New OleDbDataAdapter(cmd)
            oDataAdapter.Fill(dtset, strTableName)
            Return dtset
        Catch e As Exception
            Debug.WriteLine("executeException --" + e.ToString())
            Return Nothing
        Finally
            Debug.WriteLine("Select-- SQL is --" + SQLString)
        End Try

    End Function

    '********************************************
    'Public Function executeNonQuery
    '
    '********************************************
    Public Function executeNonQuery(ByVal SQLString As String) As Boolean
        Try
            cmd.Connection = OleDBConn
            cmd.CommandText = SQLString
            OleDBConn.Open()
            cmd.ExecuteNonQuery()
            OleDBConn.Close()
            Return True
        Catch e As SqlClient.SqlException
            Debug.WriteLine("executeException --" & e.ToString())
            Return False
        Catch e As Exception
            Debug.WriteLine("executeException --" & e.ToString())
            Return False
        Finally
            Debug.WriteLine("executeNonQuery - SQL is -" & SQLString)
            OleDBConn.Close()
        End Try
    End Function

    '********************************************
    'Public Function executeNonQuery
    '
    '********************************************
    Public Function executeNonQueryArrayList(ByVal arrSQL As ArrayList) As Boolean
        Try
            Dim i As Integer
            Dim SQLString As String
            cmd.Connection = OleDBConn
            OleDBConn.Open()
            trac = cmd.Connection.BeginTransaction
            cmd.Transaction = trac

            For i = 0 To arrSQL.Count - 1

                SQLString = arrSQL(i)
                Debug.WriteLine("SQLString --" & SQLString)
                cmd.CommandText = SQLString
                cmd.ExecuteNonQuery()
            Next
            trac.Commit()
            Return True
        Catch e As SqlClient.SqlException
            trac.Rollback()
            Debug.WriteLine("executeException --" & e.ToString())
            Return False
        Catch ex As Exception
            MsgBox(ex.ToString)

            trac.Rollback()
            Debug.WriteLine("executeException --" & ex.ToString())
            Return False
        Finally
            OleDBConn.Close()
        End Try
    End Function
End Module