A procedure that returns tables

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

using ArxSamples.PostgreSql;
using ArxSamples.PostgreSql.Operations.Execute.Basics;

// 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 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.PostgreSql.SampleOutput;

namespace ArxSamples.PostgreSql.Operations.Execute.Basics;

/// <summary>
/// A procedure that returns tables, in two scenarios. A PostgreSQL procedure returns rows through cursors:
/// each table is an <c>INOUT refcursor</c> parameter, declared with <c>AddCursorParameter</c>. ARX runs the call and reads
/// every declared cursor in one transaction (two round trips), and returns each table as a result set named after its
/// parameter.
/// </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", new[] { "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_TwoTablesByNameAsync(dbContext, orderId);
    }

    /// <summary>1. <c>sp_order_with_lines</c> asked for its lines only: the other cursor parameter is left at its default.</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);
        procedure.AddCursorParameter("p_lines");

        var lines = (await dbContext.Service.ExecuteAsync(procedure)).ResultSets["p_lines"];
        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 and its lines, by name (and by position, in declaration order).</summary>
    private static async Task Scenario2_TwoTablesByNameAsync(SampleContext dbContext, long orderId)
    {
        Step("2. Two tables, by name");

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

        var result = await dbContext.Service.ExecuteAsync(procedure);
        var order = result.ResultSets["p_order"].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 name
      SO-E401 for Tables Inc, total 29.00; 2 lines (ResultSets[1] is "p_lines")