A report with several result sets

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 RealCase2_AReportWithSeveralResultSets().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.RealCases;

/// <summary>
/// An account page built from one call: <c>sp_account_report</c> returns three tables (the contacts, the orders, a
/// summary row), read together in one round trip, each by its position: the order of the procedure's SELECTs.
/// </summary>
public sealed class RealCase2_AReportWithSeveralResultSets : ISample
{
    public string Name => "execute-real-2-a-report-with-several-result-sets";

    public string Title => "A report with several result sets";

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

        foreach (var first in new[] { "Rae", "Rob" })
        {
            var contact = new ArxEntity("contact");
            contact["first_name"] = first;
            contact["last_name"] = "Report-E601";
            contact["account_id"] = new ArxEntityReference("account", accountId);
            await dbContext.Service.CreateAsync(contact);
        }

        foreach (var (number, total) in new[] { ("SO-E601", 120m), ("SO-E602", 80m) })
        {
            var order = new ArxEntity("sales_order");
            order["order_number"] = number;
            order["account_id"] = new ArxEntityReference("account", accountId);
            order["ordered_on"] = new DateTime(2026, 11, 8, 9, 0, 0, DateTimeKind.Utc);
            order["total"] = total;
            await dbContext.Service.CreateAsync(order);
        }

        await Scenario1_ThreeTablesInOneCallAsync(dbContext, accountId);
    }

    /// <summary>1. Contacts, orders and a summary: ResultSets[0], [1] and [2].</summary>
    private static async Task Scenario1_ThreeTablesInOneCallAsync(SampleContext dbContext, ArxRecordId accountId)
    {
        Step("1. Three tables in one call");

        var report = new ArxProcedureExpression("sp_account_report");
        report.AddParameter("p_account_id", accountId.AsInt64());

        var sets = (await dbContext.Service.ExecuteAsync(report)).ResultSets;
        var summary = sets[2].Entities.Single();

        Show($"contacts: {string.Join(", ", sets[0].Entities.Select(x => x["first_name"]))}");
        Show($"orders: {string.Join(", ", sets[1].Entities.Select(x => $"{x["order_number"]} {x["total"]:N2}"))}");
        Show($"summary: {summary["contacts"]} contacts, {summary["orders"]} orders, revenue {summary["revenue"]:N2}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT (SELECT count(*) FROM contact c WHERE c.account_id = a.id) AS contacts,
         *        (SELECT count(*) FROM sales_order o WHERE o.account_id = a.id) AS orders,
         *        (SELECT sum(total) FROM sales_order o WHERE o.account_id = a.id) AS revenue
         * FROM account a
         * WHERE a.account_number = 'ACC-E601';
         * -- Expect: 2, 2, 200.00.
         * ===========================================================
         */
    }
}

  > 1. Three tables in one call
      contacts: Rae, Rob
      orders: SO-E601 120.00, SO-E602 80.00
      summary: 2 contacts, 2 orders, revenue 200.00