Solved

Help with Transactions (several questions and some code)!

Posted on 2008-10-10
7
281 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
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

919 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

14 Experts available now in Live!

Get 1:1 Help Now