Using transactions with subsonic

Question

In my web application I've to keep audit of the user actions. So whenever user takes an action I update the object on which action is taken and keep audit trail of that action.

Now If I first modify the object and then update audit trail but the audit trail fails then what?

Obviously I need to roll-back changes to modified object. I can use Sql-Transactions in simple application, but I'm using Subsonic to talk to db. How I can handle the situation?

Bistort

Answer 1

Something like:

Using ts As New System.Transactions.TransactionScope()
  Using sharedConnectionScope As New SubSonic.SharedDbConnectionScope()

' Do your individual saves here

' If all OK
      ts.Complete()

   End Using
End Using

Fieldsman

I can confirm TransactionScope works correctly with SubSonic, and does rollback transactions correctly. – Rhines

Thanks @Fieldsman and @bnkdev. I'm using C# so I'll post the code in C# also so it can be readily used by others. Also wouldn't you put the individual saves or actions inside try/catch so it is easier to know if all Ok or not? – Bistort

Answer 2

The answer given by @Kevinw is perfectly okay. I'm posting this just as translation of his answer to C# code. I'm not using comments as it will not format code :) Also I'm using try/catch to know if transaction should complete or be rolled back.

using (System.Transactions.TransactionScope ts = new TransactionScope())
{
    using (SharedDbConnectionScope scs = new SharedDbConnectionScope())
    {
        try
        {
            //do your stuff like saving multiple objects etc. here 

            //everything should be completed nicely before you reach this
            //line if not throw exception and don't reach to line below
            ts.Complete();
        }
        catch (Exception ex)
        {
            //ts.Dispose(); //Don't need this as using will take care of it.
            //Do stuff with exception or throw it to caller
        }
    }
}

Bistort

Calling ts.Dispose() inside the catch-clause is not required as the using statement will take care of it anyway. In the general case, it could even cause problems as SharedDbConnectionScope or other transaction aware code launched inside the transaction scope might depend on being disposed before the enclosing transaction scope is disposed. – Favour

@OskarBerggren commented it. – Bistort

Does this also handle operations inside a stored procedure? Say I am calling multiple sps which do CRUD operations, will this revert everything? – Helfand

@GeomanYabes yes transactions get passed on to the MS SQL server ( if you are using that). Not sure about other database servers. – Bistort

Answer 3

Nope. If I put the SharedDbConnectionScope outside the changes are visibles in the database before ts.Complete(). Putting it inside blocks the server until the operation is done.

Lapful

Can anyone enlighten me with this? So no other transactions will be catered unless the current transaction completes? i.e. You have a long-running transaction, other users can't interact with the database until the transaction finishes? – Helfand