How to achieve transaction-like behavior for non-database operations?

Question

Suppose I have a database table with a list of file paths/URLs of the form <fileID><location>, e.g.:

fileID  location
----------------------------------
0       C:\Folder\Sub\file1.txt
1       \\path\Folder\file2.txt
2       ftp://someftp/file3.txt
...

This table represents a list of files that should be deleted. What I want to do is:

delete a specific file located at "location";
DELETE the file record from the above table.

Like this:

MyClass.DeleteFile(fileId);             // statement 1
DELETE FROM table WHERE fileId=@fileId  // statement 2

Problem: If DeleteFile() succeeds but DELETE query fails, I want to un-delete the just-deleted file. What is the best way to ensure that either both statements succeed, or none of them? I.e. I want to have them in a transaction, but obviously this is not a database transaction.

Note that DeleteFile() is backed up by different implementations depending on file location (e.g. filesystem vs ftp), so it's not simply e.g. a File.Delete() that is used for deleting.
Is this something that must be solved ad hoc (I'd rather not implement my own full blown transactionality) or is there something that is as simple to use as TransactionScope is for database queries?

Command Pattern with Undo functionality? (see http://stackoverflow.com/questions/49755/design-pattern-for-undo-engine) — Dresel, Dec 05 '13 at 14:09
Does this help: http://stackoverflow.com/questions/7939339/how-to-write-a-transaction-to-cover-moving-a-file-and-inserting-record-in-databa — Davin Tryon, Dec 05 '13 at 14:10

score 4 · Accepted Answer · answered Dec 05 '13 at 14:13

Try this (switch the order of the delete from DB and file deletion so that the file is deleted only after a successful DB transaction):

using (IDbTransaction tran = conn.BeginTransaction()) {
    try {

        ...Delete File From DB Code...  // statement 2
        MyClass.DeleteFile(fileId);             // statement 1

        tran.Commit();
    }  catch {
        tran.Rollback();
        throw;
    }
}

How to achieve transaction-like behavior for non-database operations?

1 Answers1