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 ``, 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 use…

Accepted Answer

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?

Solution

Problem

Related problems