Business errors raised by a procedure

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

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.Execute.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_POSTGRESQL")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_POSTGRESQL environment variable to your PostgreSQL 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.PostgreSql.SampleOutput;

namespace ArxSamples.PostgreSql.Operations.Execute.RealCases;

/// <summary>
/// Business rules the database enforces, in three scenarios. <c>sp_reserve_credit</c> refuses an amount over the credit
/// limit (<c>RAISE EXCEPTION ... USING HINT</c>) and an account on hold (its own code, <c>AH001</c>). Both arrive as
/// <c>ARX3005 RaisedByDatabase</c> with the routine's message, its <c>SqlState</c> and its <c>Hint</c>, so the
/// application can tell "the database said no" from "something broke", and branch on the routine's own code.
/// </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 routine's own code.</summary>
    private static string ForTheUser(ArxException error) => error.SqlState switch
    {
        "AH001" => "This account is on hold. Please contact accounts.",
        "P0001" => $"That's over the credit limit. {error.Hint}.",
        _ => "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 routine's message and hint; 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($"hint: {error.Hint}; 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 routine's own code, <c>AH001</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 routine's own code");

        try
        {
            await dbContext.Service.ExecuteAsync(Reserve(accountId, 10m));
        }
        catch (ArxDatabaseException error) when (error.Code == ArxErrorCode.RaisedByDatabase)
        {
            Show($"{error.SqlState}: {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
      hint: reserve 60.00 or less; for the user: "That's over the credit limit. reserve 60.00 or less."
  > 3. An account on hold: the routine's own code
      AH001: ARX3005 RaisedByDatabase: account 2 is on hold; for the user: "This account is on hold. Please contact accounts."