Using Connection Based Transactions

Question

2.50/5 (2 votes)

See more:

I have a separate class for Business Logic in App_Code :

These are the two code blocks:

VB

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

VB

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

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:

ASM

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.

Posted 22-Nov-12 20:21pm

Member 7781963

Updated 22-Nov-12 21:08pm

v2

Add a Solution

Comments

earloc 23-Nov-12 2:47am

what do you mean with "apply transaction when calling the functions"? can you provide more details about your purpose?
BTW: are you aware of the possible sql-injection in your solution?

Member 7781963 23-Nov-12 2:55am

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.

Member 7781963 23-Nov-12 2:56am

Where is the SQL injection??

earloc 23-Nov-12 3:50am

everytime you build an sql-command using string concatination you open up your application for possible sql-injection. It seems to me that you are using a custom "framework" for building your queries and i dont know how the method "AddInParameter" is implemented in detail - i assume for know that it uses standard SQL-Parameter to prevent SQL-Injection.
you should always build your commands like:
var commandString = "Insert into XXX (ColumnA, COlumnB) VALUES (@ColumnA, @ColumnB)";
var cmd = new SqlCommand(commandString, connection);
cmd.Parameters.AddWithValue ("@ColumnA", 123); //or a similar approach to "replace" those parameters.

in this way,, the SQLServer can "precompile" the statement and "inject" the parametervalues by itself and check, if the overall command gets broken (by ensuring the dataType of the parameter, etc)

when you build up your statement like:
var cmd = new SqlCommand("INSERT INTO XXX (ColumnA) VALUES ('" + columnA +"' +);

columnA may be defined as "A'); DROP TABLE XXX; --", resulting in a command like:
INSERT INTO XXX (ColumnA) VALUES ('A'); DROP TABLE XXX; --');

even if chances are low that this will actually be exploited(e.g. because the parameter you are concatinating is not provided by direct user input or SqlServer does detecting thos malicious queries), at least the possibility remains, what almost always is a bad code smell.

In your solution, this happens with the "Gender" Parameter in "createNewAccID" and "accID " in "insertIntoMandatorySteps"

Edit1: Codesamples are C#-syntax

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

earloc · Accepted Answer · 2012-11-22T22:14:00

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:

VB

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

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:

VB

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

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:

VB

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:

VB

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

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 :)