使用基于连接的事务 [英] Using Connection Based Transactions

问题描述

我在App_Code中有一个单独的业务逻辑类:

这是两个代码块:

I have a separate class for Business Logic in App_Code :

These are the two code blocks:

Public Shared Function createNewAccID(ByVal FBAccountID As String, ByVal FirstName As String, ByVal LastName As String, ByVal Email As String, ByVal UserName As String, ByVal Gender As String) As Object
        Dim db As SqlDatabase = Connection.connection
        Try
            Using cmd As SqlCommand = db.GetSqlStringCommand("INSERT INTO UserAccounts (AccountID,FBID,TwitterID,FirstName,LastName,Email,EmailVerified,UserName,Password,Gender,CreateDate,Points,CurrentBadge,Abused,isActive,AccountTypeID) VALUES ('" & BLL.getNewAccID & "',@FBID,'',@FirstName,@LastName,@Email,'" & True & "',@UserName,'','" & Gender & "','" & Date.Now.Date & "'," & 5 & ",'Silver'," & 0 & ",'" & True & "'," & 1 & ")")
                db.AddInParameter(cmd, "FBID", SqlDbType.VarChar, FBAccountID)
                db.AddInParameter(cmd, "FirstName", SqlDbType.VarChar, FirstName)
                db.AddInParameter(cmd, "LastName", SqlDbType.VarChar, LastName)
                db.AddInParameter(cmd, "Email", SqlDbType.VarChar, Email)
                db.AddInParameter(cmd, "UserName", SqlDbType.VarChar, "")
                db.ExecuteNonQuery(cmd)
            End Using
            Return True
        Catch ex As Exception
            'MsgBox(ex.Message)
            Return False
        End Try
    End Function

Public Shared Function insertIntoMandatorySteps(ByVal FBAccountID As String) As Object
        Dim db As SqlDatabase = Connection.connection
        Try
            Dim accID As Integer = getAccIDFromDB(FBAccountID)
            Using cmd As SqlCommand = db.GetSqlStringCommand("INSERT INTO MandatorySteps (AccountID,NextStepID,NextStepName,AllStepsCompleted) VALUES ('" & accID & "',0,'Interests','" & False & "')")
                db.ExecuteNonQuery(cmd)
            End Using
            Return True
        Catch ex As Exception
            'MsgBox(ex.Message)
            Return False
        End Try
    End Function

我从aspx.vb页面调用这些函数，当我在单个按钮click或sub中调用这两个函数时，我想使用基于连接的事务.如何将事务应用于此功能.

我知道如何使用事务，我想知道在调用函数时(实现多个插入时)可以应用事务的任何方式.

只是一个场景:

I call these functions from my aspx.vb page, when i call both the functions in a sinle button click or sub , i want to use connection based transaction. How do i apply transaction to this functions.

I know how to use transactions, i want to know is there any way i can apply transaction when calling the functions (when implementing multiple inserts).

Just a scenario:

1. I am calling both the functions : Here i want that if both the functions execute properly then commit.

2. I am calling only one function i.e function 1 or function 2 : Here i want the transaction to be applied to that particular function only.

You see the functions are in business logic layer and i call the functons from different places, so i need the flexibility to pass the transaction parameter depending upon how many functions i want to execute in a single connection.

推荐答案

使用lambda表达式将您的代码模块化！ br/> 此解决方案利用 Lambda表达式 [关闭 [ ^ ]值得一两章，但您可以实现非常优雅的解决方案.如果有任何不清楚的地方，请随时询问其他问题:

use lambda expressions to modularize your code!
this solutions makes use of Lambda-Expressions[^] and Closures[^] which deserve a chapter or two of its own, but you can achieve very elegant solutions. dont hesitate to ask further question, if anything is not clear:

Sub Main()

        ExecuteWithinTransaction(Sub(con, trans) InsertA("Al", con, trans))
        ExecuteWithinTransaction(Sub(con, trans) InsertB("Bert", con, trans))

        ExecuteWithinTransaction(Sub(con, trans)
                                     InsertA("Albert", con, trans)
                                     InsertB("Einstein", con, trans)
                                 End Sub)
    End Sub

    ''' <summary>
    ''' Executes an action within a transaction. handles connection and transaction management
    ''' </summary>
    ''' <param name="action">The action to be executed</param>
    Sub ExecuteWithinTransaction(ByVal action As Action(Of SqlConnection, SqlTransaction))

        Dim connection As SqlConnection = New SqlConnection("CONNECTIONSTRING")

        connection.Open()
        Try

            Dim transaction = connection.BeginTransaction()

            Try
                action(connection, transaction)
                transaction.Commit()
            Catch ex As Exception
                transaction.Rollback()
                Throw 'rethrow exception preserving original stacktrace
            End Try
        Finally
            connection.Close()
        End Try

    End Sub

    Sub InsertA(ByVal name As String, ByVal connection As SqlConnection, ByVal transaction As SqlTransaction)

        Dim cmd As SqlCommand = New SqlCommand("INSERT INTO XXX (Name) VALUES (@NAME)", connection, transaction)

        cmd.Parameters.AddWithValue("@Name", name)

        cmd.ExecuteNonQuery()

    End Sub

    Sub InsertB(ByVal name As String, ByVal connection As SqlConnection, ByVal transaction As SqlTransaction)

        Dim cmd As SqlCommand = New SqlCommand("INSERT INTO YYY (Name) VALUES (@NAME)", connection, transaction)

        cmd.Parameters.AddWithValue("@Name", name)

        cmd.ExecuteNonQuery()

    End Sub

顺便说一句:这种编码风格在C#中看起来更好；)

在Visual Studio 2010之前的版本中，似乎不支持对ExecuteWithinTransaction的使用调用，因为VS 2010首先引入了Lambdas.但是，自.NET 2.0起，匿名委托就存在了(这使用法变得更难看).
另外，委托动作< t，> .NET 2.0中不存在，因此您必须自己定义它:

btw: this coding style looks nicer in C# ;)

in VisualStudio versions prior 2010 the used call to ExecuteWithinTransaction seems unsupported, as Lambdas where introduced in VS 2010 first. Nevertheless anonymous delegates are around since .NET 2.0 (which makes the usage a bit uglier).
Additional, the delegate Action<t,> was not present in .NET 2.0, so you will have to define it by yourself:

Delegate Sub Action(Of T1, T2)(ByVal Arg1 As T1, ByVal Arg2 As T2)

另外，VS2008/VB.NET 9.0也似乎不支持多行匿名Subs，因此您必须将Subs定义为Function:

also, multi line anonymouse Subs seem to be not supported in VS2008 / VB.NET 9.0 either, so you would have to define the Subs as Functions:

Function InsertA(ByVal name As String, ByVal connection As String, ByVal transaction As String) As Boolean

        Console.WriteLine(name, connection, transaction)
        Return True
    End Function

    Function InsertB(ByVal name As String, ByVal connection As String, ByVal transaction As String) As Boolean

        Console.WriteLine(name, connection, transaction)
        Return True
    End Function

并这样称呼:

and call it this way:

ExecuteWithinTransaction(Function(con As String, trans As String)
                                     InsertA("Albert", con, trans)
                                     InsertB("Einstein", con, trans)
                                     Return True
                                 End Function)

无论如何，此模式都应适用于 VS 2008和.NET 2.0 [ ^ ].

很抱歉造成混乱，但我不是VB开发人员:)

any way, this pattern should be applicable in VS 2008 and .NET 2.0[^] also.

Sorry for the chaos, but i´m not a VB-Developer :)

这篇关于使用基于连接的事务的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！