Transactions

SelfServicing supports ADO.NET transactions and System.Transactions (distributed) transactions. SelfServicing 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; it's a part of an ADO.NET connection object and that transaction object can be used to execute all database statements in that transaction if that connection is used.

LLBLGen Pro's native database transactions work the same for you: you create an instance of the Transaction class and add the objects that should participate (use) that transaction to that transaction object. As of that moment the actions you perform on those objects are executed in the transaction of that transaction object.

An example will help illustrate the usage of the Transaction class. Let's add a new order with an order row to the persistent storage for the customer "CHOPS". Because we'll add two entities, we will use a transaction to make sure that when the second save fails, the first is rolled back.

Info

The example below is just to show you how to use the Transaction object as it doesn't use the recursive save functionality build into the code. As recursive saves already use an ADO.NET transaction internally, the example would have been much smaller without the external transaction.

To illustrate this, the same example is also listed using recursive saves. It also doesn't use the FK-PK synchronization functionality which synchronizes Foreign key fields with their Primary key field's value, after a save, for example when newOrderRow is added to newOrder.OrderDetails, which will sync the new PK value of newOrder.OrderID with newOrderRow.OrderID once newOrder has been saved. See the recursive save example below for an example of that.

First, let's see the code: (it assumes the entity data for "CHOPS" is already loaded in the object customer. The data is rather bogus, it's for illustration purposes only).

C#VB.NET

// Create the transaction object, pass the isolation level and give it a name
using(var transactionManager = new Transaction(IsolationLevel.ReadCommitted, "Test"))
{
    // create a new order and then 2 new order rows. 
    try
    {
        // create new order entity. Use data from the object 'customer'
        var newOrder = new OrderEntity();
        
        // set the customer reference, which will sync FK-PK values.
        // i.e.: newOrder.CustomerID = customer.CustomerID
        newOrder.Customer = customer;
        
        newOrder.EmployeeID = 1;
        newOrder.Freight = 10;
        newOrder.OrderDate = DateTime.Now.AddDays(-3.0);
        newOrder.RequiredDate = DateTime.Now.AddDays(3.0);
        newOrder.ShipAddress = customer.Address;
        newOrder.ShipCity = customer.City;
        newOrder.ShipCountry = customer.Country;
        newOrder.ShipName = "The Bounty";
        newOrder.ShippedDate = DateTime.Now;
        newOrder.ShipRegion = customer.Region;
        newOrder.ShipVia = 1;
        newOrder.ShipPostalCode = customer.PostalCode;

        // add this new order to the transaction so actions will run inside the transaction
        transactionManager.Add(newOrder);

        // save the new order. When this fails, will throw exception which will terminate transaction.
        newOrder.Save();

        // Create new order row.
        var newOrderRow = new OrderDetailsEntity();
        newOrderRow.OrderID = newOrder.OrderID; // will refetch order from persistent storage.
        newOrderRow.Discount = 0;
        newOrderRow.ProductID = 10;
        newOrderRow.Quantity = 200;
        newOrderRow.UnitPrice = 31;

        // add this new orderrow to the transaction
        transactionManager.Add(newOrderRow);

        // save the new orderrow. When this fails, will throw exception which will terminate transaction.
        newOrderRow.Save();

        // done, commit the transaction
        transactionManager.Commit();
    }
    catch(Exception)
    {
        // abort, roll back the transaction
        transactionManager.Rollback();
        throw;
    }
}

