李国帅 于2008年收集


sqlClient


Imports System.Data
Imports System.Data.SqlClient
Imports System.IO

Public Class CSqlClient
    Private strConnect As String                    '连接数据库字符串
    Private myConnection As SqlConnection = Nothing 'SqlConnection对象
    Private myConnectFlag As Boolean = False        '是否连接成功标识
    Private strSQL As String                        'SQL语句字符串

    Public Sub ErrorLog(ByVal e As Exception)
        Dim strMsg As New StringWriter()
        strMsg.WriteLine("<Date Time>" & DateTime.Now())
        strMsg.WriteLine("Source:" & e.Source)
        strMsg.WriteLine("Message:" & e.Message)

        System.Diagnostics.EventLog.WriteEntry("SqlDatabase", strMsg.ToString())
    End Sub


    '连接数据库(Sql Server)
    Public Function connect(ByRef m_strConnect As String) As Boolean
        Try
            strConnect = m_strConnect
            myConnection = New SqlConnection(strConnect)
            myConnection.Open()
            myConnectFlag = True
        Catch err As Exception
            ErrorLog(err)
        End Try
        Return myConnectFlag
    End Function

    '关闭数据库
    Public Sub close()
        If (myConnectFlag) Then
            myConnection.Close()
        End If
    End Sub

    '获取SqlServer对象
    Public Function getConnectSqlServer() As SqlConnection
        If (myConnectFlag) Then
            getConnectSqlServer = myConnection
        End If
    End Function

    '创建一个command对象
    Public Function CreateCommand(ByRef m_strSQL As String, ByRef m_Param() As SqlParameter, _
                                        ByRef m_SQLCmdType As CommandType) As SqlCommand
        Dim myCommand As New SqlCommand(m_strSQL, myConnection)
        myCommand.CommandType = m_SQLCmdType
        Dim Param As SqlParameter
        If Not m_Param Is Nothing Then
            For Each Param In m_Param
                myCommand.Parameters.Add(Param)
            Next
        End If

        Try
            Return myCommand
        Catch err As SqlException
            ErrorLog(err)
            Return Nothing
        End Try
    End Function

    '执行SQL语句
    Public Overloads Function ExecuteSQL(ByRef m_strSQL As String, ByRef m_DataSet As DataSet) As Boolean
        Try
            If (myConnectFlag) Then
                Dim myDataAdapter As New SqlDataAdapter(m_strSQL, myConnection)
                myDataAdapter.Fill(m_DataSet)
            End If
        Catch err As SqlException
            ErrorLog(err)
            Return False
        End Try
    End Function

    Public Overloads Function ExecuteSQL(ByVal m_strSQL As String, ByRef m_DataReader As SqlDataReader) As Boolean

        Dim myCommand As New SqlCommand(m_strSQL, myConnection)

        Try
            m_DataReader = myCommand.ExecuteReader()
            Return True
        Catch err As SqlException
            ErrorLog(err)
            Return False
        End Try

    End Function


    '执行存储过程
    Public Overloads Function ExecuteSP(ByRef m_SProcName As String, ByRef m_Param() As SqlParameter, ByRef m_DataSet As DataSet) As Boolean
        Dim myCommand As SqlCommand = CreateCommand(m_SProcName, m_Param, CommandType.StoredProcedure)
        Dim myDataAdapter As New SqlDataAdapter(myCommand)

        Try
            myDataAdapter.Fill(m_DataSet)
            Return True
        Catch err As SqlException
            ErrorLog(err)
            Return False
        End Try
    End Function

    Public Overloads Function ExecuteSP(ByRef m_SProcName As String, ByRef m_Param() As SqlParameter) As Boolean
        Dim myCommand As SqlCommand = CreateCommand(m_SProcName, m_Param, CommandType.StoredProcedure)

        Try
            myCommand.ExecuteNonQuery()
            Return True
        Catch err As SqlException
            ErrorLog(err)
            Return False
        End Try
    End Function

    Public Overloads Function ExecuteSP(ByRef m_SProcName As String, ByRef m_Param() As SqlParameter, ByRef m_DataReader As SqlDataReader) As Boolean
        Dim myCommand As SqlCommand = CreateCommand(m_SProcName, m_Param, CommandType.StoredProcedure)

        Try
            m_DataReader = myCommand.ExecuteReader
            Return True
        Catch err As SqlException
            ErrorLog(err)
            Return False
        End Try
    End Function





    '############################################################################################

    '获取数据库的表名
    Public Function getTables(ByRef m_TablesDataSet As DataSet) As Boolean
        Dim myTablesDataSet As New DataSet()
        Dim myDataReader As SqlDataReader
        Dim mySuccessFlag As Boolean = False

        Try
            If (myConnectFlag) Then
                Dim SuccessFlag As Boolean = False
                SuccessFlag = myTables(myDataReader)
                If (SuccessFlag) Then
                    Dim myArrayList As ArrayList = New ArrayList()
                    While (myDataReader.Read())
                        Dim myTableName As String = ""
                        myTableName = CType(myDataReader.GetValue(0), String)
                        myArrayList.Add(myTableName)
                    End While
                    myDataReader.Close()
                    Dim iArrayCount As Integer = myArrayList.Count
                    Dim myTableArray(iArrayCount) As String
                    myArrayList.CopyTo(myTableArray)
                    Dim i As Integer
                    For i = 0 To iArrayCount - 1
                        Dim tblName As String
                        tblName = myTableArray(i)
                        strSQL = "sp_help " & "[" & tblName & "]"
                        Dim myCommand As New SqlCommand(strSQL, myConnection)
                        Dim myDataAdapter As New SqlDataAdapter(myCommand)
                        myDataAdapter.Fill(myTablesDataSet, tblName)
                    Next
                End If
                m_TablesDataSet = myTablesDataSet
                mySuccessFlag = True
            End If
        Catch e As SqlException
            Console.WriteLine(e.ToString())
        End Try
        m_TablesDataSet = myTablesDataSet
        Return mySuccessFlag
    End Function

    Private Function myTables(ByRef myDataReader As SqlDataReader) As Boolean
        Dim mySuccessFlag As Boolean = False
        Dim myLocalDataReader As SqlDataReader

        Try
            If (myConnectFlag) Then
                strSQL = "SELECT Name FROM SysObjects WHERE Type='U'"
                Dim myCommand As New SqlCommand(strSQL, myConnection)
                myLocalDataReader = myCommand.ExecuteReader(CommandBehavior.SingleResult)
                mySuccessFlag = True
            End If
        Catch e As SqlException
            Console.WriteLine(e.ToString())
        End Try

        myDataReader = myLocalDataReader
        Return mySuccessFlag
    End Function


    '获取数据库的视图名
    Public Function getViews(ByRef m_TablesDataSet As DataSet) As Boolean
        Dim myTablesDataSet As New DataSet()
        Dim myDataReader As SqlDataReader
        Dim mySuccessFlag As Boolean = False

        Try
            If (myConnectFlag) Then
                Dim SuccessFlag As Boolean = False
                SuccessFlag = myViews(myDataReader)
                If (SuccessFlag) Then
                    Dim myArrayList As ArrayList = New ArrayList()
                    While (myDataReader.Read())
                        Dim myTableName As String = ""
                        myTableName = CType(myDataReader.GetValue(0), String)
                        myArrayList.Add(myTableName)
                    End While
                    myDataReader.Close()
                    Dim iArrayCount As Integer = myArrayList.Count
                    Dim myTableArray(iArrayCount) As String
                    myArrayList.CopyTo(myTableArray)
                    Dim i As Integer
                    For i = 0 To iArrayCount - 1
                        Dim tblName As String
                        tblName = myTableArray(i)
                        strSQL = "sp_help " & "[" & tblName & "]"
                        Dim myCommand As New SqlCommand(strSQL, myConnection)
                        Dim myDataAdapter As New SqlDataAdapter(myCommand)
                        myDataAdapter.Fill(myTablesDataSet, tblName)
                    Next
                End If
                m_TablesDataSet = myTablesDataSet
                mySuccessFlag = True
            End If
        Catch e As SqlException
            Console.WriteLine(e.ToString())
        End Try
        m_TablesDataSet = myTablesDataSet
        Return mySuccessFlag
    End Function

    Private Function myViews(ByRef myDataReader As SqlDataReader) As Boolean
        Dim mySuccessFlag As Boolean = False
        Dim myLocalDataReader As SqlDataReader

        Try
            If (myConnectFlag) Then
                strSQL = "SELECT * FROM SysObjects WHERE Type='V'"
                Dim myCommand As New SqlCommand(strSQL, myConnection)
                myLocalDataReader = myCommand.ExecuteReader(CommandBehavior.SingleResult)
                mySuccessFlag = True
            End If
        Catch e As SqlException
            Console.WriteLine(e.ToString())
        End Try

        myDataReader = myLocalDataReader
        Return mySuccessFlag
    End Function

    '获取数据库的存储过程名
    Public Function getStoredProcs(ByRef m_TablesDataSet As DataSet) As Boolean
        Dim myTablesDataSet As New DataSet()
        Dim myDataReader As SqlDataReader
        Dim mySuccessFlag As Boolean = False

        Try
            If (myConnectFlag) Then
                Dim SuccessFlag As Boolean = False
                SuccessFlag = myStoredProcs(myDataReader)
                If (SuccessFlag) Then
                    Dim myArrayList As ArrayList = New ArrayList()
                    While (myDataReader.Read())
                        Dim myTableName As String = ""
                        myTableName = CType(myDataReader.GetValue(0), String)
                        myArrayList.Add(myTableName)
                    End While
                    myDataReader.Close()
                    Dim iArrayCount As Integer = myArrayList.Count
                    Dim myTableArray(iArrayCount) As String
                    myArrayList.CopyTo(myTableArray)
                    Dim i As Integer
                    For i = 0 To iArrayCount - 1
                        Dim tblName As String
                        tblName = myTableArray(i)
                        strSQL = "sp_help " & "[" & tblName & "]"
                        Dim myCommand As New SqlCommand(strSQL, myConnection)
                        Dim myDataAdapter As New SqlDataAdapter(myCommand)
                        myDataAdapter.Fill(myTablesDataSet, tblName)
                    Next
                End If
                m_TablesDataSet = myTablesDataSet
                mySuccessFlag = True
            End If
        Catch e As SqlException
            Console.WriteLine(e.ToString())
        End Try
        m_TablesDataSet = myTablesDataSet
        Return mySuccessFlag
    End Function

    Private Function myStoredProcs(ByRef myDataReader As SqlDataReader) As Boolean
        Dim mySuccessFlag As Boolean = False
        Dim myLocalDataReader As SqlDataReader

        Try
            If (myConnectFlag) Then
                strSQL = "SELECT * FROM SysObjects WHERE Type='P'"
                Dim myCommand As New SqlCommand(strSQL, myConnection)
                myLocalDataReader = myCommand.ExecuteReader(CommandBehavior.SingleResult)
                mySuccessFlag = True
            End If
        Catch e As SqlException
            Console.WriteLine(e.ToString())
        End Try

        myDataReader = myLocalDataReader
        Return mySuccessFlag
    End Function

