SqlConnection事务支持

Question

我试图将事务支持添加到数据库对象中。此对象是远程处理对象。(这可以确保在Windows客户端上不需要数据库连接。)

现在我想增加对事务的支持，基本上，客户端需要这样使用它；

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

​

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

​

因此，我遇到的问题是，当我在上面的方法中使用一个新连接(using conn...)时，我不能真正创建一个事务。

因此，我认为我需要将SqlConnection实例移出方法，并将其转换为类级变量。但我觉得这不是最好的做法。(?) 此外：按顺序执行许多查询会导致一个错误：“内部致命错误”。

有人能为我指明正确的方向吗？我发现的所有示例都是通过直接按顺序执行几个查询，以最简单的方式覆盖事务。

编辑：，有点像this question。(如何将单个SqlTransaction用于.NET中的多个SqlConnections？)。所以也许这是不可能的，但是有解决办法吗？

编辑2:也似乎引发了这个“内部致命错误”，因为该对象是并行使用的。也许这是不可能的？

Answer 1

我认为您需要提高连接类的级别。我要做的是检查是否已经存在连接，如果不创建连接，则检查事务是否需要。跟踪它，直到调用提交或回滚。我不认为在这种情况下使用类级私有字段有什么问题。

就像这样：

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

我没有测试这个，但它应该能让你明白我的意思。只需确保在每一种可能的情况下关闭并处理连接和事务。

Answer 2

我认为您可以将SqlConnection对象作为类的字段。

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

当您使用这个类时，您可以尝试以下代码。

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

你觉得那个怎么样？