Transactions

[venerable bede]

I have been using transactions for a while in .net but have hit a snag.

How can I implement a transaction when 1 of my processes is not database driven ?

I basically want to insert a record (fine here) and delete a file from the documents folder.

How do I wrap these up in a transaction ?

[jmcilhinney]

You can wrap the database operation in two nested transactions. You commit the first then, if that succeeds, you perform the IO operation. If that succeeds you then commit the second transaction. This is not 100% safe as there is a miniscule possibility that the second commit might fail, but the only reason I can see for that would be if the connection to the database is lost for some reason.

[jmcilhinney]

Here's example code without the error handling.

Dim outerTransaction As OleDbTransaction = connection.BeginTransaction()
        Dim innerTransaction As OleDbTransaction = outerTransaction.Begin()
 
        command.Transaction = innerTransaction
 
        command.ExecuteNonQuery()
        innerTransaction.Commit()
 
        'Perform other operation here.
 
        outerTransaction.Commit()

[venerable bede]

I'm not sure how an OLEDbTransaction fits inb to the process of deleting a file.

It's not a database operation.

[szlamany]

It's not really possible to have a SQL DB transaction rely on a file operation outside the database. As JM said, the TRANSACTION would rollback automatically if the DB connection got lost during the "file delete" operation.

If this is MS SQL Server, you could write an EXTENDED STORED PROCEDURE that does the file delete - I've never done anything like this, but it should be possible.

Have you considered adding a DB flag field and changing that DB flag value to indicate that the "delete" transaction was initiated, then delete the file, then set the DB flag to indicate that the delete was successful?

[jmcilhinney]

I'm not sure how an OLEDbTransaction fits inb to the process of deleting a file.

It's not a database operation.

The OleDbTransaction can't control anything outside of database operations. That's the whole point of using two nested transactions. You commit the inner transaction first. If that succeeds then you know that all your database operations will succeed, but the data is not fully committed until you commit the outer transaction. That means that you can go ahead and perform whatever other operation you want to perform, in your case deleting the file. If that fails then you roll back the outer transaction and no data will be committed to the database. If it succeeds you commit the outer transaction and your database operations become final.