TransactionScope Does this work??

[Moorzee]

    Public Sub PerformDBWork()
        Try
            Using scope As New TransactionScope
                Using cnBusinessDB
                    'Open connection to Business DB
                    cnBusinessDB.Open()
                    'Do Business operations
                    DelAnyExistingAddress(UserID)
                    InsertAddress()

                    Using cnFinancialDB
                        'Open connection to Financial DB
                        cnFinancialDB.Open()
                        'Do Financial operations
                        InsertPOHeaderRecord()
                        InsertPODetailsRecords()
                    End Using

                End Using
                scope.Complete()
            End Using
        Catch txnEx As TransactionAbortedException
            RaiseEvent Transaction_Failure(MSG_TXN_ABORTED.Replace("%1", txnEx.Message.ToString()))
        Catch ex As Exception
            RaiseEvent Unknown_Exception(MSG_UNEXPECTED_ISSUE.Replace("%1", ex.Message.ToString()))
        End Try

    End Sub

Can I perform the actual database operations in routines by calling them inside the TransactionScope block if my connection objects are declared at module level?

Cheers.

[jmcilhinney]

Where your connection variables are declared is irrelevant, as is where your connection objects are created. All that matters is where the connection is opened. If a connection is opened outside the TransactionScope then commands executed over that connection will not be enlisted. Conversely, if a connection is opened within a TransactionScope then all commands executed over that connection within the TransactionScope will automatically be enlisted.

[Moorzee]

Thanks for that JMc. Mind if I ask another question? Would I handle oleDBExceptions in my InsertPOHeader routine or let my Catch txnEx in PerformDBWork handle the issue?

Cheers for your help pal.

[jmcilhinney]

Given that an OleDbException is specifically related to data access, it should probably not leave the data access layer. Currently it would be caught by your second Catch block.

[Moorzee]

Sorry my question wasn't very clear. Currently my InsertPOHeaderRecord routine has exception handling for oledbexceptions. I simply raise an event out of my class for the client consuming the class to inform users of issue.

My question is would you raise an event for the oledbexception in InsertPOHeaderRecord and also raise an event in the PerformDBWork routine for the transactionscope failure?

Cheers.