Sql Transactions: Best Way to Implement in ASP.Net

Question

I have an application that has many different types of objects that each persist themselves back to the db. This has worked fine so far without transactions and I'm not looking to go hog wild adding them. But there is an occasional need to start a transaction before a whole collection of the objects start updating to ensure that the database is only actually updated if all objects in the collection succeed.

For example, say I have a collection of apples. The command is issued to the collection to update all the apples. [transaction starting should be here] The each apples executes the code to update itself. [transaction commit/rollback should happen here].

The hitch I have is that each update is atomic right now (not explicitly wrapped in a transaction). I could pass an id to each "apple" to identify a transaction that has been stashed in some kind of cache, but then there's the risk that the cache would be invalidated mid-update and cause an unnecessary problem.

So what's the best approach to this?

Answer 1

When you say "The hitch I have is that each update is atomic right now" do you mean that each update is wrapped in a transaction? The rest of your question implies not.

Single SQL statements are wrapped in an implicit transaction.

If you want to group multiple SQL statements into a single transaction, then simply start a transaction before emitting the first statement, emit the other updates and then check the outcome. If all good commit, else rollback.

You can start a transaction from your ASP.NET page:

<%@ Page Transaction="RequiresNew" %>

and within your TSQL check for a running transaction:

IF @@TRANCOUNT > 0
    -- check for errors ...

EDIT: I think @badbadboy is correct about requiring DTC support for declarative page transactions.

This MSDN article may be of interest.

Answer 2

1. I would prefer declarative transactions over trying to do manual DB transactions.
2. As Mitch Wheat already mentioned, a transaction has context and is connection based, so it will include your changes happening in the page/method, and commit if all ok, or rollback if there was an exception.
3. I might be wrong here, but I have a feeling that "Page Transaction" is using Enterprise Services model from .NET 1.1 and works through MSDTC (Distributed transaction coordinator, registered COM+ service)...
4. I would prefer System.Transactions and LTM (light-weight transaction manager) for declarative transactions in .NET 2.0
5. I would prefer Spring.NET framework transactions (available for both ADO.NET and NHibernate versions), because I believe they support better AOP-based (aspect oriented programming) separation of concerns, you will not have to couple your code with transaction code, and mostly because these guys have been doing transactions in Java for many years and I just trust them that they know what to do.

Answer 3

First, I wouldn't be handling the transactional logic in the page. Write a business class of some sort to do this - a service, a data utility class, something you can abstract away from ASP.Net.

Next, you might look at using the TransactionScope class (in System.Transactions namespace, reference System.Transactions.dll) if you are using a database that can subscribe to distributed transaction like SQL Server.

using(TransactionScope scope = new TransactionScope())
{
  SaveObjectOne(); //these are just psuedo-code statements
  SaveObjectTwo(); //replace these with your code that saves various objs
  SaveObjectThree();
  scope.Complete(); //this commits the transaction, unless exception throws
}

TransactionScope implements IDisposable, so when using calls Dispose() the transaction will roll back if Complete() was never called. You do need to enable the Distributed Transaction Coordinator to use TransactionScope.

Answer 4

The OP says that the collection manages the save of all of the other objects in the transaction so it would seem obvious to put the transaction code here. If you are using ADO.Net, it would seem the easiest option to open the connection and begin the transaction in the collection and then just pass this to each of the other objects. I am assuming that each of the objects here inherits from a Layer Supertype Class and you are using SQL Server.

public void Save()
{
    using (SqlConnection connection = new SqlConnection("Connection String"))
    {
        using (SqlTransaction trans = connection.BeginTransaction())
        {
            foreach (BusinessObject obj in this) 
            { 
                obj.Save(connection); 
            } 
            trans.Commit();
        }            
    }
}

Answer 5

For all and more see this tutorial from Mike Taulty Link To Video Tutorial

Answer 6

Transactions are really simple in ado.net 2.0 I'd suggest using the transactionscope and let the framework mange the transaction for you:

read all about it on MSDN: http://msdn.microsoft.com/en-us/library/ms973865.aspx

Transaction Flow Management Transaction scopes can nest both directly and indirectly. A direct scope nesting is simply one scope nested inside another, as shown in Example 5.

Example 5. Direct scope nesting

Copy Code using(TransactionScope scope1 = new TransactionScope()) { using(TransactionScope scope2 = new TransactionScope()) { scope2.Complete(); } scope1.Complete(); }

An indirect scope nesting occurs when calling a method that uses a TransactionScope from within a method that uses its own scope, as is the case with the RootMethod() in Example 6.

Example 6. Indirect scope nesting

Copy Code void RootMethod() { using(TransactionScope scope = new TransactionScope()) { /* Perform transactional work here */ SomeMethod(); scope.Complete(); } }

void SomeMethod() { using(TransactionScope scope = new TransactionScope()) { /* Perform transactional work here */ scope.Complete(); }