Page through results

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

using ArxSamples.MySQL;
using ArxSamples.MySQL.Operations.RetrieveMultiple.Basics;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_MYSQL")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_MYSQL environment variable to your MySQL connection string.");

await new Basic2_PageThroughResults().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK.MySQL.Services;
using ARX.SDK.Query;
using ARX.SDK.Services;

using static ArxSamples.MySQL.SampleOutput;

namespace ArxSamples.MySQL.Operations.RetrieveMultiple.Basics;

/// <summary>
/// Paging, in three scenarios: one page with the total (in the same round trip); every page in a loop on HasNextPage;
/// and a page past the end, which still knows the total.
/// </summary>
public sealed class Basic2_PageThroughResults : ISample
{
    public string Name => "retrieve-multiple-basics-2-page-through-results";

    public string Title => "Page through results";

    public async Task RunAsync(SampleContext dbContext)
    {
        var service = new ArxMySqlDbService(dbContext.ConnectionString, ArxSchemaCatalog.Create()
            .Table("product", ArxIdColumn.Generated<long>("id"))
            .Build());

        var products = Enumerable.Range(1, 7).Select(i =>
        {
            var product = new ArxEntity("product");
            product["sku"] = $"PAGE-R50{i}";
            product["name"] = $"Paged product {i}";
            product["unit_price"] = 10m + i;
            return product;
        }).ToList();

        await service.CreateMultipleAsync(products);

        await Scenario1_OnePageAndTheTotalAsync(service);

        await Scenario2_EveryPageAsync(service);

        await Scenario3_PastTheEndAsync(service);
    }

    /// <summary>The query every scenario pages through: products whose SKU begins with PAGE-R50, by SKU, 3 to a page.</summary>
    private static ArxQueryExpression Products(int pageNumber, bool total)
    {
        var query = new ArxQueryExpression("product");
        query.ColumnSet.AddColumns("sku", "name");
        query.Criteria.AddCondition("sku", ArxConditionOperator.BeginsWith, "PAGE-R50");
        query.AddOrder("sku", ArxOrderType.Ascending);
        query.PageInfo = new ArxPagingInfo { Count = 3, PageNumber = pageNumber, ReturnTotalRecordCount = total };
        return query;
    }

    /// <summary>1. Page 1 of 3 rows, with the total: one round trip.</summary>
    private static async Task Scenario1_OnePageAndTheTotalAsync(ArxMySqlDbService service)
    {
        Step("1. One page");

        var page = await service.RetrieveMultipleAsync(Products(1, total: true));

        Show($"{string.Join(", ", page.Entities.Select(x => x["sku"]))}; {page.TotalRecordCount} in all, {page.TotalPageCount} pages, next page: {page.HasNextPage}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT sku
         * FROM product
         * WHERE sku LIKE 'PAGE-R50%'
         * ORDER BY sku
         * LIMIT 3 OFFSET 0;
         * -- Expect: 3 rows, PAGE-R501, PAGE-R502, PAGE-R503 (and 7 rows without the FETCH).
         * ===========================================================
         */
    }

    /// <summary>2. Every page: HasNextPage is right without the total (ARX reads one row more than the page).</summary>
    private static async Task Scenario2_EveryPageAsync(ArxMySqlDbService service)
    {
        Step("2. Every page");

        var read = new List<string>();
        var pageNumber = 1;
        ArxEntityCollection page;

        do
        {
            page = await service.RetrieveMultipleAsync(Products(pageNumber++, total: false));
            read.AddRange(page.Entities.Select(x => (string)x["sku"]!));
        }
        while (page.HasNextPage);

        Show($"{read.Count} products in {pageNumber - 1} pages");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM product
         * WHERE sku LIKE 'PAGE-R50%';
         * -- Expect: 7, the products the loop read.
         * ===========================================================
         */
    }

    /// <summary>3. A page past the end: no rows, and the total is still there.</summary>
    private static async Task Scenario3_PastTheEndAsync(ArxMySqlDbService service)
    {
        Step("3. Past the end");

        var page = await service.RetrieveMultipleAsync(Products(9, total: true));

        Show($"{page.Entities.Count} rows, total {page.TotalRecordCount}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM product
         * WHERE sku LIKE 'PAGE-R50%';
         * -- Expect: 7, the total page 9 reported.
         * ===========================================================
         */
    }
}

What every sample is given: the connection string (and the ISample interface the samples implement).

namespace ArxSamples.MySQL;

/// <summary>One documentation sample: a scenario a real application has, shown end to end.</summary>
public interface ISample
{
    /// <summary>The name to run it alone: <c>dotnet run -- &lt;name&gt;</c>.</summary>
    string Name { get; }

    /// <summary>One line on what it shows.</summary>
    string Title { get; }

    Task RunAsync(SampleContext dbContext);
}

/// <summary>
/// What a sample is given. Every sample gets the connection string; the application's service and catalog
/// (SampleContext.Application.cs) and the plain MySqlConnector read-back helper (SampleContext.Direct.cs) live in their own
/// files, so a sample needs only the files it uses.
/// </summary>
public sealed partial class SampleContext
{
    public SampleContext(string connectionString)
    {
        ConnectionString = connectionString;
    }

    public string ConnectionString { get; }
}

How the samples print: Step for each scenario, Show for what it did, ShowError for an error caught on purpose.

using ARX.SDK;

namespace ArxSamples.MySQL;

/// <summary>How the samples print: a numbered step for each scenario, then what it did.</summary>
public static class SampleOutput
{
    /// <summary>The start of a scenario: <c>Step("1. By the id Create returned")</c>.</summary>
    public static void Step(string text) => Console.WriteLine($"  > {text}");

    /// <summary>What the scenario did.</summary>
    public static void Show(string text) => Console.WriteLine($"      {text}");

    /// <summary>An ARX error the scenario caught on purpose.</summary>
    public static void ShowError(ArxException exception)
        => Console.WriteLine($"      caught {exception.GetType().Name}: {exception.Message}");
}

  > 1. One page
      PAGE-R501, PAGE-R502, PAGE-R503; 7 in all, 3 pages, next page: True
  > 2. Every page
      7 products in 3 pages
  > 3. Past the end
      0 rows, total 7