8.2 Data Manipulation & CRUD Logic

8.2 Data Manipulation & CRUD Logic

Quick Answer: Data manipulation in X++ centers on direct database cursor statements and table buffer methods. Queries utilize select and while select statements enhanced with qualifiers such as firstOnly (limits result set to a single record), forUpdate (locks records for write operations), and four join types: join (inner join), outer join (left outer join), exists join (evaluates existence without loading child fields), and notexists join. Transactions are governed by ttsbegin, ttscommit, and ttsabort; physical database commits only execute when the transaction level reaches zero (appl.ttsLevel() == 0). Standard table CRUD methods (insert(), update(), delete()) enforce application business logic, table events, and Chain of Command (CoC) extensions, whereas kernel methods (doInsert(), doUpdate(), doDelete()) bypass all business logic and event handlers.

---

1. Declarative Data Access: The X++ SELECT Statement

X++ embeds declarative database querying directly into the language syntax. Rather than writing external SQL connection strings and command objects, developers query tables using native table buffer variables.

Syntax & Query Qualifiers

CustTable custTable;

// Basic select with projection and qualifiers
select firstonly forupdate AccountNum, CreditMax from custTable
    index hint AccountIdx
    where custTable.CustGroup == "US-10"
       && custTable.Blocked   == CustVendorBlocked::No;

Key Query Keywords and Modifiers

• firstOnly: Restricts the database engine to return at most one matching record. Compiles to SELECT TOP 1 in SQL Server, significantly reducing network traffic and eliminating cursor overhead on the Application Object Server (AOS).
• forUpdate: Informs the database query optimizer and AOS that the retrieved record buffer will be updated or deleted. Under Optimistic Concurrency Control (OCC), this captures the current RecVersion stamp. Under Pessimistic Concurrency Control (PCC), an exclusive or update lock is acquired immediately.
• reverse: Reverses the sort order defined by the selected index or primary key.
• index hint <IndexName>: Instructs the SQL Server query optimizer to prioritize a specific database index. Because modern Azure SQL Query Store and intelligent optimizers generate efficient execution plans automatically, Microsoft best practice discourages using index hint unless performance telemetry proves the optimizer chose a suboptimal plan.
• Field Projection Lists: Specifying individual field names (e.g., select AccountNum, CreditMax from custTable) prevents fetching all table columns across the TDS network connection, conserving memory and bandwidth.

---

2. Advanced Relational Joins: Inner, Outer, Exists, and NotExists

X++ supports four distinct join keywords in select and while select statements. Mastering join behaviors is vital for query optimization and exam success.

Why exists join is Essential for Performance

When an application needs to filter parent records based on child criteria without displaying child data, an exists join is vastly superior to an inner join:

1. Network Payload Elimination: SQL Server does not project or transmit child columns over the network to the AOS tier.
2. Short-Circuit Query Execution: SQL Server terminates index scanning for a parent record the instant it encounters the first matching child row, rather than scanning and aggregating all matching child records.
3. Elimination of Duplicates: An inner join repeats parent records for every child row unless explicitly grouped; an exists join returns each matching parent row exactly once.

CustTable custTable;
CustTrans custTrans;

// Select customers who have at least one transaction in 2026
while select custTable
    exists join custTrans
    where custTrans.AccountNum == custTable.AccountNum
       && custTrans.TransDate  >= str2Date("01/01/2026", 321)
{
    // custTable contains valid customer data
    // NOTE: custTrans fields are NOT loaded and cannot be read here!
    info(strFmt("Customer %1 has transactions in 2026.", custTable.AccountNum));
}

---

3. Cursor Iteration: while select vs. Set-Based Operations

When multiple rows must be processed, X++ provides the while select looping statement. The runtime opens a database cursor and fetches rows sequentially.

CustTable custTable;

// Sequential cursor traversal
while select forupdate custTable
    where custTable.CustGroup == "US-20"
{
    ttsbegin;
    custTable.CreditMax += 500.00;
    custTable.update();
    ttscommit;
}

Set-Based SQL Optimization Alternatives

While while select is necessary when complex per-row business logic or external service calls must occur, executing row-by-row updates on millions of records generates excessive database round trips. Dynamics 365 F&O provides three set-based operators that translate directly into single SQL statements:

1. insert_recordset TargetTable (Field1, Field2) select SourceField1, SourceField2 from SourceTable: Generates a single INSERT INTO ... SELECT statement executed entirely inside Azure SQL.
2. update_recordset TableBuffer setting Field1 = expr where Condition: Generates a single UPDATE Table SET ... WHERE statement.
3. delete_from TableBuffer where Condition: Generates a single DELETE FROM Table WHERE statement.

