IceOnlys Blog

All Around Microsoft Dynamics Business Central

Back to all posts

BusinessCraft: Transactions, Events, and SQL Server

The last BusinessCraft post ended with two open questions: what happens when a posting fails halfway, and when do events arrive?

Both are answered now. A failed posting rolls back, the mini base app publishes events, and a second app subscribes to them. On the way, BusinessCraft also got a SQL Server driver and an Extension Management page.

Transactions

Here is the problem again. Sales Post inserts the invoice header, copies the lines, posts item ledger entries, and deletes the order:

procedure PostOrder(var OrderHeader: Record "Sales Header"): Code[20]
var
    InvoiceHeader: Record "Sales Invoice Header";
begin
    CheckOrder(OrderHeader);

    InvoiceHeader.Init();
    InvoiceHeader."Customer No." := OrderHeader."Customer No.";
    InvoiceHeader."Customer Name" := OrderHeader."Customer Name";
    InvoiceHeader."Posting Date" := WorkDate();
    InvoiceHeader."Order No." := OrderHeader."No.";
    InvoiceHeader.Insert(true);

    OnAfterInsertInvoiceHeader(OrderHeader, InvoiceHeader);

    CopyLines(OrderHeader, InvoiceHeader);
    OrderHeader.Delete(true);
    exit(InvoiceHeader."No.");
end;

To test the rollback, the app needed a way to fail in the middle. So items got an Allow Negative Inventory field, and Item Ledger Post now checks the stock before it posts a negative adjustment:

local procedure TestInventoryAvailable(ItemNo: Code[20]; Quantity: Decimal)
var
    Item: Record Item;
    AvailableInventory: Decimal;
begin
    Item.SetLoadFields("Allow Negative Inventory");
    Item.Get(ItemNo);
    if Item."Allow Negative Inventory" then
        exit;

    AvailableInventory := CalcInventory(ItemNo);
    if AvailableInventory < Quantity then
        Error(NotEnoughInventoryErr, ItemNo, AvailableInventory, Quantity);
end;

At that point the invoice header is already inserted, and the number series has already given out SI-00001. Before this change, the error left all of that behind.

How it works

Every client request now runs in a write transaction. Inserts, modifies, and deletes do not go to the database directly. They go into an overlay that belongs to the request:

  • Error throws the overlay away. Nothing reaches the database.
  • Commit writes the overlay to the database. A later Error does not undo it.
  • Reads in the same request see their own uncommitted rows. This matters: when an order has two lines for the same item, the inventory check for the second line has to see the ledger entry from the first line.
  • The end of the request commits whatever is left, like BC does when the code finishes without an error.

The overlay is task-local, so two sessions can post at the same time without seeing each other's uncommitted rows.

There is one special case. Confirm in BusinessCraft suspends the call, asks the user, and then runs the procedure again from the start. Without care, the first attempt's inserts would be stored twice. So the overlay is discarded before the second run. Rows that were committed before the Confirm stay.

The test

There is a new integration test, failed_post_rolls_the_invoice_back. It creates a customer and an item without inventory, creates an order, and posts it:

  1. The error dialog says Item IT-00001 has only 0 in inventory, ....
  2. The order is still there as SO-00001.
  3. The posted invoice list is empty.
  4. The item ledger entry list is empty.
  5. The item's inventory is still 0.

Then the test enables Allow Negative Inventory and posts again. The invoice is SI-00001. The number from the failed attempt was not used up, because the update of the number series was rolled back together with everything else.

RunModal in a write transaction

Every BC developer has seen this error at least once. You cannot call Page.RunModal, or Codeunit.Run with a used return value, after you have written to the database without a Commit.

BusinessCraft refuses it now too:

Page.RunModal or Codeunit.Run with a consumed return value is not allowed when a write transaction has been started.

And the other side works as well: if Codeunit.Run(...) then catches the error, throws away only that codeunit's writes, and the caller can continue and commit its own changes.

Events

The last post said: without events, there is no extensibility. Now there are events.

Sales Post publishes two business events:

[BusinessEvent(false, false)]
local procedure OnAfterInsertInvoiceHeader(var OrderHeader: Record "Sales Header"; var InvoiceHeader: Record "Sales Invoice Header")
begin
end;

[BusinessEvent(false, false)]
local procedure OnAfterInsertInvoiceLine(var InvoiceLine: Record "Sales Invoice Line"; var OrderLine: Record "Sales Line"; InvoiceHeader: Record "Sales Invoice Header")
begin
end;

BusinessCraft parses BusinessEvent and IntegrationEvent publishers and runs every subscriber in every installed app when the event is raised.

Subscribers are bound by parameter name, like in BC. A subscriber may leave out parameters it does not need, and it may declare them in any order. A parameter name that the publisher does not have is still an error.

A second app: Customer Level

