VB.net 操作Oracle

Imports Microsoft.VisualBasic
'Imports System.Data.OracleClient
Imports System.Data
Imports Oracle.DataAccess.Client


Public Class Database
    Public Const gstrDateFormat = "yyyy-MM-dd"                '全局的日期型格式
    Public Const gstrDateTimeFormat = "yyyy-MM-dd HH24:MI:SS" '全局的日期时间格式
    Public Const gstrLongDateFormat = "yyyy-MM-dd HH:MM:SS"
    Public Const gstrOracleDateFormat = "YYYY-MM-DD"
    Public Const gstrOracleDateTimeFormat = "yyyy-MM-dd HH24:MI:SS" '全局的日期时间格式


    Private Function getConnectString() As String
        Dim strX As String

        strX = "Data Source=abc;Password=abc;User ID=abc;Min Pool Size=10;Max Pool Size=100;" & _
               "Connection Lifetime=120;Connection Timeout=60;Incr Pool Size=5;Decr Pool Size=2"

        Return strX

    End Function

    Public Sub gGetData(ByVal tmpStrSQL As String, ByVal tmpInTableName As String, _
                ByRef intErrNUM As Integer, ByRef strErrDesc As String, _
                ByRef tmpDS As DataSet)
        Dim dbConn As New OracleConnection()

        Dim dbCmd As New OracleCommand(tmpStrSQL, dbConn)

        Dim dbAdapter As New OracleDataAdapter(dbCmd)
        Try
            dbConn.ConnectionString = getConnectString()
            dbConn.Open()

            dbAdapter.Fill(tmpDS, tmpInTableName)

            If Not xGetDataSetBoolean(tmpDS) Then
                intErrNUM = 1
                strErrDesc = "数据集无表"
            Else
                If tmpDS.Tables(tmpInTableName).Rows.Count = 0 Then
                    intErrNUM = 2
                    strErrDesc = "没有满足条件的记录"
                Else
                    intErrNUM = 0
                    strErrDesc = "成功"
                End If
            End If


        Catch ex As Exception
            intErrNUM = 9
            strErrDesc = Err.Description
        Finally

            If dbConn.State = ConnectionState.Open Then

                dbConn.Close()

            End If
            dbAdapter.Dispose()
            dbCmd.Dispose()
            dbConn.Dispose()

        End Try

    End Sub

    Public Function gExecute(ByVal tmpSql() As String, ByRef tmpErrMsg As String) As Boolean
        Dim dbConn As New OracleConnection()
        Dim myTrans As OracleTransaction
        Dim iBound As Integer
        Dim iLoop As Integer

        gExecute = False

        dbConn.ConnectionString = getConnectString()
        Try
            dbConn.Open()

            Dim dbCMD As New OracleCommand("", dbConn)

            myTrans = dbConn.BeginTransaction(IsolationLevel.ReadCommitted)

            Try

                iBound = UBound(tmpSql)


                For iLoop = 0 To iBound

                    dbCMD.CommandText = tmpSql(iLoop)

                    dbCMD.ExecuteNonQuery()
                Next

                myTrans.Commit()

                gExecute = True

                tmpErrMsg = "执行成功"

            Catch ex As Exception

                tmpErrMsg = Err.Description
                myTrans.Rollback()

            Finally
                myTrans.Dispose()
                dbCMD.Dispose()

            End Try
        Catch ex As Exception
            tmpErrMsg = Err.Description

        Finally

            If dbConn.State = ConnectionState.Open Then

                dbConn.Close()
            End If

            dbConn.Dispose()
        End Try

    End Function

    'todo: 判断DataSet 是否为空
    Public Function xGetDataSetBoolean(ByVal tmpInDataSet As DataSet) As Boolean
        If tmpInDataSet.Tables.Count = 0 Then
            Return False
            Exit Function
        Else
            Return True
            Exit Function
        End If

    End Function

    '/--------------------------------------------------------------------------------------------------------------------------------------
    '|              增加一个表，一表列
    '|              
    '|              
    '|              
    '\--------------------------------------------------------------------------------------------------------------------------------------
    Public Function gWriteErrorTable() As DataSet
        Dim tmpDataSet As New DataSet

        Dim tmpErroTableName As New DataTable("Error")
        tmpDataSet.Tables.Add(tmpErroTableName)
        Dim objNewDataRow As DataRow
        Dim objCol1 As New DataColumn
        Dim objCol2 As New DataColumn

        objCol1.ColumnName = "ErrorCode"
        objCol1.DataType = GetType(System.Int16)
        tmpErroTableName.Columns.Add(objCol1)

        objCol2.ColumnName = "ErrorDesc"
        objCol2.DataType = GetType(System.String)
        objCol2.MaxLength = 255

        tmpErroTableName.Columns.Add(objCol2)

        objNewDataRow = tmpErroTableName.NewRow
        objNewDataRow.Item("ErrorCode") = "9"
        objNewDataRow.Item("ErrorDesc") = "未使用"


        tmpErroTableName.Rows.Add(objNewDataRow)

        objCol1.Dispose()
        objCol2.Dispose()

        Return tmpDataSet

    End Function


End Class