[!NOTE] Automatic Set-Based Fallback to Row-by-Row If a table overrides the database methods (insert, update, or delete), has active database logs, or has table event subscribers, the runtime automatically falls back from set-based SQL execution to row-by-row cursor execution to ensure business code runs, unless RecordInsertList or low-level bypasses are invoked.

---

4. Transaction Integrity: ttsbegin, ttscommit, and ttsabort

The Transaction Tracking System (TTS) guarantees database integrity by enforcing ACID (Atomicity, Consistency, Isolation, Durability) transaction properties. In X++, transactions are defined using ttsbegin and ttscommit blocks.

The Transaction Level Mechanism (appl.ttsLevel())

X++ supports nested transaction scopes. Functions throughout the application can open transaction blocks independently without knowing whether an outer transaction is already active.

Transaction Level Progression:
[Level 0] -> ttsbegin -> [Level 1] -> ttsbegin -> [Level 2] 
                                                        |
[Level 0] <- ttscommit <- [Level 1] <- ttscommit <------+

1. ttsbegin: Increments the integer transaction level counter by 1. If appl.ttsLevel() moves from 0 to 1, the AOS starts a physical transaction with SQL Server (BEGIN TRANSACTION).
2. ttscommit: Decrements the transaction level counter by 1. Crucial Rule: No physical commit is written to the database until appl.ttsLevel() reaches 0. Nested ttscommit calls at level 2 or 1 merely decrement the counter.
3. ttsabort: Immediately rolls back all database modifications performed across all nested transaction levels, resets appl.ttsLevel() to 0, and releases all held table row locks.

info(strFmt("TTS Level: %1", appl.ttsLevel())); // Prints 0
ttsbegin;
    info(strFmt("TTS Level: %1", appl.ttsLevel())); // Prints 1
    ttsbegin;
        info(strFmt("TTS Level: %1", appl.ttsLevel())); // Prints 2
    ttscommit; // Decrements to 1; NO database commit occurs here!
    info(strFmt("TTS Level: %1", appl.ttsLevel())); // Prints 1
ttscommit; // Decrements to 0; PHYSICAL COMMIT executes in SQL Server!
info(strFmt("TTS Level: %1", appl.ttsLevel())); // Prints 0

Optimistic Concurrency Control (OCC) and forUpdate

By default, tables in Dynamics 365 F&O utilize Optimistic Concurrency Control (OCCEnabled = Yes). Under OCC:

• When a table buffer is selected without forUpdate, no locks are held on the database row.
• When selected forUpdate, the runtime caches the record's 32-bit RecVersion field.
• When .update() is invoked inside ttscommit, SQL Server executes: UPDATE Table SET ..., RecVersion = newVersion WHERE RecId = @id AND RecVersion = @cachedVersion.
• If another process altered the row in the interim, the RecVersion values do not match, zero rows are affected, and the AOS throws an Exception::UpdateConflict.

---

5. CRUD Architecture: Standard Methods vs. Kernel do... Methods

Every table in Dynamics 365 F&O inherits from the system class Common. When interacting with table buffers, developers can choose between standard business methods and kernel do... methods.

Comparison: Standard CRUD Methods vs. Kernel do... Methods

CustTable custTable;

// PATTERN A: Standard Business CRUD (Enforces business rules and events)
ttsbegin;
custTable.AccountNum = "US-099";
custTable.CustGroup  = "US-10";
if (custTable.validateWrite())
{
    custTable.insert(); // Fires table events, defaulting, and extensions
}
ttscommit;

// PATTERN B: Kernel doInsert (Low-level bypass)
ttsbegin;
custTable.AccountNum = "US-100";
custTable.CustGroup  = "US-10";
// Bypasses validateWrite, insert overrides, event handlers, and CoC!
custTable.doInsert(); 
ttscommit;

[!CAUTION] The Dangers of Kernel do... Methods Calling doInsert(), doUpdate(), or doDelete() in business code violates core ERP architecture. It bypasses data integrity checks, prevents third-party ISV extensions from running, disables auditing, and corrupts child tables by skipping cascade deletes. Never use do... methods unless building specialized bulk staging tools where upstream validation has already occurred.

---

6. The Validation Lifecycle Pattern & Data Integrity

To ensure transactional consistency, Microsoft architecture separates data validation from persistent writing. Tables provide two validation entry points:

1. validateWrite(): Evaluates whether the current record buffer contains valid data to be inserted or updated. Checks mandatory fields, range constraints, and referential keys.
2. validateDelete(): Evaluates whether the record can be deleted without violating foreign key constraints or active transactional history.

