Solved

Help with Transactions (several questions and some code)!

Posted on 2008-10-10
7
302 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 10

Expert Comment

by:Smart_Man
ID: 22690656
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
ID: 22701885
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
ID: 22706079
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:CarlaGibson
ID: 22724668

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
ID: 31505167
Thanks!  I'm sorry it took so long to close this question.
0
 

Author Comment

by:CarlaGibson
ID: 22761198
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
ID: 22761323
No problem, glad to help.
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to create and use a custom WaterMark textbox class.  The custom WaterMark textbox class allows you to set the WaterMark Background Color and WaterMark text at design time.   IMAGE OF WATERMARKS STEPS Create VB …
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

691 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