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?

score 15 · Answer 1 · edited May 23 '17 at 12:34

15

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
        }
    }
}

edited May 23 '17 at 12:34

Community

1
1

answered May 27 '09 at 02:26

TheVillageIdiot

40,053
20
133
188

1

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. – Oskar Berggren Nov 17 '13 at 02:27
@OskarBerggren commented it. – TheVillageIdiot Nov 17 '13 at 09:14
Does this also handle operations inside a stored procedure? Say I am calling multiple sps which do CRUD operations, will this revert everything? – xGeo Mar 27 '19 at 05:31
@GeomanYabes yes transactions get passed on to the MS SQL server ( if you are using that). Not sure about other database servers. – TheVillageIdiot Mar 27 '19 at 11:32

score 10 · Accepted Answer · answered May 26 '09 at 14:42

10

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

answered May 26 '09 at 14:42

1

I can confirm TransactionScope works correctly with SubSonic, and does rollback transactions correctly. – Ta01 May 26 '09 at 14:46
Thanks @kevinw 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? – TheVillageIdiot May 27 '09 at 02:20

score 1 · Answer 3 · edited Jan 13 '14 at 10:27

1

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.

edited Jan 13 '14 at 10:27

Liam

27,717
28
128
190

answered Nov 12 '09 at 10:17

Apocatastasis

500
1
9
21

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? – xGeo Mar 27 '19 at 13:47

Using transactions with subsonic

3 Answers3

Linked