' Create the transaction object, pass the isolation level and give it a name
Using transactionManager As new Transaction(IsolationLevel.ReadCommitted, "Test")
    ' create a new order and then 2 new order rows. 
    Try
        ' create new order entity. Use data from the object 'customer'.
        Dim newOrder As New OrderEntity()
        
        ' set the customer reference, which will sync FK-PK values 
        ' i.e.: newOrder.CustomerID = customer.CustomerID
        newOrder.Customer = customer
        
        newOrder.EmployeeID = 1
        newOrder.Freight = 10
        newOrder.OrderDate = DateTime.Now.AddDays(-3.0)
        newOrder.RequiredDate = DateTime.Now.AddDays(3.0)
        newOrder.ShipAddress = customer.Address
        newOrder.ShipCity = customer.City
        newOrder.ShipCountry = customer.Country
        newOrder.ShipName = "The Bounty"
        newOrder.ShippedDate = DateTime.Now
        newOrder.ShipRegion = customer.Region
        newOrder.ShipVia = 1
        newOrder.ShipPostalCode = customer.PostalCode

        ' add this new order to the transaction so actions will run inside the transaction
        transactionManager.Add(newOrder)

        ' save the new order. When this fails, will throw exception which will terminate transaction.
        newOrder.Save()

        ' Create new order row.
        Dim newOrderRow As New OrderDetailsEntity()
        newOrderRow.OrderID = newOrder.OrderID  ' will refetch order from persistent storage.
        newOrderRow.Discount = 0
        newOrderRow.ProductID = 10
        newOrderRow.Quantity = 200
        newOrderRow.UnitPrice = 31

        ' add this new orderrow to the transaction
        transactionManager.Add(newOrderRow)

        ' save the new orderrow. When this fails, will throw exception which will terminate transaction.
        newOrderRow.Save()

        ' done, commit the transaction
        transactionManager.Commit()
    Catch
        ' abort, roll back the transaction
        transactionManager.Rollback()
        Throw
    End Try
End Using

First a Transaction object is created. As soon as you instantiate the object, a database connection is open and usable. This is also the reason why you have to include a finally clause and have to call Dispose() when the transaction is no longer needed. If you want an entity or entity collection object (and all its objects inside it) to participate in a transaction, add it to the particular transaction object with the Add() method. You can only add objects that can execute data modification statements like an entity object or an entity collection object.

When you add an entity or entity collection object to a Transaction object, from that moment on its logic will use the connection of the transaction object to work with the database, which automatically makes sure the database statements executed will be run inside the transaction itself.

The transaction object will take care of every other overhead like notifying objects when a transaction has been finished or aborted. If a transaction is aborted (Rollback() is called) or Committed (Commit() is called), all objects participating in the transaction are automatically removed from the transaction so you don't have to do that yourself.

It's best practice to embed the usage of the transaction in a try/catch/finally statement as it is done in the example above, so you can make sure when something fails during the usage of the transaction: everything is rolled back or at the end and everything is committed correctly.

Using recursive save functionality

The same functionality, but now implemented using the recursive save functionality build into the generated code. As you can see, no external transaction is used, because the code starts a new transaction internally. You can always add the entities to an existing transaction, which then makes the save actions take place inside that transaction. Recursive saves always create a normal ADO.NET transaction with isolation level ReadComitted.

C#VB.NET

// create a new order and then 2 new order rows. 
// create new order entity. Use data from the object 'customer'
var newOrder = new OrderEntity();

// set the customer reference, which will sync FK-PK values.
// (newOrder.CustomerID = customer.CustomerID). You also could have said:
// newOrder.CustomerID = customer.CustomerID;
// or
// newOrder.CustomerID = _someVariable;
newOrder.Customer = customer;

newOrder.EmployeeID = 1;
newOrder.Freight = 10;
newOrder.OrderDate = DateTime.Now.AddDays(-3.0);
newOrder.RequiredDate = DateTime.Now.AddDays(3.0);
newOrder.ShipAddress = customer.Address;
newOrder.ShipCity = customer.City;
newOrder.ShipCountry = customer.Country;
newOrder.ShipName = "The Bounty";
newOrder.ShippedDate = DateTime.Now;
newOrder.ShipRegion = customer.Region;
newOrder.ShipVia = 1;
newOrder.ShipPostalCode = customer.PostalCode;

// Create new order row.
var newOrderRow = new OrderDetailsEntity();
newOrderRow.Discount = 0;
newOrderRow.ProductID = 10;
newOrderRow.Quantity = 200;
newOrderRow.UnitPrice = 31;
// make sure the OrderID fields are synchronized when
// newOrder is saved.
newOrder.OrderDetails.Add(newOrderRow);

// save the new order, recursively. This will first save customer
// if that's changed, then newOrder, then sync newOrder.OrderID with newOrderRow.OrderID
// and then save newOrderRow. The complete Save action is done inside an ADO.NET transaction.
newOrder.Save(true);

