SqlConnection transaction support

Question

I'm trying to add transaction support to our database object. This object is a Remoting object. (this to ensure that on the Windows Client, no database connection is required.)

Now I want to add support for transactions; Basically, a client will need to use it like this;

1. BeginTransaction
2. ExecuteNonQuery("insert...")
3. ExecuteNonQuery("insert...")
4. Commit / Rollback

Public Function ExecuteNonQuery(ByVal Query As String) As Integer
   Dim intResult As Integer = -1

   Using conn As New SqlConnection(Me.strConnectionString)

       Using cmd As New SqlCommand(Query, conn)

          conn.Open()
          intResult = cmd.ExecuteNonQuery()

       End Using 
   End Using 
   Return intResult 
End Function

So the problem I have is that I can't really create a transaction when I use a new connection (using conn...) in the method above.

So I think I need to move the SqlConnection instance outside of the method and change it into a class-level variable. But this is not best-practice I think. (?) Besides: executing a lot of queries in sequence results in an error: 'internal fatal error'.

Is there anybody who can point me in the right direction? All examples I've found online cover transactions in its simplest way by executing several queries directly in sequence.

edit: it's a little bit like this question. (How to use a single SqlTransaction for multiple SqlConnections in .NET?) . So maybe it is just not possible, but is there a workaround?

edit 2: It also seems that this 'internal fatal error' is raised because the object is used in parallel. Maybe that's just not possible?

Answer 1

I think you can put the SqlConnection object as a field of the class.

Class SQLHelp
    Implements IDisposable
    Private ReadOnly _conn As SqlConnection
    Private _trans As SqlTransaction

    Public ReadOnly Property Connection As SqlConnection
        Get
            Return _conn
        End Get
    End Property

    Sub New(strConnectionString)
        _conn = New SqlConnection(strConnectionString)
        _trans = Nothing
    End Sub

    Public Function ExecuteNonQuery(ByVal Query As String) As Integer
        Dim intResult As Integer = -1

        If _trans Is Nothing Then
            Using cmd As New SqlCommand(Query, _conn)

                If _conn.State <> ConnectionState.Open Then
                    _conn.Open()
                End If
                intResult = cmd.ExecuteNonQuery()

            End Using
        Else
            Using cmd As New SqlCommand(Query, _conn, _trans)

                If _conn.State <> ConnectionState.Open Then
                    _conn.Open()
                End If
                intResult = cmd.ExecuteNonQuery()

            End Using
        End If
        Return intResult
    End Function

    Public Function BeginTransaction() As SqlTransaction
        _trans = _conn.BeginTransaction()
        Return _trans
    End Function

    Public Sub SubmitTransaction()
        If Not (_trans Is Nothing) Then
            _trans.Commit()
        End If
        _trans = Nothing
    End Sub

    Public Sub RollbackTransaction()
        If Not (_trans Is Nothing) Then
            _trans.Rollback()
        End If
    End Sub

    Public Sub Dispose() Implements IDisposable.Dispose

        If Not (_trans Is Nothing) Then
            _trans.Dispose()
        End If

        _conn.Close()
        _conn.Dispose()

    End Sub
End Class

When you are using this class, you can try the following code.

    Using _sqlHelp As New SQLHelp(connectionString)
       _sqlHelp.BeginTransaction()
       'ExecuteNonQueryCode
       _sqlHelp.Commit() 'Or _sqlHelp.Rollback()
    End Using

What do you think about it?

Answer 2

I think you will need to make the connection class level. What I would do is check to see if there is already a connection and then if not create that and the transaction if desired. Tracking it until commit or rollback is called. I don't think there is anything wrong with using a class level private field in a case like this.

Something like this:

Public Class SqlHandler
    Implements IDisposable

    Private mConnection As SqlConnection
    Private mTransaction As SqlTransaction
    Private strConnectionString As String

    Public Sub New()

    End Sub

    Public Sub New(UseTransaction As Boolean)
        mConnection = New SqlConnection
        If UseTransaction Then
            mTransaction = mConnection.BeginTransaction()
        End If
    End Sub

    Public Function ExecuteNonQuery(ByVal Query As String) As Integer
        Dim intResult As Integer = -1

        Using mConnection As SqlConnection
            Using cmd As New SqlCommand(Query, mConnection, mTransaction)
                cmd.Transaction = mTransaction
                mConnection.Open()
                intResult = cmd.ExecuteNonQuery()
            End Using
        End Using
        Return intResult
    End Function

    Public Sub Commit()
      If mTransaction IsNot Nothing Then
        mTransaction.Commit()
        mTransaction.Dispose()
      End If
          mConnection.Close()
          mConnection.Dispose()
    End Sub

#Region "IDisposable Support"
    Private disposedValue As Boolean ' To detect redundant calls

    Protected Overridable Sub Dispose(disposing As Boolean)
        If Not Me.disposedValue Then
            If disposing Then
                If mTransaction IsNot Nothing Then
                    mTransaction.Rollback()
                    mTransaction.Dispose()
                End If
                If mConnection IsNot Nothing Then
                    mConnection.Close()
                    mConnection.Dispose()
                End If
            End If
        End If
        Me.disposedValue = True
    End Sub

    Public Sub Dispose() Implements IDisposable.Dispose
        ' Do not change this code.  Put cleanup code in Dispose(disposing As Boolean) above.
        Dispose(True)
        GC.SuppressFinalize(Me)
    End Sub
#End Region

End Class

I didn't test this, but it should give you the idea of what I mean. Just make sure to close and dispose of your connection and transaction under each possible set of circumstances.