Page through a procedure's result: the procedure pages itself, ARX pages a function, ARX pages a result set

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 RealCase3_PageThroughAProcedure().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>
/// Paging what a procedure or function returns, the three ways, in four scenarios. Each sets
/// <see cref="ArxProcedureExpression.PageInfo"/>, as a query does, and gets back a result set with <c>CurrentPage</c>,
/// <c>HasNextPage</c> and, when asked, the total. The total is opt-in: ask for it once (page 1), as for RetrieveMultiple.
/// <list type="number">
/// <item><b>The routine pages itself</b> (best for large data: the database produces only the page). ARX fills its page
/// arguments from <c>PageInfo</c> and reads its total from an OUT parameter.</item>
/// <item><b>ARX pages a function's rows</b>: <c>OFFSET</c>/<c>FETCH NEXT</c> and the total in one round trip. Cheap
/// for an inline table-valued function (SQL Server expands it, so the order and the limit reach the index); a
/// multi-statement function fills its whole table first.</item>
/// <item><b>ARX pages a table the procedure returns</b> (a result set): SQL Server has no cursor to move, so ARX reads
/// past the earlier rows, keeps one page and one row more, and counts the rest. Every row crosses the network.</item>
/// <item>Which to choose.</item>
/// </list>
/// </summary>
public sealed class RealCase3_PageThroughAProcedure : ISample
{
    public string Name => "execute-real-3-page-through-a-procedure";

    public string Title => "Page through a procedure's result: the procedure pages itself, ARX pages a function, ARX pages a result set";

    private const string PREFIX = "PG-E7";

    public async Task RunAsync(SampleContext dbContext)
    {
        var products = Enumerable.Range(1, 120).Select(i =>
        {
            var product = new ArxEntity("product");
            product["sku"] = $"{PREFIX}{i:D3}";
            product["name"] = $"Paged product {i}";
            product["unit_price"] = 1m + i % 10;
            return product;
        }).ToList();

        await dbContext.Service.CreateMultipleAsync(products);

        await Scenario1_TheProcedurePagesItselfAsync(dbContext);

        await Scenario2_ARXPagesAFunctionAsync(dbContext);

        await Scenario3_ARXPagesAProceduresTableAsync(dbContext);

        Scenario4_WhichToChoose();
    }

    private static string Describe(ArxEntityCollection page)
        => $"page {page.CurrentPage}: {page.Entities.Count} rows ({page.Entities.FirstOrDefault()?["sku"]} to {page.Entities.LastOrDefault()?["sku"]}), next page: {page.HasNextPage}"
           + (page.TotalRecordCount > 0 ? $", {page.TotalRecordCount} in all, {page.TotalPageCount} pages" : string.Empty);

    /// <summary>
    /// 1. <c>sp_products_page(p_prefix, p_page, p_page_size, INOUT p_rows, INOUT p_total)</c> pages itself. ARX fills
    /// <c>p_page</c> and <c>p_page_size</c>, reads the table from <c>p_rows</c> and the total from <c>p_total</c>.
    /// </summary>
    private static async Task Scenario1_TheProcedurePagesItselfAsync(SampleContext dbContext)
    {
        Step("1. The procedure pages itself");

        foreach (var pageNumber in new[] { 1, 3 })
        {
            var procedure = new ArxProcedureExpression("sp_products_page");
            procedure.AddParameter("p_prefix", PREFIX);
            procedure.PageInfo = new ArxPagingInfo { Count = 50, PageNumber = pageNumber, ReturnTotalRecordCount = pageNumber == 1 };
            procedure.PagingParameters = ArxPagingParameters.PageNumber("p_page", "p_page_size", totalRecordCount: "p_total");

            Show(Describe((await dbContext.Service.ExecuteAsync(procedure)).ResultSets[0]));
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*), min(sku), max(sku)
         * FROM (SELECT sku FROM product WHERE sku LIKE 'PG-E7%' ORDER BY sku OFFSET 100 ROWS FETCH NEXT 50 ROWS ONLY) AS page_3;
         * -- Expect: 20, PG-E7101, PG-E7120 (page 1 is PG-E7001 to PG-E7050; 120 in all, 3 pages).
         * ===========================================================
         */
    }