The Golden Rule of CRUD Persistence

Calling custTable.insert() or custTable.update() does not automatically execute validateWrite(). The runtime assumes that validation has already been performed or that headless background logic is intentionally managing validation state. Therefore, developers must explicitly call validateWrite() prior to modifying data.

public static void saveCustomer(CustTable _custTable)
{
    // Step 1: Validate prior to modifying database
    if (!_custTable.validateWrite())
    {
        throw error("@ApplicationPlatform:CannotSaveRecord");
    }

    // Step 2: Persist within transaction
    ttsbegin;
    if (_custTable.RecId == 0)
    {
        _custTable.insert();
    }
    else
    {
        _custTable.update();
    }
    ttscommit;
}

---

7. Scenario Walk-Through: High-Concurrency Credit Limit Update

Scenario Description

An e-commerce order processing engine frequently updates customer credit balances as orders are placed. Due to high transaction volume, concurrent web threads frequently attempt to update the same customer record simultaneously. The code must select the customer record for update, perform business validation on the new credit limit, persist the update inside a transaction, and catch any Optimistic Concurrency Control collisions (Exception::UpdateConflict).

Step-by-Step Implementation Flow

1. Initialize Retry Counter: Create a retry control loop supporting up to 3 attempts.
2. Outer try Block: Place try before ttsbegin to ensure clean rollback on collision.
3. Select forUpdate: Lock the record buffer and capture the active RecVersion.
4. Validate Business Constraints: Call validateWrite() and checkFailed if credit limit exceeds company thresholds.
5. Persist with update(): Invoke standard .update() to trigger audit logging and events.
6. Commit Transaction: Execute ttscommit to finalize changes in Azure SQL.

public class CustCreditLimitManager
{
    public static void adjustCreditLimit(CustAccount _accountNum, real _adjustmentAmount)
    {
        #define.MaxRetries(3)
        int retryCount = 0;

        try
        {
            ttsbegin;

            CustTable custTable;
            // Critical: Select forUpdate to signal write intent and capture RecVersion
            select firstonly forupdate custTable
                where custTable.AccountNum == _accountNum;

            if (!custTable)
            {
                throw error(strFmt("@ABC:CustomerNotFound", _accountNum));
            }

            // Apply business adjustment
            custTable.CreditMax += _adjustmentAmount;

            // Validate record state before persisting
            if (custTable.validateWrite())
            {
                custTable.update(); // Standard update invoking CoC and events
            }
            else
            {
                throw error("@ABC:CreditLimitValidationFailed");
            }

            ttscommit; // Persist changes to Azure SQL Database
            info(strFmt("@ABC:CreditLimitUpdated", _accountNum, custTable.CreditMax));
        }
        catch (Exception::UpdateConflict)
        {
            if (retryCount < #MaxRetries)
            {
                retryCount++;
                sleep(75 * retryCount); // Dynamic backoff
                retry; // Returns to try, re-reading fresh RecVersion
            }
            else
            {
                error(strFmt("@ABC:ConcurrencyFailure", _accountNum));
                throw Exception::UpdateConflict;
            }
        }
    }
}

---

8. Real-World Exam Traps: Data Manipulation & CRUD

[!WARNING] Exam Trap 1: Updating a Buffer Selected Without forUpdate A recurring exam question provides a code snippet where a record is retrieved via select firstonly custTable where ... (omitting forUpdate), modified, and followed by custTable.update();. This causes a runtime exception: "Cannot edit a record in Customer (CustTable). The record must be selected forUpdate." You cannot mutate a buffer selected without forUpdate.

[!WARNING] Exam Trap 2: Believing doUpdate() Fires Table Event Handlers Questions often ask how to optimize performance when running bulk updates while ensuring that custom business events and third-party extensions still execute. Options suggesting doUpdate() or doInsert() are traps! Kernel do... methods bypass all application logic, table event handlers (onUpdating, onUpdated), and Chain of Command methods.

[!WARNING] Exam Trap 3: Expecting Intermediate ttscommit Calls to Persist Data An exam scenario will present nested transactions (e.g., three nested ttsbegin calls) and ask at which line data is permanently committed to Azure SQL Database. Remember: intermediate ttscommit statements only decrement appl.ttsLevel(). Data is committed to physical storage exclusively when appl.ttsLevel() transitions to 0.

[!WARNING] Exam Trap 4: Attempting to Read Fields from an exists join Buffer An exam question presents a query with while select salesTable exists join salesLine where ... and then asks what value is printed by info(salesLine.ItemId);. Because exists join evaluates only the existence of matching rows and never loads child columns into AOS memory, salesLine.ItemId is completely blank/empty.