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?

Could you elaborate (hopefully I'm not kinda asking you to prove a negative) — tbone, Jun 19 '14 at 17:08
I have deleted my answer as I dont think we're on the same page understanding what the TransactionScope is for C# and what it would be in VBA. ADODB implements the mechanism to roll back any changes during a Transaction but you can't wrap a block of any VBA code in a TransactionScope and have it rolled back if the Transaction failed... — , Jun 23 '14 at 15:23

Heinzi · Accepted Answer · 2014-06-20T09:31:16.793

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.

edited Jun 20 '14 at 09:31

answered Jun 20 '14 at 09:25

Heinzi

167,459
57
363
519

I am sorry I may be misunderstanding something here but what is the point of all of this since if you are using C# to handle the transactions and just explicitly calling it from VBA. You may as well handle the entire transaction process in C# and just have a method to return the result. Why expose it all to VBA since it can be easily handled from C#? – Jun 20 '14 at 09:32
@mehow: 1. Thanks for spotting the typo, fixed. 2. Maybe he needs to execute VBA code in between the various `DoSomeWork` calls? Anyway, it's a good question, but it's probably better aimed at the OP than at me. – Heinzi Jun 20 '14 at 09:33
yeah you don't have `using` in VBA but you have `with – Jun 20 '14 at 09:35
@mehow: True, `using` and `With` are similar in that they both create some kind of scope, but `using` calls `Dispose` at the end of its scope, whereas `With` only provides a lexical shortcut for accessing fields and methods. – Heinzi Jun 20 '14 at 09:37
yes, but you can create a custom class that implements IDispose and it will eventually get called when all the references to the objects are free (VBA uses reference countinig) whereas you could create an instance of your own `TransactionScopeWrapper` class wrapper for the original `TransactionScope` but unfortunately `System.TransactionScope` is not COM visible. – Jun 20 '14 at 09:41
@mehow: TransactionScope not being COM visible could indeed be a problem, although one would need to test to be sure. After all, we are not calling any methods on it in VBA, just keeping the reference. If it is a problem, I agree that a COM visible wrapper object would probably be the easiest solution (e.g., just keeping the reference to `scope` in a private field of `myCsObject` in the example given). – Heinzi Jun 20 '14 at 09:48
This is exactly what I was thinking. @mehow do you agree that this would work (as opposed to whether it is wise to be going about things this way in the first place!) – tbone Jun 21 '14 at 16:07
fwiw.....the reason I want this ability is I will have many adhoc, one-off Excel docs with data that I want to quickly script to push into an enterprise app....kinda "dirty" but that's ok by me. – tbone Jun 21 '14 at 16:08

Initiating a TransactionScope from Excel VBA?

1 Answers1