CODEWITHSUNDEEP
asp.netdatabasetransactions
Jared
Jared

Reputation: 7243

Sql Transactions: Best Way to Implement in ASP.Net

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?

Upvotes: 4

Views: 3784

Answers (4)

MarkDav.is
MarkDav.is

Reputation: 640

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:

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

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

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

Upvotes: 1

Martin Brown
Martin Brown

Reputation: 25330

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

Upvotes: 1

Jason Jackson
Jason Jackson

Reputation: 17260

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.

Upvotes: 4

Paul Kapustin
Paul Kapustin

Reputation: 3293

  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.

Upvotes: 0

Related Questions