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 Gates.DA
Imports System.Data
Imports System.Data.SqlClient

Namespace DA

    Public Class DBTransaction

        Dim _DBConnector As DBConnector
        Dim _DBTransaction As 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 Sub BeginTransactio()

            Try
                If _DBConnector Is Nothing Then
                    _DBConnector.GetDBConnection()
                End If

                If _DBConnector.DBConnector.State = ConnectionState.Open Then
                    _DBConnector.OpenDBConnection()

                End If
                _DBTransaction = _DBConnector.DBConnector.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
            Finally
                _DBTransaction.Dispose()
                _DBConnector.CloseDBConnection()
                _isTransaction = False

            End Try
        End Sub
        Public Sub RollbackTransaction()
            Try

                If Not _DBTransaction Is Nothing Then
                    _DBTransaction.Rollback()

                    _DBTransaction.Dispose()

                End If
                _DBConnector.CloseDBConnection()
                _isTransaction = False

            Catch ex As Exception

            End Try
        End Sub

        Private Function getCommandTrans(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.DBConnector
                If isSP Then
                    selCommand.CommandType = CommandType.StoredProcedure
                Else
                    selCommand.CommandType = CommandType.Text
                End If

                selCommand.CommandText = commandText

                If _TimeOut > 0 Then
                    selCommand.CommandTimeout = _TimeOut
                End If
                If _isTransaction Then
                    selCommand.Transaction = _DBTransaction
                End If

                Return selCommand

 

            Catch ex As Exception
                RollbackTransaction()

                Throw ex
            End Try
        End Function

        Private Function getAdapterTrans(ByVal command As IDbCommand) As IDbDataAdapter

            Try

                Return New System.Data.SqlClient.SqlDataAdapter(command)

            Catch ex As Exception
                Throw ex

            End Try
        End Function

        Public Function SQLToDataset(ByVal tableName As String, ByVal strSQL As String) As DataSet
            Dim reDS As New DataSet
            Dim selCommand As IDbCommand
            Dim selAdpatee As IDataAdapter

            Try

                Dim tables() As String
                tables = tableName.Split("|")

                selCommand = getCommandTrans(strSQL)
                selAdpatee = getAdapterTrans(selCommand)

                selAdpatee.Fill(reDS)

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

                Return reDS

            Catch ex As Exception
                Throw ex

            End Try

        End Function

        Public Function ExecSQL(ByVal strSql As String) As Boolean

            Dim selCommand As IDbCommand

            Try
                selCommand = getCommandTrans(strSql)
                selCommand.ExecuteNonQuery()

                Return True
            Catch ex As Exception
                Return False
                Throw

            End Try

        End Function

 

    End Class

End Namespace