Wednesday, May 2, 2012

UnitOfWork - with example code

AX 2012 has a new concept of UnitOfWork aimed at managing database transactions. It's better than ttsbegin/ttscommit way of batching multiple transactions since that had some shortcomings like

•    transactions will start and commit on the client, UOW is on server.
•    transactions are open for a longer period
•    multiple RPC calls to the server, UOW takes a single call for multiple transactions
•    blocked in case there is user interaction inside tts scope

One interesting feature in UOW is that it automatically propagates the primary key value to the corresponding foreign key field, when the row with the foreign key field is inserted.

Methods at work
The UOW object takes a series of individual rows as parameters. It is designed to successfully process each row or it will reject all changes if any issue arises. In addition, the UOW class cannot support set based operations. It operates only on the specific rows that it is given.


optimisticLock keyword on the select statement for retrieving the objects for updates/deletes



Persist to DB

We will create a parent/child tables example to understand UOW.

1. Create two tables.
TabSale has a primary key (RecId) foreign relation with TabLineItemOfSale.
Fields for the parent table TabSale:
• SaleName
• SaleComment

Fields for the child table TabLineItemOfSale:
• LiosName
• LiosComment
• MasterSaleRecIdFky – the foreign key

Create a foreign key relation on TabLineItemOfSale, name it TabSale, table property as TabSale, RelatedTableRole as masterSale and CreateNavigationPropertyMethods as Yes.
Note that the method masterSale is not a physical instance method on TabLineItemOfSale but is created run time to propagate the primary key value to the foreign key.

Below text is copied from MSDN link.
When you set the CreateNavigationPropertyMethods property to Yes on a table relation, the system generates navigation methods for the table buffer class. A navigation method links two table buffer instances by their foreign key relationship. The UnitOfWork class is one area where this navigation linkage is used.
The name for a navigation method is copied from the value of the RelatedTableRole property on the table relation. This is true when the RelatedTableRole value is set explicitly in the Properties window, and when the RelatedTableRole value is generated by setting the UseDefaultRoleNames to Yes.

2. Create a class UnitOfWorkEg with a server static method runUnitOfWorkEg and paste below code in that.
server static public void runUnitOfWorkEg()
    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.
    // Prepare the UoW to do the inserts.

    // Before saving changes, prepare more inserts.
    // Add a second child to the current parent record.
    tLineIos.LiosName = "Desk";
    tLineIos.LiosComment = "To work at.";
    // 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.";
    // Make the changes to the SQL database, and commit.

    // 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.
    // 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));

3. Create a job UnitOfWorkExample with below code.
static void UnitOfWorkExample(Args _args)

4. Run the job and see the output.

No comments:

Post a Comment