class1.vb

图书借阅系统

Imports System
Imports System.Data
Imports System.Data.OleDb
Public Class OleDatabase
    '类成员声明
    Public Connection As OleDbConnection     'OleDbConnection对象 连接数据库
    Public Adapter As OleDbDataAdapter     'OleDbDataAdapter 用于填充DATASET 和更新数据源
    Public Command As OleDbCommand       ' OleDbCommand 用于执行SQL语句
    Public CommandBuilder As OleDbCommandBuilder '协调dataset的更改和关联数据库的单表命令
    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   '获取生成SQL语句的对象Adapter
            CommandBuilder.RefreshSchema() '刷新用于生成INSERT UPDATE DELETE 的数据架构信息
            Adapter.Update(Data)   ' 为DATASET中已插入 删除 更新的行调用INSERT UPDATE DELETE 
        Catch ex As Exception
            succ = False    '更新失败
        End Try
        UpDate = succ      '更新成功
    End Function
    '执行SELECT SQL语句,并返回OleDbDataReader对象
    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 '获取或设置为数据源执行的SQL语句或存储过程
            Command.CommandType = CommandType.Text '获取或设置一个指示如何解释Commandtext属性的值   SQL文本命令CommandType.Text
            Adapter.SelectCommand = Command     '用于选择数据源的记录
            DataReader = Command.ExecuteReader(CommandBehavior.CloseConnection)  '如果关闭datareader 关联的connection 也将关闭
        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() '针对CONNECTION返回受影响的行数
        Catch ex As Exception
            ret = -1
            DisplayError(ex)
        Finally
            Connection.Close()
        End Try
        SelectQuery = ret
    End Function
    '执行SELECT 存储过程，并填充一个已经new过的数据集对象
    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  'StoredProcedure存储过程的名称
            Adapter.SelectCommand = Command
            Adapter.Fill(SelectData)   '添加或刷新行时用
        Catch ex As Exception
            ret = False
            DisplayError(ex)
        Finally
            Connection.Close()
        End Try
        SelectSP = ret
    End Function
    '执行SELECT SQL语句,并返回OleDbDataReader对象
    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    '获取或设置为数据源执行的SQL语句或存储过程
            Command.CommandType = CommandType.StoredProcedure  '获取或设置一个指示如何解释Commandtext属性的值   StoredProcedure存储过程的名称
            Adapter.SelectCommand = Command       '用于选择数据源的记录
            DataReader = Command.ExecuteReader()   '将commandtxt发送到conection并生成datareader
        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() '执行SQL语句并返回受影响的行数
        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    '获取或设置为数据源执行的SQL语句或存储过程
            Command.CommandType = CommandType.StoredProcedure  '获取或设置一个指示如何解释Commandtext属性的值   StoredProcedure存储过程的名称
            Adapter.DeleteCommand = Command    '从数据集中删除数据
            Command.ExecuteNonQuery()    '针对CONNECTION返回受影响的行数
        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()   '针对CONNECTION返回受影响的行数
        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  '获取或设置为数据源执行的SQL语句或存储过程
            Command.CommandType = CommandType.Text 'SQL文本命令
            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
    Public Function SelectQuery(ByVal SelectString 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 = SelectString   '获取或设置为数据源执行的SQL语句或存储过程
            Command.CommandType = CommandType.Text
            Adapter.SelectCommand = Command
            Adapter.Fill(SelectData)
        Catch ex As Exception
            ret = False
            DisplayError(ex)
        Finally
            Connection.Close()
        End Try
        SelectQuery = ret
    End Function
    '接口AccessConnection ----- 连接到Access数据库    
    'DbPath ---- 数据库完整路径                                '''''''''''''''''''''''''''''''
    'UserId ---- 用户名
    'Passord ----- 用户密码
    '成功返回 True，否则返回 False
    Public Function AccessConnection(ByVal DbPath As String, ByVal UserId As String, ByVal Password As String) As Boolean
        Dim ret As Boolean = True
        '如果数据库连接没有打开，尝试打开数据库连接
        If Connection.State = ConnectionState.Closed Then
            '构造连接字串
            ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                          DbPath & ";User Id=" & UserId & ";Password=" & Password & ";"
            Try
                Connection.ConnectionString = ConnString
                Connection.Open()
            Catch ex As Exception
                ret = False
                DisplayError(ex)
            End Try
        End If
        AccessConnection = ret '返回函数结果
    End Function




    '接口CreateConnection ----- 创建数据库连接,自定义连接字串，可连接各种数据库
    'connString ----- 连接字串
    '成功返回 True，否则返回 False
    Public Function CreateConnection(ByVal ConnectionString As String) As Boolean
        Dim ret As Boolean = True
        ConnString = ConnectionString
        '如果数据库连接没有打开，尝试打开数据库连接
        If Connection.State = ConnectionState.Closed Then
            Try
                Connection.ConnectionString = ConnectionString
                Connection.Open()
            Catch ex As Exception
                ret = False
                DisplayError(ex)
            End Try
        End If
        CreateConnection = ret '返回函数结果
    End Function

    '关闭数据库连接      
    Public Function CloseConnection()
        If Connection.State = ConnectionState.Open Then
            Try
                Connection.Close() '关闭数据库连接
            Catch ex As Exception
                DisplayError(ex)
            End Try
        End If
    End Function

    Public Sub New()
        '初始化类的成员变量
        Connection = New OleDbConnection
        Adapter = New OleDbDataAdapter
        Command = New OleDbCommand
        CommandBuilder = New OleDbCommandBuilder

        DeleteCommand = New OleDbCommand
        InsertCommand = New OleDbCommand
        SelectCommand = New OleDbCommand
        UpdateCommand = New OleDbCommand

        Command.Connection = Connection
        DeleteCommand.Connection = Connection
        InsertCommand.Connection = Connection
        SelectCommand.Connection = Connection
        UpdateCommand.Connection = Connection

    End Sub

    Protected Overrides Sub Finalize()
        '如果数据库处于连接状态，则关闭连接
        If Connection.State = ConnectionState.Open Then
            Try
                Connection.Close()
            Catch ex As Exception
                DisplayError(ex)
            End Try
        End If
    End Sub


End Class