Transactions

Adapter supports ADO.NET transactions and System.Transactions (distributed) transactions. This section explains how ADO.NET transactions and distributed transactions are used in Adapter. Adapter automatically uses ADO.NET transactions for recursive saves and save/delete actions for collections of entities so you don't have to start/commit transactions yourself if you persist entity changes.

Normal, native database transactions

Native database transactions are provided by ADO.NET; a transaction started using ADO.NET starts a native database transaction. LLBLGen Pro's native database transactions are implemented in the DataAccessAdapter object.

As the transactional code is inside the DataAccessAdapter class, every method of the DataAccessAdapter object you call after a transaction is started on the DataAccessAdapter class (implicitly or explicitly), is ran inside that transaction, including stored procedure calls, entity fetches, entity collection saves etc.

You don't have to add entity objects or entity collection objects to the DataAccessAdapter object to make them participate in the transaction, just call a DataAccessAdapter method and it's inside the transaction of that particular DataAccessAdapter object.

If you wish to run a particular action outside of a transaction, create a new DataAccessAdapter object for that particular action. You can start/commit multiple transactions after each other on the same DataAccessAdapter instance.

Info

If you start a new transaction by calling DataAccessAdapter.StartTransaction() the connection is kept open until Rollback() or Commit() is called.

Persisting entities through a DataAccessAdapter class instance by recursively saving an entity graph or by saving an entity collection will automatically start a transaction as multiple entities are involved. This will always be a transaction with isolation level ReadCommitted. This frees you in general from transaction management.

You can also start a transaction explicitly. The example below will help illustrate starting a transaction explicitly. The example below will update two different entities in one transaction. For this example, it has to be done in one atomic action, and therefore requires a transaction.

C#VB.NET

// create adapter for fetching and the transaction. 
using(var adapter = new DataAccessAdapter())
{
    // start the transaction explicitly.
    adapter.StartTransaction(IsolationLevel.ReadCommitted, "TwoUpates");
    try
    {
        // fetch the two entities
        var customer = new CustomerEntity("CHOPS");
        OrderEntity order = new OrderEntity(10254);
        adapter.FetchEntity(customer);
        adapter.FetchEntity(order);
        
        // alter the entities
        customer.Fax = "12345678";
        order.Freight = 12;
        
        // save the two entities again
        adapter.SaveEntity(customer);
        adapter.SaveEntity(order);
        
        // done
        adapter.Commit();
    }
    catch
    {
        // abort, roll back the transaction
        adapter.Rollback();
        // bubble up exception
        throw;
    }
}

' create adapter for fetching and the transaction. 
Using adapter As New DataAccessAdapter()
    ' start the transaction.
    adapter.StartTransaction(IsolationLevel.ReadCommitted, "TwoUpates")

    Try
        ' fetch the two entities
        Dim customer As New CustomerEntity("CHOPS")
        Dim order As New OrderEntity(10254)
        adapter.FetchEntity(customer)
        adapter.FetchEntity(order)
        
        ' alter the entities
        customer.Fax = "12345678"
        order.Freight = 12
        
        ' save the two entities again.
        adapter.SaveEntity(customer)
        adapter.SaveEntity(order)
        
        ' done
        adapter.Commit()
    Catch
        ' abort, roll back the transaction
        adapter.Rollback()
        ' bubble up exception
        Throw
    End Try
End Using

First a DataAccessAdapter object is created and a transaction is explicitly started. As soon as you start the transaction, a database connection is open and usable. It's best practice to embed the usage of a transaction in a try/catch/finally statement as it is done in the example above, or a using block. This ensures that if something fails during the usage of the transaction, everything is rolled back or, at the end, everything is committed correctly. A DataAccessAdapter instance will rollback an open transaction when it's disposed.

Transaction save-points

Most databases support transaction save-points. Transaction save-points make it possible to do fine grained transaction control on a semi-nested level. This can be helpful as ADO.NET doesn't support nested transactions. Save-points let you define a point in a transaction to which you can roll back, without rolling back the complete transaction. This can be handy if you for example have saved some entities in a transaction which were saved OK, and another one fails, however the failure of that save shouldn't terminate the whole transaction, just roll back the transaction to a given point in the transaction.

