How to: Use the UnitOfWork Class to Manage Database Transactions

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

You can use the UnitOfWork class to manage database transactions. You give a series of specific individual rows to a UnitOfWork object, in the form of table buffer variables. The UnitOfWork object successfully processes each row, or it rejects all changes. The UnitOfWork class automatically determines the correct sequence of delete, insert, and then update operations on tables that are linked by a foreign key relationship.

An X++ SQL delete_from statement can affect sets of rows, as defined by conditions that are specified in its where clause. In contrast, the UnitOfWork class cannot support set based operations. The UnitOfWork class operates only on the specific rows that it is given.

The UnitOfWork class automatically propagates the primary key value to the corresponding foreign key field, when the row with the foreign key field is inserted.

Prerequisites

To understand this topic, it helps you to first understand the following:

• How to: Create Tables – explains how to create a table and its fields.

• How to: Add a Relation to a Table – explains how to add a foreign key, and explains the RelatedTableRole property of each relation node.

Call Patterns for the UnitOfWork Class

There is a recurring pattern in the common uses of the UnitOfWork class. The call pattern for inserts is first to add one record at a time to your constructed UnitOfWork object. You add records for insert by calling the insertOnSaveChanges method. You can call this method multiple times in succession. After all the inserts have been added, you call the saveChanges method to persist the inserts to the database.

For updates and deletes, you must begin by using the X++ SQL select statement to retrieve the records from the database, and then put each into a table buffer. You must use the optimisticLock keyword on the select statement.

The pattern is similar for updates and deletes, except the records are added by calling the updateOnSaveChanges method. For deletes call the deleteOnSaveChanges method. You can add inserts, updates, and deletes in succession, in any sequence, and then call the saveChanges method.

Scenario for the Code Example

The code example in a following section performs SQL data operations on two tables. One table has a foreign key field that corresponds to the primary key field of the other table.

The fields for the parent table TabSale are as follows:

• RecId – the primary key.

• SaleName

• SaleComment

The fields for the child table TabLineItemOfSale are as follows:

• RecId – the primary key.

• LiosName

• LiosComment

• MasterSaleRecIdFky – the foreign key.

Code Example for the UnitOfWork Class

The following table lists the major lines of code in the subsequent code example, in the same order as they occur in the example.

Code for the X++ Method

The following X++ code sample demonstrates the UnitOfWork class.

    // X++, a method on a class. Not a job.
    server static public void RunTheUowDemoMethod()
    {
        UnitOfWork uow = new UnitOfWork();
        TabSale           tSale;    // Buffer for parent table.
        TabLineItemOfSale tLineIos; // Buffer for child  table.
        int64 i64MasterSaleRecIdFky;
    
        // Delete all rows, without using UoW.
        delete_from tLineIos;
        delete_from tSale;
    
        // Prepare a parent row for insert.
        // We let the system assign a RecId value, the primary key.
        tSale.SaleName = "Big";
        tSale.SaleComment = "A row in the parent table.";
    
        // Prepare a child row for insert.
        // Again, we let the system assign a RecId value, the primary key.
        // We also let the system assign SaleRecIdFky foreign key value!
        tLineIos.LiosName = "Chair";
        tLineIos.LiosComment = "To sit in.";
    
        // Method name is the RelatedTableRole property value.
        tLineIos.masterSale(tSale);
    
        // Prepare the UoW to do the inserts.
        uow.insertOnSaveChanges(tSale);
        uow.insertOnSaveChanges(tLineIos);
    
        // Before saving changes, prepare more inserts.
        // Add a second child to the current parent record.
        tLineIos.LiosName = "Desk";
        tLineIos.LiosComment = "To work at.";
        tLineIos.masterSale(tSale);
        uow.insertOnSaveChanges(tLineIos);
    
        // Add a second pair of parent + child records.
        tSale.SaleName = "Small";
        tSale.SaleComment = "Another row in the parent table.";
        tLineIos.LiosName = "Shirt";
        tLineIos.LiosComment = "To wear.";
        tLineIos.masterSale(tSale);
        uow.insertOnSaveChanges(tSale);
        uow.insertOnSaveChanges(tLineIos);
    
        // Make the changes to the SQL database, and commit.
        uow.saveChanges();
        //------------------------------------
    
        // Read the newly inserted child row.
        // Use optimistic concurrency, in case OccEnabled=No on the table.
        select optimisticLock
            LiosComment, MasterSaleRecIdFky
            from tLineIos
            where tLineIos.LiosName == "Desk";
    
        i64MasterSaleRecIdFky = tLineIos.MasterSaleRecIdFky;
    
        tLineIos.LiosComment =
            tLineIos.LiosComment + " Appended.";
    
        // Prepare the UoW to do the update. Then update.
        uow.updateonSaveChanges(tLineIos);
        uow.saveChanges();
    
        // All changes are complete. Display the results.
        tLineIos = null;
        tSale = null;
    
        select LiosName, LiosComment, RecId, MasterSaleRecIdFky
            from tLineIos
            where tLineIos.LiosName == "Desk";
    
        select SaleName, SaleComment, RecId
            from tSale
            where tSale.RecId == i64MasterSaleRecIdFky;
    
        // Display the parent RecId and the matching child foreign key.
        info(strFmt("TabSale:  RecId=%1 , SaleName=%2",
            tSale.RecId, tSale.SaleName));
        info(strFmt("TabLineItemOfSale:  MasterSaleRecIdFky=%1 , LiosName=%2 , RecId=%3 , LiosComment=%4",
            tLineIos.MasterSaleRecIdFky, tLineIos.LiosName, tLineIos.RecId, tLineIos.LiosComment));
    }

The following lines of output were displayed in the Infolog by the previous program.

TabSale: RecId=5637144847 , SaleName=Big

TabLineItemOfSale: MasterSaleRecIdFky=5637144847 , LiosName=Desk , RecId=5637144849 , LiosComment=To work at. Appended.

Special Factors

The following table lists special factors to consider when you use the UnitOfWork class.

See also

Data Integrity

Transaction Integrity

Announcements: New book: "Inside Microsoft Dynamics AX 2012 R3" now available. Get your copy at the MS Press Store.