Initiating a TransactionScope from Excel VBA?

Question

I have some existing c# code that I'd like to expose via com interop so that it can be called from Excel VBA. As I will be performing many nested batch update operations, I need to support transactions, and to minimize the refactoring required at the c# level, I'd like to use the TransactionScope approach:

Implementing an Implicit Transaction using Transaction Scope
http://msdn.microsoft.com/en-us/library/ms172152.aspx

The TransactionScope class provides a simple way to mark a block of code as participating in a transaction, without requiring you to interact with the transaction itself. A transaction scope can select and manage the ambient transaction automatically. Due to its ease of use and efficiency, it is recommended that you use the TransactionScope class when developing a transaction application.

In addition, you do not need to enlist resources explicitly with the transaction. Any System.Transactions resource manager (such as SQL Server 2005) can detect the existence of an ambient transaction created by the scope and automatically enlist.

My question is: is it possible to initiate the TransactionScope within the VBA code (or call a c# method via COM interop to instantiate and return a TransactionScope object), and then proceed to call various other c# objects via COM Interop, which will all automatically participate in the single root transaction?

Answer 1

I don't see a problem with your approach. You don't have using in VBA, so you will have to simulate it by

• using an error handler and
• being very careful not to leave the method without disposing the TransactionScope in one way or another.

For example, let's say that in your C# application, you provide the following methods:

public TransactionScope BeginTransactionScope()
{
    return new TransactionScope();
}

public void CommitTransactionScope(TransactionScope scope)
{
    scope.Complete();
    scope.Dispose();  // simulates leaving the using { ... } scope
}

public void RollbackTransactionScope(TransactionScope scope)
{
    scope.Dispose();  // simulates leaving the using { ... } scope
}

// Does some work using Connections and maybe nested scopes
public void DoSomeWork1() { ... }
public void DoSomeWork2() { ... }
public void DoSomeWork3() { ... }

You compile your C# DLL and provide it to Excel VBA via COM interop. In VBA, you run the methods as follows:

Private Sub MySub()

    On Error Goto MyErrorHandler

    ...
    Dim scope As Object
    Set scope = myCsObject.BeginTransactionScope()
    ...
    myCsObject.DoSomeWork1
    ...
    myCsObject.DoSomeWork2
    ...
    myCsObject.DoSomeWork3
    ...
    myCsObject.CommitTransactionScope scope
    Set scope = Nothing
    ...

    Exit Sub

MyErrorHandler:
    If Not (scope Is Nothing) Then
        myCsObject.RollbackTransactionScope scope
    End If
    ' Remainder of your error handler goes here
    ...
End Sub

Note, though, that any data access you perform with VBA code (in between the DoSomeWork calls) will be outside the transaction scope, since TransactionScope is not compatible with classic ADO or DAO.