The following example illustrates the save-point functionality. It first saves a new Address entity and after that it saves the transaction. It then saves a new Customer entity but takes into account that this can fail. If it does, it should roll back to the save-point set before the second save, so it avoids rolling back the complete transaction.

C#VB.NET

using(var adapter = new DataAccessAdapter())
{
    try
    {
        adapter.StartTransaction(IsolationLevel.ReadCommitted, "SavepointRollback");

        // first save a new address
        var newAddress = new AddressEntity();
        // ... fill the address entity with values

        // save it.
        adapter.SaveEntity(newAddress, true);

        // create a transaction save point
        adapter.SaveTransaction("SavepointAddress");

        // save a new customer
        var newCustomer = new CustomerEntity();
        // ... fill the customer entity with values
        newCustomer.VisitingAddress = newAddress;
        newCustomer.BillingAddress = newAddress;
        
        try
        {
            adapter.SaveEntity(newCustomer, true);
        }
        catch(Exception ex)
        {
            // something was wrong. 
            // ... handle ex here.

            // roll back to savepoint.
            adapter.Rollback("SavepointAddress");
        }
        
        // commit the transaction. If the customer save failed, 
        // only address is saved, otherwise both.
        adapter.Commit();
    }
    catch
    {
        // fatal error, roll back everything
        adapter.Rollback();
        throw;
    }
}

Using adapter As new DataAccessAdapter()
    Try
        adapter.StartTransaction(IsolationLevel.ReadCommitted, "SavepointRollback")

        ' first save a new address
        Dim newAddress As New AddressEntity()
        ' ... fill the address entity with values

        ' save it.
        adapter.SaveEntity(newAddress, True)

        ' save the transaction
        adapter.SaveTransaction("SavepointAddress")

        ' save a new customer
        Dim newCustomer As New CustomerEntity()
        ' ... fill the customer entity with values
        newCustomer.VisitingAddress = newAddress
        newCustomer.BillingAddress = newAddress
            
        Try
            adapter.SaveEntity(newCustomer, True)
        Catch(Exception ex)
            ' something was wrong. 
            ' ... handle ex here.

            ' roll back to savepoint.
            adapter.Rollback("SavepointAddress")
        End Try
        
        ' commit the transaction. If the customer save failed, 
        ' only address is saved, otherwise both.
        adapter.Commit()
    Catch
        // fatal error, roll back everything
        adapter.Rollback()
        Throw
    End Try
End Using

Info

Microsoft Access and Microsoft's Oracle ADO.NET provider don't support savepoints in transactions, so this feature is not supported when you use LLBLGen Pro with MS Access or when you use the MS Oracle provider with Oracle. In the case of Oracle, use ODP.NET instead, which does support save points.

System.Transactions support

.NET contains the System.Transactions namespace, which is a namespace with the TransactionScope class, which eases the creation of distributed transactions, by specifying a given scope. All transactions, e.g. ADO.NET transactions, are automatically elevated to distributed transactions, if required by the TransactionScope they're declared in.

This requires support by the used database system as the database system has to be able to promote a non-distributed transaction to a distributed transaction.

The developer can define such a TransactionScope using the normal .NET constructs, like

using(TransactionScope scope = new TransactionScope())
{
    // your code here.
}

A DataAccessAdapter object is able to determine if it's participating inside an ambient transaction of System.Transactions. If so, it enlists itself with a Resource Manager tied to the System.Transactions transaction. As soon as a Transaction or DataAccessAdapter is enlisted through a Resource Manager, the Commit() and Rollback() methods are setting the ResourceManager's commit/abort signal which is requested by the System.Transactions' Transaction manager.

If multiple transactions are executed on a DataAccessAdapter and one rolled back, the resource manager will report an abort. Once one rollback is requested, the transaction will always report a rollback to the MSDTC.

Going out of scope

When the System.Transactions transaction is committed or rolled back, the Resource manager is notified and will then notify the DataAccessAdapter that it can commit/rollback the transaction. That call will then notify the enlisted entities of the outcome of the transaction.

Multiple transactions executed using a single DataAccessAdapter object

For the DataAccessAdapter it will look like its still inside the same transaction, so no new transaction is started. This will make sure that an entity which is already participating in the transaction isn't enlisted again and the field values aren't saved again etc.

Example

Below is an example which shows the usage of a TransactionScope in combination of a DataAccessAdapter object. The code contains Assert statements to illustrate the state / outcome of the various statements.

