Remember resolved tables, or preload them

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.Create.AdvancedCases;

// 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 Advanced10_RememberResolvedTables().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

using ARX.SDK.SQL.Services;
using ARX.SDK.Services;

using static ArxSamples.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.Create.AdvancedCases;

/// <summary>
/// The three ways to set up a service, from the least code to the fastest, in three scenarios: without a catalog,
/// reading the tables on every call; without a catalog, remembering what was read (RememberResolvedTables); and the
/// tables preloaded into a catalog once, at startup.
/// </summary>
/// <remarks>
/// <para><b>Re-read (the default).</b> Every call reads its tables from the database first: always current, one more
/// round trip per call. Measured on PostgreSQL, a create or an update by id took 5–7 times as long as with a catalog
/// (the benchmark project's NoCatalog results).</para>
/// <para><b>Remembered.</b> <c>RememberResolvedTables = true</c>: each table is read the first time a call needs it,
/// then kept for the service's lifetime, so later calls were level with a catalog. A schema change after that needs a
/// new service; keep one service for the application (a singleton) so the tables are read once.</para>
/// <para><b>Preloaded.</b> <c>ArxPreLoadTable</c> reads the tables once, at startup, into a catalog: no call reads
/// the database's catalog, and a table left out is refused (ARX1016) instead of resolved. It is the setup for an
/// application that grows past simple CRUD (Create Advanced6 shows its options).</para>
/// </remarks>
public sealed class Advanced10_RememberResolvedTables : ISample
{
    public string Name => "create-advanced-10-remember-resolved-tables";

    public string Title => "Remember resolved tables, or preload them";

    public async Task RunAsync(SampleContext dbContext)
    {
        await Scenario1_ReReadOnEveryCallAsync(dbContext.ConnectionString);

        await Scenario2_RememberWhatWasReadAsync(dbContext.ConnectionString);

        await Scenario3_PreloadACatalogAtStartupAsync(dbContext.ConnectionString);
    }

    private static ArxEntity Account(string name, string number)
    {
        var account = new ArxEntity("account");
        account["name"] = name;
        account["account_number"] = number;
        return account;
    }

    /// <summary>1. The default: both creates read the account table first, so each sees the table as it is now.</summary>
    private static async Task Scenario1_ReReadOnEveryCallAsync(string connectionString)
    {
        Step("1. Re-read on every call (the default)");

        var service = new ArxMsSqlDbService(connectionString);

        var first = await service.CreateAsync(Account("Re-read 1", "ACC-N081"));
        var second = await service.CreateAsync(Account("Re-read 2", "ACC-N082"));

        Show($"created {first} and {second}; the table was read twice");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT account_number, name
         * FROM account
         * WHERE account_number IN ('ACC-N081', 'ACC-N082');
         * -- Expect: 2 rows.
         * ===========================================================
         */
    }

    /// <summary>2. Remembered: the first call reads the account table, the later ones use what it read.</summary>
    private static async Task Scenario2_RememberWhatWasReadAsync(string connectionString)
    {
        Step("2. Remember what was read");

        // One service for the application's lifetime, so the tables are read once.
        var service = new ArxMsSqlDbService(connectionString) { RememberResolvedTables = true };

        var id = await service.CreateAsync(Account("Remembered", "ACC-N083"));

        var change = new ArxEntity("account");
        change.RecordId = id;
        change["employee_count"] = 12;
        await service.UpdateAsync(change);

        var read = await service.RetrieveAsync("account", id, new ArxColumnSet("name", "employee_count")) ?? throw new InvalidOperationException("The account record isn't there.");

        Show($"{read["name"]}, {read["employee_count"]} employees; the table was read once, by the create");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, employee_count
         * FROM account
         * WHERE account_number = 'ACC-N083';
         * -- Expect: 1 row, Remembered, 12.
         * ===========================================================
         */
    }

    /// <summary>3. Preloaded: the tables read once, at startup, into a catalog the service is created with.</summary>
    private static async Task Scenario3_PreloadACatalogAtStartupAsync(string connectionString)
    {
        Step("3. Preload a catalog at startup");

        var tables = new ArxPreLoadTableRegister()
            .PreLoadTable("account");

        ArxSchemaCatalog schema = await new ArxPreLoadTable(tables).LoadAsync(connectionString);

        var service = new ArxMsSqlDbService(connectionString, schema);

        var id = await service.CreateAsync(Account("Preloaded", "ACC-N084"));

        Show($"created {id}; no call reads the database's catalog");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT account_number, name
         * FROM account
         * WHERE account_number = 'ACC-N084';
         * -- Expect: 1 row, Preloaded.
         * ===========================================================
         */
    }
}

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

namespace ArxSamples.MSSQL;

/// <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 SqlClient 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.MSSQL;

/// <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. Re-read on every call (the default)
      created 1 and 2; the table was read twice
  > 2. Remember what was read
      Remembered, 12 employees; the table was read once, by the create
  > 3. Preload a catalog at startup
      created 4; no call reads the database's catalog