End Class


oledbClient


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
    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
            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

    '连接到DB2数据库
    'Server ---- 服务器地址或名称
    'DbName ----- 要连接到的数据库名称
    'UserId ---- 用户名
    'Password ---- 登录口令
    Public Function Db2Connection(ByVal Server As String, ByVal DbName 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=IBMDADB2.1;Mode=ReadWrite;UID=" & _
                          UserId & ";PWD=" & Password & ";Data Source=" & DbName & _
                         ";Location=" & Server
            Try
                Connection.ConnectionString = ConnString
                Connection.Open()
            Catch ex As Exception
                ret = False
                DisplayError(ex)
            End Try
        End If
        Db2Connection = 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

    '接口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

    '接口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

    '接口SqlConnection ----- 连接到SQL Server数据库(通过用户名和密码登录）
    'DbName ---- 数据库名
    'UserId ---- 用户名
    'Passord ----- 用户密码
    'DataSource ----- 要连接的SQL实例的名字或者网络地址
    '如果是网络地址，IP地址后要加个端口号，如:190.190.200.100,1433,SQL缺省的端口号为1433
    '成功返回 True，否则返回 False
    Public Function SqlConnection(ByVal DataSource As String, ByVal DbName 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=SQLOLEDB.1;Initial Catalog=" & _
                          DbName & ";Data Source=" & DataSource & ";User Id=" & UserId & _
                          ";Password=" & Password & ";"
            Try
                Connection.ConnectionString = ConnString
                Connection.Open()
            Catch ex As Exception
                ret = False
                DisplayError(ex)
            End Try
        End If
        SqlConnection = ret '返回函数结果
    End Function

    '接口SqlConnection ----- 连接到SQL Server数据库(信任安全连接）
    'DbName ---- 数据库名
    'DataSource ----- 要连接的SQL实例的名字
    '成功返回 True，否则返回 False
    Public Function SqlConnection(ByVal DataSource As String, ByVal DbName As String) As Boolean
        Dim ret As Boolean = True
        '如果数据库连接没有打开，尝试打开数据库连接
        If Connection.State = ConnectionState.Closed Then
            '构造连接字串
            ConnString = "Provider=SQLOLEDB.1;Initial Catalog=" & _
                          DbName & ";Data Source=" & DataSource & _
                          ";Integrated Security=SSPI;Persist Security Info=False;"
            Try
                Connection.ConnectionString = ConnString
                Connection.Open()
            Catch ex As Exception
                ret = False
                DisplayError(ex)
            End Try
        End If
        SqlConnection = ret '返回函数结果
    End Function

    '接口OracleConnection ----- 连接到Oracle数据库(通过用户名和密码登录）
    'UserId ---- 用户名
    'Passord ----- 用户密码
    'DataSource ----- 要连接的Oracle实例的名字或者网络地址
    '如果是网络地址，IP地址后要加个端口号，如:190.190.200.100,1433
    '成功返回 True，否则返回 False
    Public Function OracleConnection(ByVal DataSource 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=OraOLEDB.Oracle;Data Source=" & DataSource & _
                         ";User Id=" & UserId & ";Password=" & Password & ";"
            Try
                Connection.ConnectionString = ConnString
                Connection.Open()
            Catch ex As Exception
                ret = False
                DisplayError(ex)
            End Try
        End If
        OracleConnection = ret '返回函数结果
    End Function

    '接口OracleConnection ----- 连接到Oracle Server数据库(信任安全连接）
    'DataSource ----- 要连接的Oracle实例的名字
    '成功返回 True，否则返回 False
    Public Function OracleConnection(ByVal DataSource As String) As Boolean
        Dim ret As Boolean = True
        '如果数据库连接没有打开，尝试打开数据库连接
        If Connection.State = ConnectionState.Closed Then
            '构造连接字串
            ConnString = "Provider=OraOLEDB.Oracle;Data Source=" & DataSource & ";OSAuthent=1;"
            Try
                Connection.ConnectionString = ConnString
                Connection.Open()
            Catch ex As Exception
                ret = False
                DisplayError(ex)
            End Try
        End If
        OracleConnection = ret '返回函数结果
    End Function

    '接口ExcelConnection ----- 连接到Excel工作表
    'DbPath ----- .xls文件完整路径
    '成功返回 True，否则返回 False
    Public Function ExcelConnetion(ByVal DbPath 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 & _
            "; Extended Properties=Excel 8.0;"
            Try
                Connection.ConnectionString = ConnString
                Connection.Open()
            Catch ex As Exception
                ret = False
                DisplayError(ex)
            End Try
        End If
        ExcelConnetion = ret '返回函数结果
    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