Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

Help with Transactions (several questions and some code)!

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
CarlaGibson
Asked:
CarlaGibson
  • 4
  • 2
1 Solution
 
Smart_ManCommented:
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
 
CarlaGibsonAuthor Commented:
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
 
mkosbieCommented:
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
Industry Leaders: 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!

 
CarlaGibsonAuthor Commented:

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
 
CarlaGibsonAuthor Commented:
Thanks!  I'm sorry it took so long to close this question.
0
 
CarlaGibsonAuthor Commented:
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
 
mkosbieCommented:
No problem, glad to help.
0

Featured Post

Independent Software Vendors: 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!

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now