Read OUT and INOUT values

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

The sample, as it is in the repository.

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

namespace ArxSamples.MSSQL.Operations.Execute.Basics;

/// <summary>
/// OUT and INOUT values, in two scenarios: an INOUT goes in and comes back (here filled in by the procedure when it
/// arrives as NULL), and OUT values come back typed in <see cref="ArxExecuteResult.Outputs"/>. SQL Server has one kind,
/// <c>OUTPUT</c>, which is both: ARX sends an OUT as an OUTPUT parameter that starts as NULL. Each is declared with its
/// .NET type, and ARX sizes it to hold any value of that type.
/// </summary>
public sealed class Basic2_ReadOutValues : ISample
{
    public string Name => "execute-basics-2-read-out-values";

    public string Title => "Read OUT and INOUT values";

    public async Task RunAsync(SampleContext dbContext)
    {
        var account = new ArxEntity("account");
        account["name"] = "Totals Trading";
        account["account_number"] = "ACC-E201";
        var accountId = await dbContext.Service.CreateAsync(account);

        foreach (var (number, year, total) in new[] { ("SO-E201", 2025, 100m), ("SO-E202", 2026, 250m), ("SO-E203", 2026, 50m) })
        {
            var order = new ArxEntity("sales_order");
            order["order_number"] = number;
            order["account_id"] = new ArxEntityReference("account", accountId);
            order["ordered_on"] = new DateTime(year, 6, 1, 12, 0, 0, DateTimeKind.Utc);
            order["total"] = total;
            await dbContext.Service.CreateAsync(order);
        }

        await Scenario1_AYearInAndTotalsOutAsync(dbContext, accountId);

        await Scenario2_TheYearFilledInByTheProcedureAsync(dbContext, accountId);
    }

    private static ArxProcedureExpression Totals(ArxRecordId accountId, int? year)
    {
        var procedure = new ArxProcedureExpression("sp_account_totals");
        procedure.AddParameter("p_account_id", accountId.AsInt64());
        procedure.AddInOutParameter("p_year", year);
        procedure.AddOutParameter<long>("p_orders");
        procedure.AddOutParameter<decimal>("p_revenue");
        return procedure;
    }

    /// <summary>1. <c>p_year</c> = 2026 in; the number of orders and the revenue of that year out.</summary>
    private static async Task Scenario1_AYearInAndTotalsOutAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("1. A year in, the totals out");

        var outputs = (await dbContext.Service.ExecuteAsync(Totals(accountId, 2026))).Outputs;
        Show($"year {outputs["p_year"]}: {outputs["p_orders"]} orders ({outputs["p_orders"]!.GetType().Name}), revenue {outputs["p_revenue"]:N2} ({outputs["p_revenue"]!.GetType().Name})");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*), sum(total)
         * FROM sales_order
         * WHERE order_number LIKE 'SO-E20%' AND YEAR(ordered_on) = 2026;
         * -- Expect: 2, 300.00.
         * ===========================================================
         */
    }

    /// <summary>2. <c>p_year</c> sent as NULL: the procedure fills in the current year and returns it.</summary>
    private static async Task Scenario2_TheYearFilledInByTheProcedureAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("2. An INOUT the procedure fills in");

        var outputs = (await dbContext.Service.ExecuteAsync(Totals(accountId, null))).Outputs;
        Show($"no year given: the procedure used {outputs["p_year"]}, {outputs["p_orders"]} orders");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT YEAR(SYSDATETIMEOFFSET()) AS this_year, count(*)
         * FROM sales_order
         * WHERE order_number LIKE 'SO-E20%' AND YEAR(ordered_on) = YEAR(SYSDATETIMEOFFSET());
         * -- Expect: the year and the count the sample printed.
         * ===========================================================
         */
    }
}

  > 1. A year in, the totals out
      year 2026: 2 orders (Int64), revenue 300.00 (Decimal)
  > 2. An INOUT the procedure fills in
      no year given: the procedure used 2026, 2 orders