Nested Transactions in .NET

Question

How can I perform the equivalent of this? My understanding is that this is impossible with TransactionScopes but I'd like to accomplish the equivalent in some other way:

Business Logic class:

public bool Bar()
{
  try
  {
    using (var tsWork = new TransactionScope())
    {
      ComplicatedDataImportCode(somedata);
      FlagRecordInDatabaseAsImported(); // this is the same record that's modified in the catch
      tsWork.Complete();
      return true;
    }
    catch (DuplicateDataException err)
    {
      // if we got here, the above transaction should have rolled back,
      // so take that same record in the database and update it to "Duplicate".
      FlagSameRecordInDatabaseAsDuplicate(err.Message);
    }

    return false;
}

Now this works fine, until I encapsulate all of this inside of a transaction (perhaps an integration test that I want to rollback after performing asserts).

Simple test to prove my point:

public void CanTest()
{
  // Arrange
  var foo = new Foo();

  using (var ts = new TransactionScope())
  {
    // Act
    var success = foo.Bar();

    // Assert
    if (success)
    {
      Assert.That(SomethingThatTestsThatTheDataWasImported);
    }
    else
    {
      Assert.That(SomethingThatTestsThatTheRecordWasMarkedAsDuplicate);
    }

    // Now that we have been able to perform our Asserts, rollback.

  }
}

Ultimately, the code in Foo.Bar() can be modified to accommodate a solution however, the code in ComplicatedDataImportCode() cannot be modified for this solution, and is consequently what I really need to make sure I properly rollback in the failure scenario.

Again, I understand that TransactionScopes cannot be used to do this, according to the post that I referenced at the beginning of this question. I used TransactionScopes here to indicate what I wanted to do, and am looking for the best alternative way to implement this functionality.

Answer 1

Isn't it so that this must be supported by the DBMS you're using ?

SQL Server doesn't really support nested transactions for instance, however, with SQL Server you can use savepoints.

An article I've written some years ago on my blog.

Answer 2

If you can get a hold of the active DB connection that ComplicatedDataImportCode is using, you should just have to run a BEGIN TRAN and ROLLBACK TRAN on that connection.