Public Class TestDbConnection

 

    Public Sub TestAllApproaches()

 

        BasicPattern()

        SimpleTransactionPattern()

 

        UsingDbConnectionSub() ' Simple call

        UsingDbConnectionRoot() ' Cascade call

        UsingDbConnectionNestedTransactionTest() ' Nested sql transactions test

 

    End Sub

 

    Public Sub BasicPattern()

        Using conn As New SqlConnection(Common.GetConnectionString)

            conn.Open()

 

            Dim comm As New SqlCommand("Users_DoStuff", conn)

            comm.CommandType = Data.CommandType.StoredProcedure

            comm.ExecuteNonQuery()

        End Using

    End Sub

 

    Public Sub BasicPattern(ByVal trans As SqlTransaction)

        Dim comm As New SqlCommand("Users_DoStuff", trans.Connection, trans)

        comm.CommandType = Data.CommandType.StoredProcedure

        comm.ExecuteNonQuery()

    End Sub

 

    Public Sub SimpleTransactionPattern()

        Using conn As New SqlConnection(Common.GetConnectionString)

            conn.Open()

 

            Dim trans As SqlTransaction = conn.BeginTransaction

            Dim comm As New SqlCommand("Users_BeginDoingStuff", conn, trans)

            comm.CommandType = Data.CommandType.StoredProcedure

            comm.ExecuteNonQuery()

 

            BasicPattern(trans)

        End Using

    End Sub

 

    Public Sub UsingDbConnectionRoot()

        Using conn As New DbConnection(True)

            Dim comm As SqlCommand = conn.CreateCommand("Users_BeginDoingStuff")

            comm.CommandType = Data.CommandType.StoredProcedure

            comm.ExecuteNonQuery()

 

            BasicPattern(conn.Transaction)

            UsingDbConnectionSub(conn)

        End Using

    End Sub

 

    Public Sub UsingDbConnectionSub(Optional ByVal transaction As DbConnection = Nothing)

        Using conn As New DbConnection(False, transaction)

            Dim comm As SqlCommand = conn.CreateCommand("Users_DoStuff")

            comm.CommandType = Data.CommandType.StoredProcedure

            comm.ExecuteNonQuery()

        End Using

    End Sub

 

    Public Sub UsingDbConnectionNestedTransactionTest()

        Using conn As New DbConnection(True)

            Dim comm As SqlCommand = conn.CreateCommand("Users_BeginDoingStuff")

            comm.CommandType = Data.CommandType.StoredProcedure

            comm.ExecuteNonQuery()

 

            ' New transaction nested on the first one

            Using connNested As New DbConnection(True, conn)

                Dim commNested As SqlCommand = conn.CreateCommand("Users_BeginDoingStuff")

                commNested.CommandType = Data.CommandType.StoredProcedure

                commNested.ExecuteNonQuery()

 

                ' Roll's back the first transaction too

                connNested.RollbackTransaction()

            End Using

        End Using

    End Sub

End Class

Imports System.Data.SqlClient

Imports Common

 

