Update many records in one call

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.UpdateMultiple.Basics;

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

The sample, as it is in the repository.

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

using static ArxSamples.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.UpdateMultiple.Basics;

/// <summary>
/// Many records in one call, in three scenarios: a different value for each row, rows that change different attributes,
/// and all or nothing when one record doesn't exist.
/// </summary>
public sealed class Basic1_UpdateManyRecords : ISample
{
    public string Name => "update-multiple-basics-1-update-many-records";

    public string Title => "Update many records in one call";

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

        await Scenario2_RowsThatChangeDifferentAttributesAsync(dbContext.ConnectionString);

        await Scenario3_AllOrNothingAsync(dbContext.ConnectionString);
    }

    private static ArxMsSqlDbService Service(string connectionString) => new ArxMsSqlDbService(connectionString, ArxSchemaCatalog.Create()
        .Table("account", ArxIdColumn.Generated<long>("id"))
        .Build());

    /// <summary>Creates accounts ACC-U2nn and returns their ids, in order.</summary>
    private static async Task<IReadOnlyList<ArxRecordId>> CreateAccountsAsync(ArxMsSqlDbService service, int first, int count)
    {
        var accounts = new List<ArxEntity>();

        for (var i = 0; i < count; i++)
        {
            var account = new ArxEntity("account");
            account["name"] = $"Basic update {first + i}";
            account["account_number"] = $"ACC-U2{first + i:00}";
            account["employee_count"] = 10;
            accounts.Add(account);
        }

        return await service.CreateMultipleAsync(accounts);
    }

    /// <summary>1. The usual way: one entity per record, each with its own value. The call returns the rows changed.</summary>
    private static async Task Scenario1_ADifferentValuePerRowAsync(string connectionString)
    {
        Step("1. A value per row");

        var service = Service(connectionString);
        var ids = await CreateAccountsAsync(service, 1, 5);

        var changes = new List<ArxEntity>();

        for (var i = 0; i < ids.Count; i++)
        {
            var change = new ArxEntity("account");
            change.RecordId = ids[i];
            change["employee_count"] = 100 + i;
            changes.Add(change);
        }

        int changed = await service.UpdateMultipleAsync(changes);

        Show($"{changed} accounts changed in one call");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT account_number, employee_count
         * FROM account
         * WHERE account_number BETWEEN 'ACC-U201' AND 'ACC-U205'
         * ORDER BY account_number;
         * -- Expect: 5 rows, employee_count 100, 101, 102, 103, 104.
         * ===========================================================
         */
    }

    /// <summary>2. Rows don't have to change the same attributes: each one writes only what it sets.</summary>
    private static async Task Scenario2_RowsThatChangeDifferentAttributesAsync(string connectionString)
    {
        Step("2. Different attributes");

        var service = Service(connectionString);
        var ids = await CreateAccountsAsync(service, 11, 2);

        var renamed = new ArxEntity("account");
        renamed.RecordId = ids[0];
        renamed["name"] = "Basic update 11 (renamed)";

        var resized = new ArxEntity("account");
        resized.RecordId = ids[1];
        resized["employee_count"] = 999;

        await service.UpdateMultipleAsync(new[] { renamed, resized });

        Show("one renamed, one resized");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT account_number, name, employee_count
         * FROM account
         * WHERE account_number IN ('ACC-U211', 'ACC-U212')
         * ORDER BY account_number;
         * -- Expect: ACC-U211 renamed with employee_count 10; ACC-U212 still named Basic update 12, employee_count 999.
         * ===========================================================
         */
    }

    /// <summary>3. All or nothing: if one record doesn't exist, no record changes, and the error says how many were missing.</summary>
    private static async Task Scenario3_AllOrNothingAsync(string connectionString)
    {
        Step("3. All or nothing");

        var service = Service(connectionString);
        var ids = await CreateAccountsAsync(service, 21, 3);

        var changes = ids.Select(id =>
        {
            var change = new ArxEntity("account");
            change.RecordId = id;
            change["employee_count"] = 7;
            return change;
        }).ToList();

        // An id no account has: maybe the account was deleted in the meantime.
        var missing = new ArxEntity("account");
        missing["id"] = 999_999_999L;
        missing["employee_count"] = 7;
        changes.Add(missing);

        try
        {
            await service.UpdateMultipleAsync(changes);
        }
        catch (ArxException error) when (error.Code == ArxErrorCode.RecordNotFound)
        {
            Show($"{error.Message}");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE account_number BETWEEN 'ACC-U221' AND 'ACC-U223' AND employee_count = 7;
         * -- Expect: 0: the three accounts that exist were not changed either.
         * ===========================================================
         */
    }
}

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. A value per row
      5 accounts changed in one call
  > 2. Different attributes
      one renamed, one resized
  > 3. All or nothing
      ARX2002 RecordNotFound: UpdateMultiple on 'account': 1 of 4 entities matched no row. Nothing was changed.