Call a stored procedure

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.Execute.Basics;

// 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 Basic1_CallAProcedure().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

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

namespace ArxSamples.MSSQL.Operations.Execute.Basics;

/// <summary>
/// Calling a stored procedure, in three scenarios: named arguments; positional arguments; a parameter left out, so the
/// procedure's default is used. The call is described with <see cref="ArxProcedureExpression"/>, the way a query
/// is described with <see cref="ArxQueryExpression"/>; ARX sends <c>EXEC</c> with every value as a parameter.
/// </summary>
public sealed class Basic1_CallAProcedure : ISample
{
    public string Name => "execute-basics-1-call-a-procedure";

    public string Title => "Call a stored procedure";

    public async Task RunAsync(SampleContext dbContext)
    {
        var service = new ArxMsSqlDbService(dbContext.ConnectionString);

        var account = new ArxEntity("account");
        account["name"] = "Old Name Ltd";
        account["account_number"] = "ACC-E101";
        var accountId = await dbContext.Service.CreateAsync(account);

        await Scenario1_NamedArgumentsAsync(service, accountId);

        await Scenario2_PositionalArgumentsAsync(service, accountId);

        await Scenario3_ADefaultAsync(service, accountId);
    }

    /// <summary>1. <c>sp_rename_account(p_account_id, p_name, p_reason)</c> with every argument named. Nothing comes back.</summary>
    private static async Task Scenario1_NamedArgumentsAsync(ArxMsSqlDbService service, ArxRecordId accountId)
    {
        Step("1. Named arguments");

        var procedure = new ArxProcedureExpression("sp_rename_account");
        procedure.AddParameter("p_account_id", accountId.AsInt64());
        procedure.AddParameter("p_name", "New Name Ltd");
        procedure.AddParameter("p_reason", "rebranding");

        var result = await service.ExecuteAsync(procedure);
        Show($"renamed; outputs: {result.Outputs.Attributes.Count()}, result sets: {result.ResultSets.Count}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name
         * FROM account
         * WHERE account_number = 'ACC-E101';
         * -- Expect: New Name Ltd (until scenario 2 renames it again).
         * ===========================================================
         */
    }

    /// <summary>2. The same procedure with positional arguments, in the procedure's order.</summary>
    private static async Task Scenario2_PositionalArgumentsAsync(ArxMsSqlDbService service, ArxRecordId accountId)
    {
        Step("2. Positional arguments");

        var procedure = new ArxProcedureExpression("sp_rename_account");
        procedure.AddParameter(accountId.AsInt64());
        procedure.AddParameter("Positional Ltd");
        procedure.AddParameter("a positional call");

        await service.ExecuteAsync(procedure);
        Show("renamed to Positional Ltd");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name
         * FROM account
         * WHERE account_number = 'ACC-E101';
         * -- Expect: Positional Ltd (until scenario 3).
         * ===========================================================
         */
    }

    /// <summary>3. <c>p_reason</c> left out: the procedure's default (<c>'not given'</c>) is used.</summary>
    private static async Task Scenario3_ADefaultAsync(ArxMsSqlDbService service, ArxRecordId accountId)
    {
        Step("3. A parameter left out: the default is used");

        var procedure = new ArxProcedureExpression("sp_rename_account");
        procedure.AddParameter("p_account_id", accountId.AsInt64());
        procedure.AddParameter("p_name", "Final Name Ltd");

        await service.ExecuteAsync(procedure);
        Show("renamed to Final Name Ltd, without a reason");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, CASE WHEN modified_on IS NOT NULL THEN 1 ELSE 0 END AS modified
         * FROM account
         * WHERE account_number = 'ACC-E101';
         * -- Expect: Final Name Ltd, 1.
         * ===========================================================
         */
    }
}

  > 1. Named arguments
      renamed; outputs: 1, result sets: 0
  > 2. Positional arguments
      renamed to Positional Ltd
  > 3. A parameter left out: the default is used
      renamed to Final Name Ltd, without a reason