A nightly job that commits in batches

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.Execute.RealCases;

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

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

The sample, as it is in the repository.

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

namespace ArxSamples.MSSQL.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 its progress.
/// ARX's own call runs outside any transaction, which a procedure that commits needs (inside one, SQL Server ends the
/// caller's transaction at the procedure's COMMIT and reports error 266, a changed transaction count);
/// inside an ARX transaction or a batch scope the same call is refused with ARX8001 and the reason.
/// </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);

        // Seven orders whose total needs recalculating (marked -1), each with one line of two units.
        for (var i = 1; i <= 7; i++)
        {
            var order = new ArxEntity("sales_order");
            order["order_number"] = $"SO-E9{i:D2}";
            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);

        await Scenario2_RunTheJobAsync(dbContext);
    }

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

    /// <summary>2. Batches of three, each committed; the progress arrives as messages. 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(), new ArxExecuteOptions { CommandTimeout = 600 });

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

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT SUM(CASE WHEN total = 10 THEN 1 ELSE 0 END) AS recalculated, SUM(CASE WHEN total < 0 THEN 1 ELSE 0 END) AS [left]
         * FROM sales_order
         * WHERE order_number LIKE 'SO-E9%';
         * -- Expect: 7, 0.
         * ===========================================================
         */
    }

    /// <summary>1. The job in an ARX transaction: SQL Server reports the changed transaction count (266), ARX8001 says why, and the transaction rolls back.</summary>
    private static async Task Scenario1_NotInsideATransactionAsync(SampleContext dbContext)
    {
        Step("1. Not inside an ARX transaction");

        await using var connection = new ARX.SDK.SQL.Services.ArxMsSqlConnection(dbContext.ConnectionString);
        await using var transaction = await connection.BeginTransactionAsync();

        try
        {
            await dbContext.Service.ExecuteAsync(Job(), transaction);
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.NotSupported)
        {
            ShowError(error);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM sales_order
         * WHERE order_number LIKE 'SO-E9%' AND total < 0;
         * -- Expect: 7 right after this scenario (the refused call changed nothing); 0 once scenario 2 has run.
         * ===========================================================
         */
    }
}

  > 1. Not inside an ARX transaction
  > 2. Run the job alone: a commit per batch
      0: recalculated 3 orders
      0: recalculated 6 orders
      0: recalculated 7 orders
      0: done: 7 orders