How to write a transaction to cover Moving a file and Inserting record in database?

Solution 1:

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();
} 

Solution 2:

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.