Solved

Help with Transactions (several questions and some code)!

Posted on 2008-10-10
7
276 Views
Last Modified: 2012-05-05
I'm new to VB.NET, but have been using VB6 (with Transactions) for several years.  I have several functions that call Update/Insert Stored Procedures that I need transactions for.

Scenario 1:  Function 1 calls Function 2 (Example: Function 1 Updates a record and Function 2 Inserts a history record into a history table)

Scenario 2: Function 2 is called by itself

Function 2 needs to be able to use the Connection/Transaction that was passed in, or create it's own Connection/Transaction if one wasn't passed in.

Question 1: Is there a better way of doing this than what I have?

Question 2: In the Catch in Function 2, I don't want to Rollback the Transaction here if the Function didn't begin it.  Or if I do roll it back here, will it generate another exception when it tries to roll it back in the calling function?

Question 3: In Function 1, if I keep the code the way it is, will the Rollback in Catch work properly since the connection and transaction are set up in the Using scope?  I don't get an error in my error list (and the code is not finished enough to run it yet).

Here is a representation of what I currently have:



Public Function Function1(ByVal intParameterToUpdate As Integer) As Integer
 

    Dim intReturnVal As Integer = 0
 

    Dim tx As SqlTransaction = Nothing

    Dim strStoredProcedure As String = "{Name of Update Stored Procedure}"
 

    Try

        Using cn As SqlConnection = GetSqlConnection, cm As New SqlCommand(strStoredProcedure, cn, tx)
 

            cm.CommandType = CommandType.StoredProcedure

            cm.Transaction = tx
 

            cm.Parameters.AddWithValue("@ParamToUpdate", intParameterToUpdate)
 

            cn.BeginTransaction()
 

            intReturnVal = cm.ExecuteNonQuery()
 

            If intReturnVal > 0 Then

                intReturnVal = Function2(intParameterToUpdate, cn, tx)

            End If
 

            If intReturnVal > 0 Then

                tx.Commit()

            Else

                tx.Rollback()

            End If
 

        End Using
 

    Catch ex As Exception

        tx.Rollback()

        Throw
 

    End Try
 

    Return intReturnVal
 

End Function
 

Public Function Function2(ByVal intParameterToInsert As Integer, Optional ByVal cn As SqlConnection = Nothing, _

                          Optional ByVal tx As SqlTransaction = Nothing) As Integer
 

    Dim intReturnVal As Integer = 0
 

    Dim strStoredProcedure As String = "{Name of Insert Stored Procedure}"
 

    If cn Is Nothing Then

        cn As SqlConnection = GetSqlConnection

    End If
 

    Try

        Using cm As New SqlCommand(strStoredProcedure, cn, tx)
 

            cm.CommandType = CommandType.StoredProcedure

            cm.Transaction = tx
 

            cm.Parameters.AddWithValue("@ParamToInsert", intParameterToInsert)
 

            cn.BeginTransaction()
 

            intReturnVal = cm.ExecuteNonQuery()
 

            If intReturnVal >= 0 Then

                tx.Commit()

            Else

                tx.Rollback()

            End If
 

        End Using
 

    Catch ex As Exception

        If {Transaction was started} Then tx.Rollback()

        Throw
 

    End Try
 

    Return intReturnVal
 

End Function

Open in new window

0
Comment
Question by:CarlaGibson
  • 4
  • 2
7 Comments
 
LVL 10

Expert Comment

by:Smart_Man
Comment Utility
Question 1: Is there a better way of doing this than what I have?

i think you might use SQL routine to do your bussiness instead of multiple functions.

you might think of using triggers to update the db history table too.


what is the db you are using ?

0
 

Author Comment

by:CarlaGibson
Comment Utility
What is a SQL routine?

I can't add any triggers to the database.  

That was just an example code.  The second function doesn't necessarily update a history table.  I need to be able to pass connections and transactions around to different functions.  They each have their own stored procedure that they call.  And each function can be called by multiple other functions that either pass in a connection/transaction that has already been started, or the function has to start it's own connection/transaction if none was passed in.