To test this properly, I needed an extension that hooks into the base app. So there is a second app now, BusinessCraft Customer Level. It is the classic training example:

  • an enum Customer Level with Bronze, Silver, and Gold
  • a table extension on Customer, Sales Header, and Sales Invoice Header
  • page extensions on the customer card, the sales quote, the sales order, and the posted invoice
  • a German translation
  • a subscriber that copies the level from the order to the posted invoice
codeunit 50150 "Customer Level Mgt."
{
    [EventSubscriber(ObjectType::Codeunit, Codeunit::"Sales Post", OnAfterInsertInvoiceHeader, '', false, false)]
    local procedure SalesPost_OnAfterInsertInvoiceHeader(var OrderHeader: Record "Sales Header"; var InvoiceHeader: Record "Sales Invoice Header")
    begin
        InvoiceHeader."Customer Level" := OrderHeader."Customer Level";
        InvoiceHeader.Modify();
    end;
}

To get there, BusinessCraft had to learn a few more things: enum fields in table extensions, modify triggers in table extensions, and addlast in page extensions.

Of course, the subscriber's Modify runs inside the same write transaction as the posting. If the posting fails later, the customer level on the invoice is rolled back with the rest.

Extension Management

With two apps, you also want to see them. BusinessCraft now fills the platform tables Published Application and NAV App Installed App from the installed apps. The base app has an Extension Management page that reads them, just like the one in BC.

On the command line, an app can be uninstalled and its data can be removed afterwards:

# uninstall an app (keeps company tables and fields; refused if another app depends on it)
cargo run -p craft-server -- --database sqlite://data/craft.db --uninstall "Customer Level"

# later, drop that app's tables and extension columns
cargo run -p craft-server -- --database sqlite://data/craft.db --drop-app-data <app id>

This is the same two-step process as in BC: uninstall keeps the data, so a reinstall finds it again. Only the explicit cleanup deletes it.

SQL Server

Until now, BusinessCraft stored everything in SQLite. There is now an MSSQL driver as well:

cargo run -p craft-server -- --database "mssql://sa:Secret%40@localhost/CRONUS?encrypt=false" --app path/to/app
cargo run -p craft-web -- --backend server --database "mssql://sa:Secret%40@localhost/CRONUS?encrypt=false"

The physical schema follows the naming rules of BC: Company$Table$AppId for company tables, Table$AppId for shared tables, $Key1 for the primary key, and the $systemId, $systemCreatedAt, ... columns for the AL System* fields. The goal is a database that looks familiar when you open it in SQL Server Management Studio.

SQLite got some love too. Dates are now stored as DATE and DATETIME instead of text, and CalcSums is pushed down to a SQL SUM instead of summing in Rust.

The small things

A lot of web client and runtime work happened alongside:

  • The list filter pane groups fields into visible and available ones, like BC, and only offers real table fields. Options, booleans, and lookups get their own editors.
  • FactBoxes on sales documents: customer cues and the availability of the current line's item, with drill-downs as links.
  • DecimalPlaces is respected, with 2:2 as the default for amounts.
  • ExtendedDatatype email, phone, and URL fields are rendered as links in read-only view.
  • Document subpages can be collapsed like FastTabs.
  • The header and role center show the company's display name instead of the technical name.
  • UserId() and StrMenu work. The base app uses StrMenu to ask whether a new sales document should be a quote or an order.
  • ShortcutKey = 'Return' opens the selected row in a list.
  • TestField errors show the primary key and the translated table caption.
  • Media fields are left out of list repeaters, like in BC.
  • F5 no longer reloads the browser page and kills the session.
  • And a guide to run craft-web on Ubuntu behind nginx.

Numbers

  • 125 commits, up from 72
  • About 48,500 lines of Rust, up from about 40,900
  • Two apps: the mini base app and Customer Level
  • Three database backends: memory, SQLite, and SQL Server
  • Still a Cursor Pro subscription for $20 a month

Next: Permissions

Permissions are the next big construction site.

The NAV way already works. There are Permission Set, Permission, and Access Control tables. The first user gets SUPER, and BusinessCraft checks read, insert, modify, delete, and execute permissions on records, pages, and codeunits. Indirect permissions are not tested yet.

The hard part is the rest. Permissions need to be aggregated cleanly from all sources: system permission sets, permission sets from apps, and permission sets that users create in the client. And the Business Central logic around them has to run in BusinessCraft.

That logic is special. The permission pages in BC do not simply show a table. They work on temporary tables like Permission Set Buffer, create the records in Tenant Permission Set, and then read them back from Aggregate Permission Set. Temporary source tables, Page.Run with a temporary record, CurrPage.Editable, OnInsertRecord returning false on a temporary page: every one of these pages uses a different corner of the runtime.

Quite an adventure. I'll keep you updated. :)

BusinessCraft is not affiliated with or endorsed by Microsoft. "Microsoft", "Dynamics 365" and "Business Central" are trademarks of Microsoft Corporation.