?
Solved

Help with Transactions (several questions and some code)!

Posted on 2008-10-10
7
Medium Priority
?
305 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 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses
Course of the Month13 days, 18 hours left to enroll

800 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