asp.net & SQL Server + Transaction Rollback

Question

I have a portion of my function below which retrieves an autoID from the first SProc and pass it into another SProc as a parameter. Here I am using a Transaction rollback, let's say I have a senario whereby the first SProc executed everything successfully but encounters a problem in the second SProc, will the first SProc operation be rolled back as well together with the second SProc? All along I only have to deal with one SProc transaction but this is abit different for me. Thanks.

Public Shared Function VoucherRedemption(ByVal dbTrans As DbTransaction _
                                      , ByVal dbConnection As DbConnection _
                                      , ByVal receiptNo As String _
                                      , ByVal voucherNo As String _
                                      , ByVal customerCode As String) As Boolean
Dim dbCommand As DbCommand = Nothing

Try
            If DbConnection.State <> ConnectionState.Open Then
                DbConnection.Open()
            End If

            dbCommand = GetStoredProcedureCommand("Mem_Redeem")
            dbCommand.Connection = DbConnection
            dbCommand.Transaction = dbTrans

            AddInParameter(dbCommand, "@ReceiptNo", DbType.String, 50, DBNull.Value)
            If Not String.IsNullOrEmpty(receiptNo) Then
                dbCommand.Parameters("@ReceiptNo").Value = receiptNo
            End If

            AddOutParameter(dbCommand, "@OutAutoIDs", DbType.String, 4000, DBNull.Value)

            ExecuteNonQuery(dbCommand)

            Dim outAutoIDs As String = CType(dbCommand.Parameters("@OutAutoIDs").Value, String)

            dbCommand = GetStoredProcedureCommand("Mem_Redeem_Log_Add")
            dbCommand.Connection = DbConnection
            dbCommand.Transaction = dbTrans

            AddInParameter(dbCommand, "@VoucherNo", DbType.String, 50, DBNull.Value)
            dbCommand.Parameters("@VoucherNo").Value = voucherNo 

            AddInParameter(dbCommand, "@RedeemTransactAutoID", DbType.String, 4000, DBNull.Value)
            dbCommand.Parameters("@RedeemTransactAutoID").Value = outAutoIDs

     ExecuteNonQuery(dbCommand)

        Catch ex As Exception
            Throw New DALException(ex, dbCommand, customerCode, "VoucherRedemption")
        Finally
            If Not dbCommand Is Nothing Then
                dbCommand.Dispose()
            End If
            If Not dbTrans Is Nothing Then
                dbTrans.Dispose()
            End If
        End Try

bitoshi.n · Accepted Answer · 2012-05-04 03:11:09Z

It looks like you are using 2 storedprocedure. Use TransactionScope instead of DbTransaction So, here may for your code

Public Shared Function VoucherRedemption(ByVal dbTrans As DbTransaction _
                                      , ByVal dbConnection As DbConnection _
                                      , ByVal receiptNo As String _
                                      , ByVal voucherNo As String _
                                      , ByVal customerCode As String) As Boolean
Dim dbCommand As DbCommand = Nothing
Using scope As New TransactionScope()
Try 
            If DbConnection.State <> ConnectionState.Open Then
                DbConnection.Open()
            End If

            dbCommand = GetStoredProcedureCommand("Mem_Redeem")
            dbCommand.Connection = DbConnection

            AddInParameter(dbCommand, "@ReceiptNo", DbType.String, 50, DBNull.Value)
            If Not String.IsNullOrEmpty(receiptNo) Then
                dbCommand.Parameters("@ReceiptNo").Value = receiptNo
            End If

            AddOutParameter(dbCommand, "@OutAutoIDs", DbType.String, 4000, DBNull.Value)

            ExecuteNonQuery(dbCommand)

            Dim outAutoIDs As String = CType(dbCommand.Parameters("@OutAutoIDs").Value, String)

            dbCommand = GetStoredProcedureCommand("Mem_Redeem_Log_Add")
            dbCommand.Connection = DbConnection

            AddInParameter(dbCommand, "@VoucherNo", DbType.String, 50, DBNull.Value)
            dbCommand.Parameters("@VoucherNo").Value = voucherNo 

            AddInParameter(dbCommand, "@RedeemTransactAutoID", DbType.String, 4000, DBNull.Value)
            dbCommand.Parameters("@RedeemTransactAutoID").Value = outAutoIDs

     ExecuteNonQuery(dbCommand)

        Catch ex As Exception
            Throw New DALException(ex, dbCommand, customerCode, "VoucherRedemption")
        Finally
            If Not dbCommand Is Nothing Then
                dbCommand.Dispose()
            End If
        End Try
    scope.Complete()
End Using

Collectives™ on Stack Overflow

asp.net & SQL Server + Transaction Rollback

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related