Retrieve many records with a query

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.RetrieveMultiple.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_RetrieveManyRecords().RunAsync(new SampleContext(connectionString));

The sample, as it is in the repository.

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

using static ArxSamples.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.RetrieveMultiple.Basics;

/// <summary>
/// Many records with a query, in three scenarios: a condition and an order; several conditions together; and how many
/// rows came back. Every value in a condition is sent as a parameter, never written into the SQL.
/// </summary>
public sealed class Basic1_RetrieveManyRecords : ISample
{
    public string Name => "retrieve-multiple-basics-1-retrieve-many-records";

    public string Title => "Retrieve many records with a query";

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

        var accounts = new List<ArxEntity>();

        foreach (var (number, name, employees) in new[] { ("ACC-R201", "Basic Bikes", 12), ("ACC-R202", "Basic Boats", 340), ("ACC-R203", "Basic Books", 45) })
        {
            var account = new ArxEntity("account");
            account["name"] = name;
            account["account_number"] = number;
            account["employee_count"] = employees;
            accounts.Add(account);
        }

        await service.CreateMultipleAsync(accounts);

        await Scenario1_AConditionAndAnOrderAsync(service);

        await Scenario2_SeveralConditionsAsync(service);

        await Scenario3_HowManyCameBackAsync(service);
    }

    /// <summary>1. The accounts whose name begins with "Basic B", by name.</summary>
    private static async Task Scenario1_AConditionAndAnOrderAsync(ArxMsSqlDbService service)
    {
        Step("1. A condition and an order");

        var query = new ArxQueryExpression("account");
        query.ColumnSet.AddColumns("name", "employee_count");
        query.Criteria.AddCondition("name", ArxConditionOperator.BeginsWith, "Basic B");
        query.AddOrder("name", ArxOrderType.Ascending);

        ArxEntityCollection result = await service.RetrieveMultipleAsync(query);

        Show($"{string.Join(", ", result.Entities.Select(x => x["name"]))}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name
         * FROM account
         * WHERE name LIKE 'Basic B%'
         * ORDER BY name;
         * -- Expect: 3 rows, Basic Bikes, Basic Boats, Basic Books.
         * ===========================================================
         */
    }

    /// <summary>2. Conditions in the criteria are joined with AND: "Basic" accounts with 40 employees or more.</summary>
    private static async Task Scenario2_SeveralConditionsAsync(ArxMsSqlDbService service)
    {
        Step("2. Several conditions");

        var query = new ArxQueryExpression("account");
        query.ColumnSet.AddColumns("name", "employee_count");
        query.Criteria.AddCondition("name", ArxConditionOperator.BeginsWith, "Basic B");
        query.Criteria.AddCondition("employee_count", ArxConditionOperator.GreaterEqual, 40);
        query.AddOrder("employee_count", ArxOrderType.Descending);

        var result = await service.RetrieveMultipleAsync(query);

        Show($"{string.Join(", ", result.Entities.Select(x => $"{x["name"]} ({x["employee_count"]})"))}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT name, employee_count
         * FROM account
         * WHERE name LIKE 'Basic B%' AND employee_count >= 40
         * ORDER BY employee_count DESC;
         * -- Expect: 2 rows, Basic Boats 340, Basic Books 45.
         * ===========================================================
         */
    }

    /// <summary>3. Without paging, TotalRecordCount is the number of rows that came back.</summary>
    private static async Task Scenario3_HowManyCameBackAsync(ArxMsSqlDbService service)
    {
        Step("3. How many came back");

        var query = new ArxQueryExpression("account");
        query.ColumnSet.AddColumn("name");
        query.Criteria.AddCondition("account_number", ArxConditionOperator.In, "ACC-R201", "ACC-R203", "ACC-R999");

        var result = await service.RetrieveMultipleAsync(query);

        Show($"{result.TotalRecordCount} of the 3 numbers asked for");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM account
         * WHERE account_number IN ('ACC-R201', 'ACC-R203', 'ACC-R999');
         * -- Expect: 2 (nobody has ACC-R999).
         * ===========================================================
         */
    }
}

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 condition and an order
      Basic Bikes, Basic Boats, Basic Books
  > 2. Several conditions
      Basic Boats (340), Basic Books (45)
  > 3. How many came back
      2 of the 3 numbers asked for