A procedure that returns tables

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 Basic4_AProcedureThatReturnsTables().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>
/// A procedure that returns tables, in two scenarios. A SQL Server procedure returns a table by selecting it: each
/// SELECT it runs is a result set. ARX runs the call in one round trip and returns the result sets by position
/// (<c>ResultSets[0]</c>, <c>ResultSets[1]</c> …), in the order the procedure sends them.
/// </summary>
public sealed class Basic4_AProcedureThatReturnsTables : ISample
{
    public string Name => "execute-basics-4-a-procedure-that-returns-tables";

    public string Title => "A procedure that returns tables";

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

        foreach (var (sku, price) in new[] { ("SKU-E401", 10m), ("SKU-E402", 4.5m) })
        {
            var product = new ArxEntity("product");
            product["sku"] = sku;
            product["name"] = $"Product {sku}";
            product["unit_price"] = price;
            await dbContext.Service.CreateAsync(product);
        }

        var place = new ArxProcedureExpression("sp_place_order");
        place.AddParameter("p_account_id", accountId.AsInt64());
        place.AddParameter("p_order_number", "SO-E401");
        place.AddParameter("p_skus", "SKU-E401,SKU-E402");
        place.AddParameter("p_quantity", 2);
        place.AddOutParameter<long>("p_order_id");
        place.AddOutParameter<decimal>("p_total");
        var orderId = (long)(await dbContext.Service.ExecuteAsync(place)).Outputs["p_order_id"]!;

        await Scenario1_OneTableAsync(dbContext, orderId);

        await Scenario2_TwoTablesByPositionAsync(dbContext, orderId);
    }

    /// <summary>1. <c>sp_order_with_lines</c>'s second table, the lines: <c>ResultSets[1]</c>.</summary>
    private static async Task Scenario1_OneTableAsync(SampleContext dbContext, long orderId)
    {
        Step("1. One table");

        var procedure = new ArxProcedureExpression("sp_order_with_lines");
        procedure.AddParameter("p_order_id", orderId);

        var lines = (await dbContext.Service.ExecuteAsync(procedure)).ResultSets[1];
        Show(string.Join(", ", lines.Entities.Select(x => $"{x["sku"]} x{x["quantity"]} at {x["unit_price"]:N2}")));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT p.sku, l.quantity, l.unit_price
         * FROM order_line l
         * JOIN sales_order o ON o.id = l.order_id
         * JOIN product p ON p.id = l.product_id
         * WHERE o.order_number = 'SO-E401'
         * ORDER BY p.sku;
         * -- Expect: 2 rows: SKU-E401 2 10.00, SKU-E402 2 4.50.
         * ===========================================================
         */
    }

    /// <summary>2. Both tables: the order (<c>ResultSets[0]</c>) and its lines (<c>ResultSets[1]</c>).</summary>
    private static async Task Scenario2_TwoTablesByPositionAsync(SampleContext dbContext, long orderId)
    {
        Step("2. Two tables, by position");

        var procedure = new ArxProcedureExpression("sp_order_with_lines");
        procedure.AddParameter("p_order_id", orderId);

        var result = await dbContext.Service.ExecuteAsync(procedure);
        var order = result.ResultSets[0].Entities.Single();
        Show($"{order["order_number"]} for {order["account"]}, total {order["total"]:N2}; {result.ResultSets[1].Entities.Count} lines (ResultSets[1] is \"{result.ResultSets[1].EntityName}\")");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT o.order_number, a.name, o.total, (SELECT count(*) FROM order_line l WHERE l.order_id = o.id) AS lines
         * FROM sales_order o
         * JOIN account a ON a.id = o.account_id
         * WHERE o.order_number = 'SO-E401';
         * -- Expect: SO-E401, Tables Inc, 29.00, 2.
         * ===========================================================
         */
    }
}

  > 1. One table
      SKU-E401 x2 at 10.00, SKU-E402 x2 at 4.50
  > 2. Two tables, by position
      SO-E401 for Tables Inc, total 29.00; 2 lines (ResultSets[1] is "sp_order_with_lines")