Transactions

Handling transacted operations is a common feature in business applications, hence CodeModeler provides it as an out of the box feature.

By default, all generated transactions are handled at a database level. In fact, if you look at the generated stored procedures, you'll find out that all writing procedures (saves and deletes) are encapsulated in transactions. Since a failed save and/or a failed delete operation could leave the database in an inconsistent state, they are wrapped into transactions. Consequently, the transaction will rollback if ever a problem is encountered while processing the operation.

For the following sample model:

Here is the default Delete procedure that gets generated:

CREATE PROCEDURE [dbo].[Customer_Delete]
(
  @Customer_Id [int],
  @_rowVersion [rowversion]
)
AS
SET NOCOUNT ON
DECLARE @error int, @rowcount int
DECLARE @tran bit; SELECT @tran = 0
IF @@TRANCOUNT = 0
BEGIN
  SELECT @tran = 1
  BEGIN TRANSACTION
END
UPDATE [Order] SET
[Order].[Order_Customer_Id] = NULL
WHERE ([Order].[Order_Customer_Id] = @Customer_Id)
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF(@error != 0)
BEGIN
  IF @tran = 1 ROLLBACK TRANSACTION
  RETURN
END
DELETE FROM [Customer]
WHERE (([Customer].[Customer_Id] = @Customer_Id) AND ([Customer].[_rowVersion] = @_rowVersion))
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF(@rowcount = 0)
BEGIN
  IF @tran = 1 ROLLBACK TRANSACTION
  RAISERROR (50001, 16, 1, 'Customer_Delete')
  RETURN
END
IF @tran = 1 COMMIT TRANSACTION
RETURN

As you can see, the stored procedure generated for the delete operation is encapsulated in a transaction.

For instance, if a developer wants to handle the use case where: if a customer gets deleted, all his orders should be deleted. In that case, we'd want the customer's orders to be removed only if the customer was successfully removed, in other words we want the customer and his orders to be removed in the same transaction.

To do so we can specify the “Cascade Delete” attribute on the customer's Orders property, such as this:

Here's what gets generated:

CREATE PROCEDURE [dbo].[Customer_Delete]
(
  @Customer_Id [int],
  @_rowVersion [rowversion]
)
AS
SET NOCOUNT ON
DECLARE @error int, @rowcount int
DECLARE @tran bit; SELECT @tran = 0
IF @@TRANCOUNT = 0
BEGIN
  SELECT @tran = 1
  BEGIN TRANSACTION
END
DELETE [Order] FROM [Order]
  LEFT OUTER JOIN [Customer] ON ([Order].[Order_Customer_Id] = [Customer].[Customer_Id])
  WHERE ([Customer].[Customer_Id] = @Customer_Id)
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
DELETE FROM [Customer]
  WHERE (([Customer].[Customer_Id] = @Customer_Id) AND ([Customer].[_rowVersion] = @_rowVersion))
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF(@rowcount = 0)
BEGIN
  IF @tran = 1 ROLLBACK TRANSACTION
  RAISERROR (50001, 16, 1, 'Customer_Delete')
  RETURN
END
IF @tran = 1 COMMIT TRANSACTION
RETURN

As you can see, lines contained in the Order table get deleted in the same transaction as the Customer.

Handling cascade deletes at the database level guarantees way better performances than doing it in upper layers.

Another point of interest concerning transactions is that they can also be handled at the Business Object Model (BOM) level as well. This can be useful in some cases: for instance, after a customer is saved, we'd like to update some business information and we'd like to encapsulate all that information into the same transaction.

Note: The “Cascade Save” attribute, unlike the “Cascade Delete” attribute, is done in the BOM. Consequently, to setup a transacted-save using the “Cascade Save” attribute, a developer should add a transaction rule on its Save method. This method is described here-under.

This can be modeled as so:

First, we create an “OnAfterSave” entity rule, which allows us to implement some extra business logic right after the Save is done. Then we add a C# snippet implementing that extra business logic. Finally, we add a rule to our Save method specifying to encapsulate that method into a transaction. This will generate the following C# code:

// Snippet method 'OnAfterSaveImpl’
private void OnAfterSave()
{
   // Do some business logic here.
}
public virtual bool Save()
{
    System.Transactions.TransactionScope scope = null;
    try
    {
        System.Transactions.TransactionOptions options = new System.Transactions.TransactionOptions();
        scope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, options);
        bool localSave = this.BaseSave(false);
        this.OnAfterSave();
        scope.Complete();
        return localSave;
    }
    finally
    {
        if ((scope != null))
        {
            scope.Dispose();
        }
    }
}

Note: Generating transactions in the BOM will necessitate that your BOM references the System.Transaction.dll assembly.

As you can see the Save method code is now wrapped into a transaction which also includes the call to our custom OnAfterSave method.

Transaction rules are available at several levels in the model:

• On the project node: it defines that all Save and Delete methods of all entities will be transactional,

• On an entity node: it defines that the Save and Delete method of the entity will be transactional,

• On a method node: it defines that the current method will be transactional.

 By default, when defining a transaction rule, the generated transaction will be a TransactionScope, however you can explicitly define an ADO transaction by doing as so:

Note: CodeModeler supports nested transactions. For instance, having a custom transactional Save on a Customer, and a transactional method SaveAllCustomers that calls iteratively the transactional Customer Save method, is fully supported.