Business errors raised by a procedure

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 RealCase4_BusinessErrorsFromTheDatabase().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.MySQL.SampleOutput;

namespace ArxSamples.MySQL.Operations.Execute.RealCases;

/// <summary>
/// Business rules the database enforces, in three scenarios. <c>sp_reserve_credit</c> refuses an account on hold
/// (<c>SIGNAL SQLSTATE '45000' … MYSQL_ERRNO = 50101</c>) and an amount over the credit limit (<c>MYSQL_ERRNO = 50102</c>).
/// Both arrive as
/// <c>ARX3005 RaisedByDatabase</c> with the procedure's message and its error number as <c>NativeCode</c>, so the
/// application can tell "the database said no" from "something broke", and branch on the procedure's own number.
/// </summary>
public sealed class RealCase4_BusinessErrorsFromTheDatabase : ISample
{
    public string Name => "execute-real-4-business-errors-from-the-database";

    public string Title => "Business errors raised by a procedure";

    public async Task RunAsync(SampleContext dbContext)
    {
        var open = new ArxEntity("account");
        open["name"] = "Credit Cove";
        open["account_number"] = "ACC-E801";
        open["credit_limit"] = 100m;
        var openId = await dbContext.Service.CreateAsync(open);

        var held = new ArxEntity("account");
        held["name"] = "Held Harbour";
        held["account_number"] = "ACC-E802";
        held["credit_limit"] = 500m;
        held["status"] = new ArxSelectionSetValue("AccountStatus", "ON_HOLD");
        var heldId = await dbContext.Service.CreateAsync(held);

        await Scenario1_WithinTheLimitAsync(dbContext, openId);

        await Scenario2_OverTheLimitAsync(dbContext, openId);

        await Scenario3_OnHoldAsync(dbContext, heldId);
    }

    private static ArxProcedureExpression Reserve(ArxRecordId accountId, decimal amount)
    {
        var procedure = new ArxProcedureExpression("sp_reserve_credit");
        procedure.AddParameter("p_account_id", accountId.AsInt64());
        procedure.AddParameter("p_amount", amount);
        return procedure;
    }

    /// <summary>The message for the user, by the procedure's own error number.</summary>
    private static string ForTheUser(ArxException error) => error.NativeCode switch
    {
        "50101" => "This account is on hold. Please contact accounts.",
        "50102" => "That's over the credit limit.",
        _ => "The request couldn't be completed."
    };

    /// <summary>1. 40 of a limit of 100: reserved.</summary>
    private static async Task Scenario1_WithinTheLimitAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("1. Within the limit");

        await dbContext.Service.ExecuteAsync(Reserve(accountId, 40m));
        Show("40.00 reserved");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT credit_limit
         * FROM account
         * WHERE account_number = 'ACC-E801';
         * -- Expect: 60.00.
         * ===========================================================
         */
    }

    /// <summary>2. 250 more: refused, with the procedure's message and number; nothing changed.</summary>
    private static async Task Scenario2_OverTheLimitAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("2. Over the limit");

        try
        {
            await dbContext.Service.ExecuteAsync(Reserve(accountId, 250m));
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.RaisedByDatabase)
        {
            ShowError(error);
            Show($"number {error.NativeCode}; for the user: \"{ForTheUser(error)}\"");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT credit_limit
         * FROM account
         * WHERE account_number = 'ACC-E801';
         * -- Expect: 60.00: the refused call changed nothing.
         * ===========================================================
         */
    }

    /// <summary>3. An account on hold: the procedure's own number, <c>50101</c>, so the application shows another message.</summary>
    private static async Task Scenario3_OnHoldAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("3. An account on hold: the procedure's own number");

        try
        {
            await dbContext.Service.ExecuteAsync(Reserve(accountId, 10m));
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.RaisedByDatabase)
        {
            Show($"{error.NativeCode}: {error.Message}; for the user: \"{ForTheUser(error)}\"");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT status, credit_limit
         * FROM account
         * WHERE account_number = 'ACC-E802';
         * -- Expect: ON_HOLD, 500.00.
         * ===========================================================
         */
    }
}

  > 1. Within the limit
      40.00 reserved
  > 2. Over the limit
      caught ArxDatabaseException: ARX3005 RaisedByDatabase: credit limit exceeded: limit 60.00, asked 250.00; reserve 60.00 or less.
      number 50102; for the user: "That's over the credit limit."
  > 3. An account on hold: the procedure's own number
      50101: ARX3005 RaisedByDatabase: account 2 is on hold: release the hold first.; for the user: "This account is on hold. Please contact accounts."