How to perform multiple operations in under single

Question

I have a scenario where it requires to add a record in to table, then - creating a resource on the cloud if record is added, then update the record in table with the resource identifier if resource is created on cloud. So, they are 3 operations and I want to revert all of it when any of them doesn't succeed.

We have TransactionScope for Multiple Db Operations in one go but I'm wondering how to achieve this? Appreciate your help!

Edit

PS: There could be any number of operations like that - say 10 or more in a sequence, and they may not even related to DB operations. They could just be creating 10 files in a sequence - so when any of the file creation fails - all the previous files should be deleted/undone.

Answer 1

How about going a command pattern way? It's may not be perfect command pattern implementation but something very close. See below:

public interface ICommand {
    ICommandResult Execute();
    ICommandResult Rollback();
}

public interface ICommandResult {
    bool Success { get; set; }
    object Data { get; set; }
    Exception Error { get; set; }
}

public class CommandResult : ICommandResult { 
    public bool Success { get; set; }
    public object Data { get; set; }
    public Exception Error { get; set; }
}

public class AddToDBCommand : ICommand {
    private ICommandResult result;
    private int newRecordId;

    public AddToDBCommand(<params_if_any>) {
        result = new CommandResult();
    }

    public ICommandResult Execute() {
        try {
            // insert record into db
            result.Success = true;
            result.Data = 10; // new record id
        }
        catch (Exception ex) {
            result.Success = false;
            result.Error = ex;
        }
        return result;
    }

    public ICommandResult Rollback() {
        try {
            // delete record inserted by this command instance
            // use ICommandResult.Data to get the 'record id' for deletion
            Console.WriteLine("Rolling back insertion of record id: " + result.Data);
            // set Success
        }
        catch(Exception ex) {
            // set Success and Error
            // I'm not sure what you want to do in such case
        }
        return result;
    }
}

Similarly you would create commands for creating cloud resource and updating record in db. In main code you can hold collection of ICommand objects and execute each one.

var commands = new List<ICommand>
{
    new AddToDBCommand(<params_if_any>),
    new AddToCloudCommand(<params_if_any>),
    new UpdateInDBCommand(<param_if_any>)
};

Then in the loop you can call Execute, if it returns Success = false then record the current command index in collection and loop backward whilst calling Rollback on each command.

Answer 2

I assume you are using Azure as cloud. So to support transactions you need to have -


1. Elastic database on Azure which supports transactions.
2. You need to have .NET framework 4.6.1 or higher to utilize distributed transaction.

I encourage you to go through https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-transactions-overview

Now in your case lets break 3 steps considering transaction scope is applied.

1. Add record to table -
   If this fails then no worries I guess.
2. Create resource in cloud-
   If this fails then Added record will be rolled back.
3. Update record in table with resource id created.
   If this fails then 1 step will be rolled back.

After transaction scope is finished you need to check that record added in 3rd step exists. If it does not then you need to manually rollback resource creation by deleting it.