C#VB.NET

var newCustomer = new CustomerEntity();
// fill newCustomer's fields.
// ..
var newAddress = new AddressEntity();
// fill newAddress' fields.
// ..

// start the scope.
using(var ts = new TransactionScope() )
{
    // as we're inside the transaction scope, we can now create a DataAccessAdapter object and
    // start a connection + transaction. The connection + transaction will be enlisted through a 
    // resource manager in the TransactionScope ts and will be controlled by that TransactionScope.
    using(var adapter = new DataAccessAdapter())
    {
        // save 2 entities, recursively. This should be done in one 
        // transaction, namely the transaction scope we've started. 
        newCustomer.VisitingAddress = newAddress;
        newCustomer.BillingAddress = newAddress;
        Assert.IsTrue( adapter.SaveEntity( newCustomer, true) );
        
        // save went well, alter the entities, which are fetched back, and 
        // save again.
        newCustomer.CompanyEmailAddress += " ";
        newAddress.StreetName += " ";
        Assert.IsTrue( adapter.SaveEntity( newCustomer, true ) );
    }
    // do not call Complete, as we want to rollback the transaction and see if the rollback indeed succeeds.
    // as the TransactionScope goes out of scope, the on-going transaction is rolled back.
}
// at this point the transaction of the previous using block is rolled back.
// let the DTC and the system.transactions threads deal with the objects. 
// this sleep is only needed because we're going to access the data directly after the rollback. In normal code, 
// this sleep isn't necessary.
Thread.Sleep( 1000 );

// test if the data is still there. Shouldn't be as the transaction has been rolled back. 
using( DataAccessAdapter adapter = new DataAccessAdapter() )
{
    CustomerEntity fetchedCustomer = new CustomerEntity( newCustomer.CustomerId );
    Assert.IsFalse( adapter.FetchEntity( fetchedCustomer ) );
    AddressEntity fetchedAddress = new AddressEntity( newAddress.AddressId );
    Assert.IsFalse( adapter.FetchEntity( fetchedAddress ) );
    Assert.AreEqual( 0, newAddress.AddressId );
}

Dim NewCustomer As New CustomerEntity()
' fill NewCustomer's fields.
' ..
Dim NewAddress As New AddressEntity()
' fill NewAddress' fields.
' ..

' start the scope.
Using  ts As New TransactionScope() 
    ' as we're inside the transaction scope, we can now create a DataAccessAdapter object and
    ' start a connection + transaction. The connection + transaction will be enlisted through a 
    ' resource manager in the TransactionScope ts and will be controlled by that TransactionScope.
    Using adapter As New DataAccessAdapter()
        ' save 2 entities, recursively. This should be done in one 
        ' transaction, namely the transaction scope we've started. 
        NewCustomer.VisitingAddress = NewAddress
        NewCustomer.BillingAddress = NewAddress
        Assert.IsTrue( adapter.SaveEntity( NewCustomer, True) )
        
        ' save went well, alter the entities, which are fetched back, and 
        ' save again.
        NewCustomer.CompanyEmailAddress = NewCustomer.CompanyEmailAddress & " "
        NewAddress.StreetName = NewAddress.StreetName & " "
        Assert.IsTrue( adapter.SaveEntity( NewCustomer, True ) )
    End Using
    ' do not call Complete, as we want to rollback the transaction and see if the rollback indeed succeeds.
    ' as the TransactionScope goes out of scope, the on-going transaction is rolled back.
End Using
' at this point the transaction of the previous using block is rolled back.
' let the DTC and the system.transactions threads deal with the objects. 
' this sleep is only needed because we're going to access the data directly after the rollback. In normal code, 
' this sleep isn't necessary.
Thread.Sleep( 1000 )

' test if the data is still there. Shouldn't be as the transaction has been rolled back. 
Using adapter As New DataAccessAdapter()
    Dim fetchedCustomer As New CustomerEntity( NewCustomer.CustomerId )
    Assert.IsFalse( adapter.FetchEntity( fetchedCustomer ) )
    Dim fetchedAddress = New AddressEntity( NewAddress.AddressId )
    Assert.IsFalse( adapter.FetchEntity( fetchedAddress ) )
    Assert.AreEqual( 0, NewAddress.AddressId )
End Using