class1.vb

这是一个完善的图书馆管理系统.后台数据库采用ACCESS.

Imports System
Imports System.Data
Imports System.Data.OleDb

Public Class OleDatabase

    '类成员声明
    Public Connection As OleDbConnection    'OleDbConnection对象
    Public Adapter As OleDbDataAdapter      'OleDbDataAdapter对象
    Public Command As OleDbCommand
    Public CommandBuilder As OleDbCommandBuilder
    Public SelectCommand As OleDbCommand
    Public InsertCommand As OleDbCommand
    Public UpdateCommand As OleDbCommand
    Public DeleteCommand As OleDbCommand
    Protected ConnString As String

    '显示错误信息
    Public Sub DisplayError(ByVal ex As Exception)
        MsgBox("模块：" & ex.Source & "出现错误" & Chr(10) & Chr(13) & "错误信息：" & ex.Message)
    End Sub

    '更新数据集(DataSet)、数据表(DataTable)、或数据行(DataRow())
    Public Function UpDate(ByRef Data As Object) As Boolean
        Dim succ As Boolean = True
        Try
            CommandBuilder.DataAdapter = Adapter
            CommandBuilder.RefreshSchema()
            Adapter.Update(Data)
        Catch ex As Exception
            succ = False
        End Try
        UpDate = succ
    End Function

    '执行SELECT SQL语句，并返回OleDbDataReader对象
    '函数成功返回True，失败返回False
    Public Function SelectQuery(ByVal SelectString As String, ByRef DataReader As OleDbDataReader) As Boolean
        Dim ret As Boolean = True
        Try
            If Connection.State = ConnectionState.Closed Then
                Connection.Open()
            End If
            Command.CommandText = SelectString
            Command.CommandType = CommandType.Text
            Adapter.SelectCommand = Command
            DataReader = Command.ExecuteReader(CommandBehavior.CloseConnection)
        Catch ex As Exception
            ret = False
            DisplayError(ex)
        End Try
        SelectQuery = ret
    End Function

    '执行SELECT SQL语句,成功返回受影响的行数,失败返回-1
    Public Function SelectQuery(ByVal SelectString As String) As Integer
        Dim ret As Integer
        Try
            If Connection.State = ConnectionState.Closed Then
                Connection.Open()
            End If
            Command.CommandText = SelectString
            Command.CommandType = CommandType.Text
            Adapter.SelectCommand = Command
            ret = Command.ExecuteNonQuery()
        Catch ex As Exception
            ret = -1
            DisplayError(ex)
        Finally
            Connection.Close()
        End Try
        SelectQuery = ret
    End Function

    '执行SELECT 存储过程，并填充一个已经new过的数据集对象
    '函数成功返回True，失败返回False
    Public Function SelectSP(ByVal SPName As String, ByRef SelectData As Object) As Boolean
        Dim ret As Boolean = True
        Try
            If Connection.State = ConnectionState.Closed Then
                Connection.Open()
            End If
            Command.CommandText = SPName
            Command.CommandType = CommandType.StoredProcedure
            Adapter.SelectCommand = Command
            Adapter.Fill(SelectData)
        Catch ex As Exception
            ret = False
            DisplayError(ex)
        Finally
            Connection.Close()
        End Try
        SelectSP = ret
    End Function

    Public Function SelectSP(ByVal SPName As String, ByRef DataReader As OleDbDataReader) As Boolean
        Dim ret As Boolean = True
        Try
            If Connection.State = ConnectionState.Closed Then
                Connection.Open()
            End If
            Command.CommandText = SPName
            Command.CommandType = CommandType.StoredProcedure
            Adapter.SelectCommand = Command
            DataReader = Command.ExecuteReader()
        Catch ex As Exception
            ret = False
            DisplayError(ex)
        Finally
            Connection.Close()
        End Try
        SelectSP = ret
    End Function

    '执行UPDATE 存储过程
    '函数成功返回True，失败返回False
    Public Function UpdateSP(ByVal SPName As String) As Boolean
        Dim ret As Boolean = True
        Try
            If Connection.State = ConnectionState.Closed Then
                Connection.Open()
            End If
            Command.CommandText = SPName
            Command.CommandType = CommandType.StoredProcedure
            Adapter.UpdateCommand = Command
            Command.ExecuteNonQuery()
        Catch ex As Exception
            ret = False
            DisplayError(ex)
        Finally
            Connection.Close()
        End Try
        UpdateSP = ret
    End Function

    '执行DELETE 存储过程
    '函数成功返回True，失败返回False
    Public Function DeleteSP(ByVal SPName As String) As Boolean
        Dim ret As Boolean = True
        Try
            If Connection.State = ConnectionState.Closed Then
                Connection.Open()
            End If
            Command.CommandText = SPName
            Command.CommandType = CommandType.StoredProcedure
            Adapter.DeleteCommand = Command
            Command.ExecuteNonQuery()
        Catch ex As Exception
            ret = False
            DisplayError(ex)
        Finally
            Connection.Close()
        End Try
        DeleteSP = ret
    End Function

    '执行INSERT 存储过程
    '函数成功返回True，失败返回False
    Public Function InsertSP(ByVal SPName As String) As Boolean
        Dim ret As Boolean = True
        Try
            If Connection.State = ConnectionState.Closed Then
                Connection.Open()
            End If
            Command.CommandText = SPName
            Command.CommandType = CommandType.StoredProcedure
            Adapter.InsertCommand = Command
            Command.ExecuteNonQuery()
        Catch ex As Exception
            ret = False
            DisplayError(ex)
        Finally
            Connection.Close()
        End Try
        InsertSP = ret
    End Function

    '执行DELETE SQL语句
    '函数执行完之后，数据库连接自动关闭，不要再调用CloseConnection()接口
    '函数成功返回True，失败返回False
    Public Function DeleteQuery(ByVal DeleteString As String) As Boolean
        Dim ret As Boolean = True
        Try
            If Connection.State = ConnectionState.Closed Then
                Connection.Open()
            End If
            Command.CommandText = DeleteString
            Command.CommandType = CommandType.Text
            Adapter.DeleteCommand = Command
            Command.ExecuteNonQuery()
        Catch ex As Exception
            ret = False
            DisplayError(ex)
        Finally
            Connection.Close()
        End Try
        DeleteQuery = ret
    End Function

    '执行UPDATE SQL语句
    '函数执行完之后，数据库连接自动关闭，不要再调用CloseConnection()接口
    '函数成功返回True，失败返回False
    Public Function UpdateQuery(ByVal UpdateString As String) As Boolean
        Dim ret As Boolean = True
        Try
            If Connection.State = ConnectionState.Closed Then
                Connection.Open()
            End If
            Command.CommandText = UpdateString
            Command.CommandType = CommandType.Text
            Adapter.UpdateCommand = Command
            Command.ExecuteNonQuery()
        Catch ex As Exception
            ret = False
            DisplayError(ex)
        Finally
            Connection.Close()
        End Try
        UpdateQuery = ret
    End Function

    '执行INSERT SQL语句
    '函数执行完之后，数据库连接自动关闭，不要再调用CloseConnection()接口
    '函数成功返回True，失败返回False
    Public Function InsertQuery(ByVal InsertString As String) As Boolean
        Dim ret As Boolean = True
        Try
            If Connection.State = ConnectionState.Closed Then
                Connection.Open()
            End If
            Command.CommandText = InsertString
            Command.CommandType = CommandType.Text
            Adapter.InsertCommand = Command
            Command.ExecuteNonQuery()
        Catch ex As Exception
            ret = False
            DisplayError(ex)
        Finally
            Connection.Close()
        End Try
        InsertQuery = ret
    End Function

    '执行SELECT SQL语句，并填充一个已经new过的数据集对象
    '函数执行完之后，数据库连接自动关闭，不要再调用CloseConnection()接口
    '如果查询成功返回True,否则返回False