A nightly job that commits in batches

Runs the sample. Generated for this page; it reads the connection string from the environment.

using ArxSamples.MySQL;
using ArxSamples.MySQL.Operations.Execute.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_MYSQL")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_MYSQL environment variable to your MySQL connection string.");

await new RealCase5_ANightlyJobThatCommits().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK.MySQL.Services;
using ARX.SDK.Query;
using ARX.SDK.Services;
using static ArxSamples.MySQL.SampleOutput;

namespace ArxSamples.MySQL.Operations.Execute.RealCases;

/// <summary>
/// A nightly job that commits as it goes, in two scenarios. <c>sp_recalculate_totals</c> recalculates order totals in
/// batches and commits after each, so a failure late in the run keeps the batches already done, and reports what it did
/// as a note. ARX's own call runs outside any transaction, which a procedure that commits needs. MySQL lets a procedure
/// COMMIT inside the caller's transaction too, and that COMMIT commits everything the transaction did before it, without
/// an error ARX could see: call such procedures outside ARX transactions and batch scopes.
/// </summary>
public sealed class RealCase5_ANightlyJobThatCommits : ISample
{
    public string Name => "execute-real-5-a-nightly-job-that-commits";

    public string Title => "A nightly job that commits in batches";

    public async Task RunAsync(SampleContext dbContext)
    {
        var account = new ArxEntity("account");
        account["name"] = "Nightly Nets";
        account["account_number"] = "ACC-E901";
        var accountId = await dbContext.Service.CreateAsync(account);

        var product = new ArxEntity("product");
        product["sku"] = "SKU-E901";
        product["name"] = "Recalculated product";
        product["unit_price"] = 5m;
        var productId = await dbContext.Service.CreateAsync(product);

        // Orders whose total needs recalculating (marked -1), each with one line of two units: three for scenario 1
        // (SO-E911 to SO-E913), seven for scenario 2 (SO-E921 to SO-E927).
        foreach (var number in Enumerable.Range(1, 3).Select(i => $"SO-E91{i}").Concat(Enumerable.Range(1, 7).Select(i => $"SO-E92{i}")))
        {
            var order = new ArxEntity("sales_order");
            order["order_number"] = number;
            order["account_id"] = new ArxEntityReference("account", accountId);
            order["ordered_on"] = new DateTime(2026, 11, 8, 1, 0, 0, DateTimeKind.Utc);
            order["total"] = -1m;
            var orderId = await dbContext.Service.CreateAsync(order);

            var line = new ArxEntity("order_line");
            line["order_id"] = new ArxEntityReference("sales_order", orderId);
            line["product_id"] = new ArxEntityReference("product", productId);
            line["quantity"] = 2;
            line["unit_price"] = 5m;
            await dbContext.Service.CreateAsync(line);
        }

        await Scenario1_NotInsideATransactionAsync(dbContext, accountId);

        await Scenario2_RunTheJobAsync(dbContext);
    }

    private static ArxProcedureExpression Job(string prefix)
    {
        var procedure = new ArxProcedureExpression("sp_recalculate_totals");
        procedure.AddParameter("p_prefix", prefix);
        procedure.AddParameter("p_batch", 3);
        return procedure;
    }

    /// <summary>
    /// 1. Why not inside an ARX transaction: the transaction writes a log row, calls the job, and rolls back. The job's
    /// COMMIT has already committed the log row and its own batches, so the rollback undoes nothing.
    /// </summary>
    private static async Task Scenario1_NotInsideATransactionAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("1. Not inside an ARX transaction: the procedure's COMMIT commits the caller's work");

        await using (var connection = new ArxMySqlConnection(dbContext.ConnectionString))
        {
            await using var transaction = await connection.BeginTransactionAsync();

            var log = new ArxEntity("activity_log");
            log["account_id"] = new ArxEntityReference("account", accountId);
            log["action"] = "nightly job E901 started";
            await dbContext.Service.CreateAsync(log, transaction);

            await dbContext.Service.ExecuteAsync(Job("SO-E91"), transaction);
            await transaction.RollbackAsync();
        }

        Show($"rolled back, yet the log row is there: {await dbContext.Direct.CountAsync("activity_log", "action = @p0", "nightly job E901 started")}, "
            + $"and {await dbContext.Direct.CountAsync("sales_order", "order_number LIKE 'SO-E91%' AND total = 10")} orders were recalculated");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT (SELECT count(*) FROM activity_log WHERE action = 'nightly job E901 started') AS log_rows,
         *        (SELECT count(*) FROM sales_order WHERE order_number LIKE 'SO-E91%' AND total = 10) AS recalculated;
         * -- Expect: 1, 3: the rollback came after the procedure's COMMIT, which had committed both.
         * ===========================================================
         */
    }

    /// <summary>2. Alone: batches of three, each committed; what it did arrives as a note. A long job sets a longer timeout.</summary>
    private static async Task Scenario2_RunTheJobAsync(SampleContext dbContext)
    {
        Step("2. Run the job alone: a commit per batch");

        var result = await dbContext.Service.ExecuteAsync(Job("SO-E92"), new ArxExecuteOptions { CommandTimeout = 600 });

        foreach (var message in result.Messages)
        {
            Show($"{message.Severity}: {message.Text}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT SUM(total = 10) AS recalculated, SUM(total < 0) AS still_negative
         * FROM sales_order
         * WHERE order_number LIKE 'SO-E92%';
         * -- Expect: 7, 0.
         * ===========================================================
         */
    }
}

  > 1. Not inside an ARX transaction: the procedure's COMMIT commits the caller's work
      rolled back, yet the log row is there: 1, and 3 orders were recalculated
  > 2. Run the job alone: a commit per batch
      Warning: done: 7 orders