    /// <summary>
    /// 2. <c>fn_products_like(p_prefix)</c> returns every matching product; ARX orders and pages its rows, with the total
    /// in the same round trip.
    /// </summary>
    private static async Task Scenario2_ARXPagesAFunctionAsync(SampleContext dbContext)
    {
        Step("2. ARX pages a function's rows");

        var function = new ArxProcedureExpression("fn_products_like") { ProcedureType = ArxProcedureType.Function };
        function.AddParameter(PREFIX);
        function.AddOrder("unit_price", ArxOrderType.Descending);
        function.AddOrder("sku", ArxOrderType.Ascending);
        function.PageInfo = new ArxPagingInfo { Count = 25, PageNumber = 2, ReturnTotalRecordCount = true };

        var page = (await dbContext.Service.ExecuteAsync(function)).ResultSets[0];
        Show(Describe(page) + $"; prices {page.Entities[0]["unit_price"]:N2} to {page.Entities[^1]["unit_price"]:N2}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT sku, unit_price
         * FROM fn_products_like('PG-E7')
         * ORDER BY unit_price DESC, sku OFFSET 25 ROWS FETCH NEXT 25 ROWS ONLY;
         * -- Expect: 25 rows, PG-E7017 (8.00) first and PG-E7015 (6.00) last (120 in all, 5 pages).
         * ===========================================================
         */
    }

    /// <summary>3. <c>sp_products</c> returns every matching product as a result set; ARX pages it.</summary>
    private static async Task Scenario3_ARXPagesAProceduresTableAsync(SampleContext dbContext)
    {
        Step("3. ARX pages a table the procedure returns");

        var procedure = new ArxProcedureExpression("sp_products");
        procedure.AddParameter("p_prefix", PREFIX);
        procedure.PageInfo = new ArxPagingInfo { Count = 40, PageNumber = 3, ReturnTotalRecordCount = true };

        Show(Describe((await dbContext.Service.ExecuteAsync(procedure)).ResultSets[0]));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*), min(sku), max(sku)
         * FROM (SELECT sku FROM product WHERE sku LIKE 'PG-E7%' ORDER BY sku OFFSET 80 ROWS FETCH NEXT 40 ROWS ONLY) AS page_3;
         * -- Expect: 40, PG-E7081, PG-E7120: the last page (no next page; 120 in all, 3 pages).
         * ===========================================================
         */
    }

    /// <summary>4. The choice, with the numbers of <c>ExecutePagingBenchmarks</c>.</summary>
    private static void Scenario4_WhichToChoose()
    {
        Step("4. Which to choose");

        Show("large data, deep pages: let the routine page itself (1); the database produces only the page.");
        Show("an inline table-valued function: let ARX page it (2); SQL Server expands it, so the limit reaches the index.");
        Show("a multi-statement function or a procedure's result set: ARX pages it (2, 3), but every skipped row is still produced (and, for 3, sent).");
        Show("the total costs a count of every row: ask for it on page 1 only, or not at all for \"load more\".");
    }
}

  > 1. The procedure pages itself
      page 1: 50 rows (PG-E7001 to PG-E7050), next page: True, 120 in all, 3 pages
      page 3: 20 rows (PG-E7101 to PG-E7120), next page: False
  > 2. ARX pages a function's rows
      page 2: 25 rows (PG-E7017 to PG-E7015), next page: True, 120 in all, 5 pages; prices 8.00 to 6.00
  > 3. ARX pages a table the procedure returns
      page 3: 40 rows (PG-E7081 to PG-E7120), next page: False, 120 in all, 3 pages
  > 4. Which to choose
      large data, deep pages: let the routine page itself (1); the database produces only the page.
      an inline table-valued function: let ARX page it (2); SQL Server expands it, so the limit reaches the index.
      a multi-statement function or a procedure's result set: ARX pages it (2, 3), but every skipped row is still produced (and, for 3, sent).
      the total costs a count of every row: ask for it on page 1 only, or not at all for "load more".