Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
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
 

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.
Posted 22-Nov-12 20:21pm
Edited 22-Nov-12 21:08pm
v2
Comments
Earloc at 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 at 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 at 23-Nov-12 2:56am
   
Where is the SQL injection??
Earloc at 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

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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
 
btw: this coding style looks nicer in C# Wink | ;)
 
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)
 
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)
 

 

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 Smile | :)
  Permalink  
v7
Comments
Member 7781963 at 23-Nov-12 4:29am
   
Thanks a lot for the detailed explanation. Ummm...the "Sub Main()" and "Sub ExecuteWithinTransaction" should be in the calling(aspx.vb) page , right?
So all i need to do is add two parameters (connection and transaction) to my functions??
Earloc at 23-Nov-12 4:36am
   
1. Sub Main() is just a sample that demonstrates the usage of this pattern.
2. the "location" of "ExecuteWithinTransaction" depends on your architecture...maybe it can be integrated in your SqlDatabase-class for general purpose.
3. The additional parameters would do the trick, even though this pattern can be more generalized in order to get less verbose method-signatures.
 
if it helped, please rate and mark the question as solved!
Member 7781963 at 23-Nov-12 4:43am
   
Do i need to import anything?? I get this error "Visual Basic 9.0 does not support this kind of lambda expression" here " ExecuteWithinTransaction(Sub(con, trans) InsertA("Al", con, trans))" when i hover over the Sub
Member 7781963 at 23-Nov-12 4:40am
   
Do i need to import anything?? I get this error "Visual Basic 9.0 does not support this kind of lambda expression" here " ExecuteWithinTransaction(Sub(con, trans) InsertA("Al", con, trans))" for the Sub
Earloc at 23-Nov-12 4:55am
   
see my improved solution! are you using VS2008 / .NET 2.0 ?
Member 7781963 at 23-Nov-12 5:05am
   
i am using VS2010 and .NET 3.5 ...but still does not work.
Member 7781963 at 23-Nov-12 5:08am
   
Tried the new pattern , but no luck.
Member 7781963 at 23-Nov-12 5:24am
   
Where do i use the delegate? I mean where does the delegate go?
Earloc at 23-Nov-12 5:31am
   
You don´t have to define the Action<t1,> delegate, if you are using .NET 3.5.
And even if you had to, i could not answer this question, as it depends on your project´s structure and architecture...
Just make sure that it can bee "seen" by the calling site ;)
mayby try out the VS2008 (VB.NET 9.0) fallback that i provided, as the error message you are facing implies that you are using VS2008.
Member 7781963 at 23-Nov-12 5:36am
   
Thanks a lot for your time and effort. + 5. Accepted Answer.
Earloc at 23-Nov-12 5:39am
   
you´re welcome! glad could´ve helped you!
Member 7781963 at 23-Nov-12 6:54am
   
we need to write the helper subs in the ordinary, non-lambda form, and then pass them as delegates to ExecuteWithinTransaction
 
private sub Lambda1 (con as SqlConnection, tran as SqlTransaction)
InsertA("Al", con, tran)
end sub

private sub Lambda2 (con as SqlConnection, tran as SqlTransaction)
InsertB("Bert", con, tran)
end sub

private sub Lambda3 (con as SqlConnection, tran as SqlTransaction)
InsertA("Albert", con, tran)
InsertB("Einstein", con, tran)
end sub
 
And then
 
ExecuteWithinTransaction (AddressOf Lambda1)
ExecuteWithinTransaction (AddressOf Lambda2)
ExecuteWithinTransaction (AddressOf Lambda3)
Earloc at 23-Nov-12 8:00am
   
strange behavior...sure, this would be a workaround for the lack of lambda-support. but how do you pass in the variables to "Lambda1" when the "Albert"-string is not a constant without introducing new fields (which would mimic the Closure in a minimalistic way)?
 
i´m suprised that my original solution does not work for you, as i did not encounter any problems with VS2010 / VB.NET 10.0 (even on .NET 2.0)

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

  Print Answers RSS
0 OriginalGriff 490
1 Gihan Liyanage 338
2 ChauhanAjay 180
3 Vinay Mistry 160
4 Sergey Alexandrovich Kryukov 153
0 Sergey Alexandrovich Kryukov 9,011
1 OriginalGriff 7,941
2 CPallini 2,603
3 Richard MacCutchan 2,121
4 Abhinav S 1,928


Advertise | Privacy | Mobile
Web03 | 2.8.140827.1 | Last Updated 23 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100