How to write a transaction to cover Moving a file and Inserting record in database?
Try to use .NET Transactional File Manager
This library allows you to wrap file system operations in transactions like this:
// Wrap a file copy and a database insert in the same transaction
TxFileManager fileMgr = new TxFileManager();
using (TransactionScope scope1 = new TransactionScope())
{
// Copy a file
fileMgr.Copy(srcFileName, destFileName);
// Insert a database record
dbMgr.ExecuteNonQuery(insertSql);
scope1.Complete();
}
Newer versions of Windows have something called TxF (Transactional NTFS) you can use. Here there is an example of code: WINDOWS VISTA - INTRODUCING TXF IN C# (PART 2) - USING SYSTEM.TRANSACTIONS AND THE DTC (I'm sorry for the caps lock on, but the title of the page is that :-) )
You'll have to use MoveFileTransacted instead of DeleteFileTransactioned
. Once you are in a DTC, your SQL connection should be enrolled into it and so everything should be a single big transaction.