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

I want to have a transaction for copying a file and then inserting a record in database.
something like below statement, but transaction doesn’t cover copying file.
What’s the solution?

using (TransactionScope scope1 = new TransactionScope())
{
    // Copy a file
    fileMgr.Move(srcFileName, destFileName);

    // Insert a database record
    dbMgr.ExecuteNonQuery(insertSql);

    scope1.Complete();
}

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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

Method 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.

Method 3

Please read up on this article before using transactional NTFS or any other suggestions: https://docs.microsoft.com/nl-nl/windows/desktop/FileIO/deprecation-of-txf

TxF is a complex and nuanced set of APIs which are not commonly used by 3rd party applications. With the possibility that these APIs may not be available in future versions of Windows, and the fact that there are simpler alternative means to achieve many of the scenarios which TxF was developed for, Microsoft strongly recommends developers to investigate those alternative means instead of creating a dependency on TxF in their applications.

A better way to achieve ACID, is to use Filestream storage

Method 4

you could manually roll your own file transaction, if the commit fails, move the file back to it’s original location

using (TransactionScope scope1 = new TransactionScope())
{
    // Copy a file
    fileMgr.Move(srcFileName, destFileName);

    try
    {
        // Insert a database record
        dbMgr.ExecuteNonQuery(insertSql);

        scope1.Complete();
    } catch (Exception) {
        fileMgr.Move(destFileName, srcFileName);
    }
}

i’m not 100% on the syntax for determining if an error occurred committing the transaction, but i think this illustrates the concept


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x