vb.net数据库连接处理

Imports System.Data
Imports System.Data.SqlClient

Namespace DA
    Public Class DBConnector
        Dim _connectionString As String
        Dim _DBConnection As System.Data.SqlClient.SqlConnection

        Public Sub New(ByVal connectionString As String)
            _connectionString = connectionString
        End Sub

        Property DBConnection() As IDbConnection
            Get
                Return _DBConnection
            End Get
            Set(ByVal value As IDbConnection)
                _DBConnection = value
            End Set
        End Property

        Public Sub GetDBConnection()

            Try
                If _DBConnection Is Nothing Then
                    _DBConnection = New System.Data.SqlClient.SqlConnection(_connectionString)
                End If
            Catch ex As Exception
                Throw ex

            End Try
          

        End Sub

        Public Sub OpenDBConnection()
            Try
                If _DBConnection Is Nothing Then
                    GetDBConnection()
                End If
                If _DBConnection.State <> ConnectionState.Open Then
                    _DBConnection.Open()

                End If
            Catch ex As Exception
                _DBConnection.Close()
                Throw ex

            End Try
        End Sub
        Public Sub CloseDBConnection()
            Try
                If Not _DBConnection Is Nothing Then
                    If _DBConnection.State = ConnectionState.Open Then
                        _DBConnection.Close()
                    End If
                End If
            Catch ex As Exception

            End Try
        End Sub
    End Class
End Namespace

 

Imports System.Data
Imports System.Data.SqlClient
Imports GatesU.MES.DA

Namespace DA

 

    Public Class DBTransaction

        Dim _DBConnector As GatesU.MES.DA.DBConnector
        Dim _DBTransaction As System.Data.IDbTransaction
        Dim _TimeOut As Integer = 0
        Dim _isTransaction As Boolean = False

        Public Sub New(ByVal connectionString As String)
            _DBConnector = New DBConnector(connectionString)

        End Sub
        Public Property DBTransaction() As IDbTransaction
            Get
                Return _DBTransaction

            End Get
            Set(ByVal value As IDbTransaction)
                _DBTransaction = value

            End Set
        End Property

        Public Property TimeOut() As Integer
            Get
                Return _TimeOut

            End Get
            Set(ByVal value As Integer)
                _TimeOut = value

            End Set
        End Property

        Public Sub BeginTransaction()
            Try

                If _DBConnector.DBConnection Is Nothing Then
                    _DBConnector.GetDBConnection()
                End If
                If _DBConnector.DBConnection.State <> ConnectionState.Open Then
                    _DBConnector.OpenDBConnection()
                End If

                _DBTransaction = _DBConnector.DBConnection.BeginTransaction(IsolationLevel.ReadUncommitted)
                _isTransaction = True

            Catch ex As Exception
                _DBConnector.CloseDBConnection()
                _isTransaction = False

            End Try

        End Sub
        Public Sub CommitTransaction()

            Try
                
                _DBTransaction.Commit()

 

            Catch ex As Exception
                _DBTransaction.Rollback()
            Finally

                _DBConnector.CloseDBConnection()
                _DBTransaction.Dispose()
                _isTransaction = False

            End Try
        End Sub
        Public Sub RollbackTransaction()
            If Not _DBTransaction Is Nothing Then
                _DBTransaction.Rollback()
                _DBTransaction.Dispose()
            End If
            _DBConnector.CloseDBConnection()
            _isTransaction = False

        End Sub

        Private Function getDBCommandTrans(ByVal commandText As String, Optional ByVal isSP As Boolean = False) As IDbCommand
            Dim selCommand As New System.Data.SqlClient.SqlCommand

            Try
                _DBConnector.OpenDBConnection()
                selCommand.Connection = _DBConnector.DBConnection
                If isSP Then
                    selCommand.CommandType = CommandType.StoredProcedure
                Else
                    selCommand.CommandType = CommandType.Text
                End If
                selCommand.CommandText = commandText
                If _isTransaction Then
                    selCommand.Transaction = _DBTransaction
                End If

                If _TimeOut > 0 Then
                    selCommand.CommandTimeout = _TimeOut
                End If
                Return selCommand

            Catch ex As Exception
                RollbackTransaction()
                Throw ex

            End Try
        End Function

        Private Function getDBAdapter(ByVal DBCommand As IDbCommand) As IDbDataAdapter

            Try

                Return New System.Data.SqlClient.SqlDataAdapter(DBCommand)
            Catch ex As Exception
                RollbackTransaction()
                Throw ex

            End Try

        End Function

        Public Function ExecSQL(ByVal strSQL As String) As Boolean

            Dim selCommand As IDbCommand

            Try
                selCommand = getDBCommandTrans(strSQL)

                selCommand.ExecuteNonQuery()
                Return True
            Catch ex As Exception
                Return False

            End Try
        End Function

        Public Function SQLToDataset(ByVal tableName As String, ByVal strSQL As String) As DataSet

            Dim selCommand As IDbCommand
            Dim selAdapter As IDataAdapter
            Dim reDS As New DataSet

            Try
                Dim tables() As String
                tables = tableName.Split("|")
                selCommand = getDBCommandTrans(strSQL)

                selAdapter = getDBAdapter(selCommand)

                If selCommand Is Nothing Or selAdapter Is Nothing Then
                    Throw New Exception("err")
                End If

                selAdapter.Fill(reDS)
                If reDS.Tables.Count <> tables.Count Then
                    Throw New Exception("e")
                End If

                For i As Integer = 0 To tables.Count - 1
                    reDS.Tables(i).TableName = tables(i)
                Next

                Return reDS

            Catch ex As Exception
                Throw ex
            End Try

        End Function

    End Class

End Namespace