' create a new order and then 2 new order rows. 
' create new order entity. Use data from the object 'customer'
Dim newOrder As New OrderEntity()
' set the customer reference, which will sync FK-PK values.
' (newOrder.CustomerID = customer.CustomerID). You also could have said:
' newOrder.CustomerID = customer.CustomerID
' or
' newOrder.CustomerID = _someVariable
newOrder.Customer = customer
newOrder.EmployeeID = 1
newOrder.Freight = 10
newOrder.OrderDate = DateTime.Now.AddDays(-3.0)
newOrder.RequiredDate = DateTime.Now.AddDays(3.0)
newOrder.ShipAddress = customer.Address
newOrder.ShipCity = customer.City
newOrder.ShipCountry = customer.Country
newOrder.ShipName = "The Bounty"
newOrder.ShippedDate = DateTime.Now
newOrder.ShipRegion = customer.Region
newOrder.ShipVia = 1
newOrder.ShipPostalCode = customer.PostalCode

' Create new order row.
Dim newOrderRow As New OrderDetailsEntity()
newOrderRow.Discount = 0
newOrderRow.ProductID = 10
newOrderRow.Quantity = 200
newOrderRow.UnitPrice = 31
' make sure the OrderID fields are synchronized when
' newOrder is saved.
newOrder.OrderDetails.Add(newOrderRow)

' save the new order, recursively. This will first save customer
' if that's changed, then newOrder, then sync newOrder.OrderID with newOrderRow.OrderID
' and then save newOrderRow. The complete Save action is done inside an ADO.NET transaction.
newOrder.Save(True)

Transaction savepoints

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

// Create the transaction object, pass the isolation level and give it a name
using(var transactionManager = new Transaction(IsolationLevel.ReadCommitted, "SavepointRollback"))
{
    try
    {
        // first save a new address
        var newAddress = new AddressEntity();
        // ... fill the address entity with values

        transactionManager.Add(newAddress);
        
        // save it.
        newAddress.Save();

        // create a transaction save point
        transactionManager.Save("SavepointAddress");

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

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

' Create the transaction object, pass the isolation level and give it a name
Using transactionManager As new Transaction(IsolationLevel.ReadCommitted, "SavepointRollback")
    Try
        ' first save a new address
        Dim newAddress As New AddressEntity()
        ' ... fill the address entity with values

        transactionManager.Add(newAddress)
        
        ' save it.
        newAddress.Save()

        ' create a transaction save point
        transactionManager.Save("SavepointAddress")

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

            ' roll back to savepoint.
            transactionManager.Rollback("SavepointAddress")
        End Try
        
        ' commit the transaction. If the customer save failed, 
        ' only address is saved, otherwise both.
        transactionManager.Commit()
    Catch
        // fatal error, roll back everything
        transactionManager.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.
}

An LLBLGen Pro Transaction object is able to determine if it's participating inside an ambient transaction of System.Transactions. If so, it enlists a Resource Manager with the System.Transactions transaction. The Resource manager contains the LLBLGen Pro Transaction object.

As soon as a LLBLGen Pro Transaction 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. 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 Transaction object (if any) that it can commit/rollback the transaction. That call will then notify the enlisted entities of the outcome of the transaction.

Example

Below is an example which shows the usage of a TransactionScope in combination of a Transaction 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())
{
    // start a new LLBLGen Pro transaction
    using(var trans = new Transaction(System.Data.IsolationLevel.ReadCommitted, "Test"))
    {
        newCustomer.VisitingAddress = newAddress;
        newCustomer.BillingAddress = newAddress;
        // add the entities to save to the LLBLGen Pro transaction
        trans.Add(newCustomer);
        trans.Add(newAddress);
        // save both entities.
        Assert.IsTrue(newCustomer.Save(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. 
var fetchedCustomer = new CustomerEntity(customerId);
Assert.AreEqual(EntityState.New, fetchedCustomer.Fields.State);
var fetchedAddress = new AddressEntity(addressId);
Assert.AreEqual(EntityState.New, fetchedAddress.Fields.State);

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() 
    ' start a New LLBLGen Pro transaction
    Using trans As New Transaction(System.Data.IsolationLevel.ReadCommitted, "Test") 
        NewCustomer.VisitingAddress = NewAddress
        NewCustomer.BillingAddress = NewAddress
        ' add the entities to save to the LLBLGen Pro transaction
        trans.Add(NewCustomer)
        trans.Add(NewAddress)
        ' save both entities.
        Assert.IsTrue(NewCustomer.Save(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. 
Dim fetchedCustomer As New CustomerEntity(customerId)
Assert.AreEqual(EntityState.New, fetchedCustomer.Fields.State)
Dim fetchedAddress As New AddressEntity(addressId)
Assert.AreEqual(EntityState.New, fetchedAddress.Fields.State)