I can't change the way the code interacts with the database or the other functions, I can only change the way the code behaves within the function itself.

I'm using SQL Server 2005.

0
 
LVL 8

Accepted Solution

by:
mkosbie earned 500 total points
Comment Utility
You're assigning the transaction variable incorrectly.  The way you have the code written right now, the variable tx effectively does nothing.  To get a reference to the SqlTransaction object associated with the connection, you need to capture cn.BeginTransaction's return value.  Check my updated code below.  Also, for more on SqlTransaction, check MSDN: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction.aspx.

Question 1: Without knowing more about your overall DB/Program design, I don't think I can comment on this.  On the level of these two functions, this seems reasonable to me.

Question 2: Why don't you use a boolean to track whether or not you were originally passed the transaction?  Check updated code below.

Question 3: The transaction (tx) is declared above the using scope in the try scope, so it should still be available in catch scope.  It should also maintain any references it needs to the connection (cn) and command (cm) until it's destroyed.
Public Function Function1(ByVal intParameterToUpdate As Integer) As Integer

 

    Dim intReturnVal As Integer = 0

 

    Dim tx As SqlTransaction

    Dim strStoredProcedure As String = "{Name of Update Stored Procedure}"

 

    Try

        Using cn As SqlConnection = GetSqlConnection, cm As New SqlCommand(strStoredProcedure, cn)

 

            cm.CommandType = CommandType.StoredProcedure

 

            cm.Parameters.AddWithValue("@ParamToUpdate", intParameterToUpdate)

 

            tx = cn.BeginTransaction()

 

            intReturnVal = cm.ExecuteNonQuery()

 

            If intReturnVal > 0 Then

                intReturnVal = Function2(intParameterToUpdate, cn, tx)

            End If

 

            If intReturnVal > 0 Then

                tx.Commit()

            Else

                tx.Rollback()

            End If

 

        End Using

 

    Catch ex As Exception

        tx.Rollback()

        Throw

 

    End Try

 

    Return intReturnVal

 

End Function

 

Public Function Function2(ByVal intParameterToInsert As Integer, Optional ByVal cn As SqlConnection = Nothing, _

                          Optional ByVal tx As SqlTransaction = Nothing) As Integer

 

    Dim isMyTransaction As Boolean = False

    Dim intReturnVal As Integer = 0

 

    Dim strStoredProcedure As String = "{Name of Insert Stored Procedure}"

 

    If cn Is Nothing Then

        cn As SqlConnection = GetSqlConnection

    End If

 

    Try

        Using cm As New SqlCommand(strStoredProcedure, cn)

 

            cm.CommandType = CommandType.StoredProcedure

 

            cm.Parameters.AddWithValue("@ParamToInsert", intParameterToInsert)

 

            If tx Is Nothing Then

                tx = cn.BeginTransaction()

                isMyTransaction = True

            Else

 

            intReturnVal = cm.ExecuteNonQuery()

 

            If intReturnVal >= 0 Then

                tx.Commit()

            Else

                tx.Rollback()

            End If

 

        End Using

 

    Catch ex As Exception

        If isMyTransaction Then tx.Rollback()

        Throw

 

    End Try

 

    Return intReturnVal

 

End Function

Open in new window

0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:CarlaGibson
Comment Utility

My supervisor took me off this project for a few days to do something else.  I will be back on it next week and will update and/or close out this question then.


0
 

Author Closing Comment

by:CarlaGibson
Comment Utility
Thanks!  I'm sorry it took so long to close this question.
0
 

Author Comment

by:CarlaGibson
Comment Utility
Thanks for your help.  Your solution worked well along with some advice from a co-worker.  Sorry it took so long to close out this question.
0
 
LVL 8

Expert Comment

by:mkosbie
Comment Utility
No problem, glad to help.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now