Handle Execute errors

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 RealCase7_HandleErrors().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>
/// The errors an Execute call can raise, turned into messages, in two scenarios: what the database refuses (a procedure
/// that doesn't exist, arguments of the wrong type, a function called as a procedure, a timeout) and what ARX refuses
/// before it (a positional argument after a named one, an OUT parameter without a type, a cursor parameter).
/// </summary>
public sealed class RealCase7_HandleErrors : ISample
{
    public string Name => "execute-real-7-handle-errors";

    public string Title => "Handle Execute errors";

    public async Task RunAsync(SampleContext dbContext)
    {
        await Scenario1_WhatTheDatabaseRefusesAsync(dbContext);

        await Scenario2_WhatARXRefusesFirstAsync(dbContext);
    }

    private static async Task TryAsync(string label, Func<Task> call)
    {
        try
        {
            await call();
            Show($"{label}: no error");
        }
        catch (ArxException error)
        {
            Show($"{label}: {error.Code} ({(int)error.Code}), {error.Category}");
        }
    }

    /// <summary>1. Mapped from SQL Server's errors.</summary>
    private static async Task Scenario1_WhatTheDatabaseRefusesAsync(SampleContext dbContext)
    {
        Step("1. What the database refuses");

        await TryAsync("a procedure that doesn't exist", () => dbContext.Service.ExecuteAsync(new ArxProcedureExpression("sp_no_such_procedure")));

        var wrongTypes = new ArxProcedureExpression("sp_rename_account");
        wrongTypes.AddParameter("p_account_id", Guid.NewGuid());
        wrongTypes.AddParameter("p_name", "x");
        await TryAsync("the wrong argument types", () => dbContext.Service.ExecuteAsync(wrongTypes));

        // A table-valued function called as a procedure. (A scalar one would run: SQL Server's EXEC accepts it and returns
        // its value as the RETURN value. Call functions as functions either way.)
        var function = new ArxProcedureExpression("fn_contacts_named");
        function.AddParameter("Report-E601");
        await TryAsync("a function called as a procedure", () => dbContext.Service.ExecuteAsync(function));

        var slow = new ArxProcedureExpression("sp_slow");
        slow.AddParameter("p_seconds", 3.0);
        await TryAsync("a timeout of 1 second", () => dbContext.Service.ExecuteAsync(slow, new ArxExecuteOptions { CommandTimeout = 1 }));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, type
         * FROM sys.objects
         * WHERE name IN ('sp_no_such_procedure', 'sp_rename_account', 'fn_contact_count', 'sp_slow')
         * ORDER BY name;
         * -- Expect: 3 rows: fn_contact_count FN, sp_rename_account P, sp_slow P (no sp_no_such_procedure).
         * ===========================================================
         */
    }

    /// <summary>2. Refused before any connection opens.</summary>
    private static async Task Scenario2_WhatARXRefusesFirstAsync(SampleContext dbContext)
    {
        Step("2. What ARX refuses first");

        var mixed = new ArxProcedureExpression("sp_rename_account");
        mixed.AddParameter("p_account_id", 1L);
        mixed.AddParameter("positional after a named one");
        await TryAsync("a positional argument after a named one", () => dbContext.Service.ExecuteAsync(mixed));

        var untyped = new ArxProcedureExpression("sp_account_totals");
        untyped.Parameters.Add(new ArxProcedureParameter("p_orders", null, null, ArxProcedureParameterDirection.Out));
        await TryAsync("an OUT parameter without a type", () => dbContext.Service.ExecuteAsync(untyped));

        var cursor = new ArxProcedureExpression("sp_order_with_lines");
        cursor.AddCursorParameter("p_lines");
        await TryAsync("a cursor parameter (SQL Server has none)", () => dbContext.Service.ExecuteAsync(cursor));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE id = 1 AND name = 'positional after a named one';
         * -- Expect: 0: nothing reached the database.
         * ===========================================================
         */
    }
}

  > 1. What the database refuses
      a procedure that doesn't exist: UndefinedEntity (5001), Schema
      the wrong argument types: DataTypeMismatch (4004), Data
      a function called as a procedure: NotSupported (8001), NotSupported
      a timeout of 1 second: Timeout (7003), Transient
  > 2. What ARX refuses first
      a positional argument after a named one: InvalidArgument (1001), Validation
      an OUT parameter without a type: InvalidArgument (1001), Validation
      a cursor parameter (SQL Server has none): NotSupported (8001), NotSupported