Public Class DbConnection

    Implements IDisposable

 

    Protected ReadOnly m_connection As SqlConnection

    Protected ReadOnly m_transactions As Stack(Of SqlTransaction)

    Protected m_transactionStatus As TransactionStatusEnum

 

    Protected m_open As Boolean

    Protected m_disposed As Boolean

    Protected ReadOnly m_disposable As Boolean

 

    Public Sub New(ByVal enforceTransaction As Boolean)

        Me.New(enforceTransaction, Nothing)

    End Sub

 

    Public Sub New(ByVal enforceTransaction As Boolean, ByVal connection As DbConnection)

        m_transactionStatus = TransactionStatusEnum.Inherited

        m_disposed = False

 

        If connection Is Nothing Then

            m_open = False

            m_disposable = True

 

            m_transactions = New Stack(Of SqlTransaction)

            m_connection = New SqlConnection(Common.GetConnectionString)

 

            Me.OpenConnection()

 

            If enforceTransaction Then

                Me.BeginTransaction()

            End If

        Else

            If Not connection.m_disposed Then

                m_connection = connection.m_connection

                m_transactions = connection.m_transactions

                m_open = connection.m_open

 

                m_disposable = False

            Else

                Throw New ObjectDisposedException(Me.GetType.ToString)

            End If

        End If

    End Sub

 

    Protected Sub OpenConnection()

        If Not m_disposed Then

            If Not m_open Then

                m_open = True

                m_connection.Open()

            End If

        Else

            Throw New ObjectDisposedException(Me.GetType.ToString)

        End If

    End Sub

 

    Protected Sub BeginTransaction()

        If Not m_disposed Then

            m_transactions.Push(m_connection.BeginTransaction())

            m_transactionStatus = TransactionStatusEnum.Open

        Else

            Throw New ObjectDisposedException(Me.GetType.ToString)

        End If

    End Sub

 

    Protected ReadOnly Property CurrentTransaction() As SqlTransaction

        Get

            If m_transactions.Count > 0 Then

                Return m_transactions.Peek()

            Else

                Return Nothing

            End If

        End Get

    End Property

 

    Public Function CreateCommand(ByVal str As String) As SqlCommand

        If Not m_disposed Then

            If CurrentTransaction Is Nothing Then

                Return New SqlCommand(str, m_connection)

            Else

                Return New SqlCommand(str, m_connection, CurrentTransaction)

            End If

        Else

            Throw New ObjectDisposedException(Me.GetType.ToString)

        End If

    End Function

 

    Public Sub RollbackTransaction()

        If Not m_disposed Then

            If m_transactionStatus = TransactionStatusEnum.Open Then

 

                While m_transactions.Count > 0

                    m_transactions.Pop().Rollback()

                End While

                m_transactionStatus = TransactionStatusEnum.RolledBack

 

            ElseIf m_transactionStatus = TransactionStatusEnum.Commited _

                    Or m_transactionStatus = TransactionStatusEnum.RolledBack Then

 

                Throw New InvalidOperationException( _

                    "Cannot rollback commited or rolledback transaction")

            End If

        Else

            Throw New ObjectDisposedException(Me.GetType.ToString)

        End If

    End Sub

 

    Public Sub SaveTransaction(ByVal savePointName As String)

        If Not m_disposed Then

            If m_transactionStatus = TransactionStatusEnum.Open Then

 

                m_transactions.Peek().Save(savePointName)

 

            ElseIf m_transactionStatus = TransactionStatusEnum.Commited _

                    Or m_transactionStatus = TransactionStatusEnum.RolledBack Then

 

                Throw New InvalidOperationException( _

                    "Cannot save rolledback or commited transaction")

            End If

        Else

            Throw New ObjectDisposedException(Me.GetType.ToString)

        End If

    End Sub

 

    Public Sub CommitTransaction()

        If Not m_disposed Then

            If m_transactionStatus = TransactionStatusEnum.Open Then

 

                m_transactions.Peek().Commit()

                m_transactions.Pop()

                m_transactionStatus = TransactionStatusEnum.Commited

 

            ElseIf m_transactionStatus = TransactionStatusEnum.Commited _

                    Or m_transactionStatus = TransactionStatusEnum.RolledBack Then

                Throw New InvalidOperationException( _

                    "Cannot commit commited or rolledback transaction")

            End If

        Else

            Throw New ObjectDisposedException(Me.GetType.ToString)

        End If

    End Sub

 

    <Obsolete("Exists only for standart framework compatibility but unsafe to use")> _

    Public ReadOnly Property Transaction() As SqlTransaction

        Get

            Return CurrentTransaction

        End Get

    End Property

 

    Public Sub Dispose() Implements IDisposable.Dispose

        If Not m_disposed Then

            If m_disposable Then

                If m_transactions.Count > 0 Then

                    Throw New InvalidOperationException( _

                        "DbConnection disposed before all transaction are commited.")

                End If

 

                m_connection.Dispose()

                m_disposed = True

            Else

                If m_transactionStatus = TransactionStatusEnum.Open Then

 

                    ' if the transaction whos'nt commited

                    RollbackTransaction()

                End If

            End If

        End If

    End Sub

 

    Protected Enum TransactionStatusEnum

        Inherited

        Open

        Commited

        RolledBack

    End Enum

 

End Class