How to write a file to disk and insert a database record in a single transaction?

Question

I am trying to write a file to disk as well as insert data into a database via a stored procedure all within an atomic transaction. i.e. If any one of these 2 operations fails (either the file cannot be written to disk or the stored procedure fails) I'd like to do nothing and simply throw an exception back to the caller.

Any suggestions on how to best to tackle this atomic transaction for a file write and a database insert?

Additional Info: I am using C# .NET with a stored procedure into MS SQL Server but general solutions not necessarily tailored to these technologies are fine too.

UPDATE: After reviewing all the answers below and researching others, I wrote this post about how to solve this problem using 3 different approaches.

Answer 1

You need to use the new TxF, the Transacted NTFS introduced in Vista, Windows 7 and Windows Server 2008. This is a good introductory article: Enhance Your Apps With File System Transactions. It contains a small managed sample of enrolling a file operation into a system transaction:

// IKernelTransaction COM Interface
[Guid("79427A2B-F895-40e0-BE79-B57DC82ED231")]
[InterfaceType(ComInterfaceType.InterfaceIsIUnknown)]
public interface IKernelTransaction
{
    int GetHandle(out IntPtr pHandle);
}

[DllImport(KERNEL32, 
   EntryPoint = "CreateFileTransacted",
   CharSet = CharSet.Unicode, SetLastError = true)]
internal static extern SafeFileHandle CreateFileTransacted(
   [In] string lpFileName,
   [In] NativeMethods.FileAccess dwDesiredAccess,
   [In] NativeMethods.FileShare dwShareMode,
   [In] IntPtr lpSecurityAttributes,
   [In] NativeMethods.FileMode dwCreationDisposition,
   [In] int dwFlagsAndAttributes,
   [In] IntPtr hTemplateFile,
   [In] KtmTransactionHandle hTransaction,
   [In] IntPtr pusMiniVersion,
   [In] IntPtr pExtendedParameter);

....

using (TransactionScope scope = new TransactionScope())
{
   // Grab Kernel level transaction handle
   IDtcTransaction dtcTransaction = 
      TransactionInterop.GetDtcTransaction(managedTransaction);
   IKernelTransaction ktmInterface = (IKernelTransaction)dtcTransaction;

   IntPtr ktmTxHandle;
   ktmInterface.GetHandle(out ktmTxHandle);

   // Grab transacted file handle
   SafeFileHandle hFile = NativeMethods.CreateFileTransacted(
      path, internalAccess, internalShare, IntPtr.Zero,
      internalMode, 0, IntPtr.Zero, ktmTxHandle,
      IntPtr.Zero, IntPtr.Zero);

   ... // Work with file (e.g. passing hFile to StreamWriter constructor)

   // Close handles
}

You'll need to enroll your SQL operation in the same transaction, which will occur automatically under a TransactionScope. But I highly recommend you override the default TransactionScope options to use ReadCommitted isolation level:

using (TransactionScope scope = new TransactionScope(
     TransactionScope.Required, 
     new TransactionOptions 
         { IsolationLevel = IsolationLEvel.ReadCommitted}))
{
...
}

W/o this you'll get the default Serializable isolation level which is way way overkill for most cases.

Answer 2

This question and answer seems to be part of the answer. It involves Transactional NTFS. SLaks links to a .NET managed wrapper for Transactional NTFS hosted on MSDN.

You could try using a TransactionScope.