Choose the columns to read

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.Retrieve.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 Basic2_ChooseTheColumns().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.Retrieve.Basics;

/// <summary>
/// Which columns come back, in three scenarios: the ones you ask for (plus the id); all of them; and how an empty value
/// differs from a NULL one.
/// </summary>
public sealed class Basic2_ChooseTheColumns : ISample
{
    public string Name => "retrieve-basics-2-choose-the-columns";

    public string Title => "Choose the columns to read";

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

        var account = new ArxEntity("account");
        account["name"] = "Columns Ltd";
        account["account_number"] = "ACC-R301";
        account["website"] = "";
        ArxRecordId id = await service.CreateAsync(account);

        await Scenario1_SomeColumnsAsync(service, id);

        await Scenario2_AllColumnsAsync(service, id);

        await Scenario3_EmptyIsNotNullAsync(service, id);
    }

    /// <summary>1. The columns asked for; the id comes too, so the entity can be updated or deleted later.</summary>
    private static async Task Scenario1_SomeColumnsAsync(ArxMsSqlDbService service, ArxRecordId id)
    {
        Step("1. Some columns");

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

        Show($"{string.Join(", ", read.Attributes.Select(x => x.Key))}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT id, name, account_number
         * FROM account
         * WHERE account_number = 'ACC-R301';
         * -- Expect: 1 row, Columns Ltd: the three attributes Retrieve returned.
         * ===========================================================
         */
    }

    /// <summary>2. No column set (or <c>ArxColumnSet.All</c>): every column of the table.</summary>
    private static async Task Scenario2_AllColumnsAsync(ArxMsSqlDbService service, ArxRecordId id)
    {
        Step("2. All columns");

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

        Show($"{read.Attributes.Count} attributes, status {read["status"]}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM information_schema.columns
         * WHERE table_name = 'account';
         * -- Expect: 17, the number of attributes Retrieve returned.
         * ===========================================================
         */
    }

    /// <summary>3. An empty website is "", a missing one is null, and both are attributes: nothing is dropped.</summary>
    private static async Task Scenario3_EmptyIsNotNullAsync(ArxMsSqlDbService service, ArxRecordId id)
    {
        Step("3. Empty and NULL");

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

        Show($"website \"{read["website"]}\", annual_revenue {(read["annual_revenue"] == null ? "null" : read["annual_revenue"])}, both present: {read.Contains("website") && read.Contains("annual_revenue")}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT CASE WHEN website = '' THEN 1 ELSE 0 END AS empty_website,
         *        CASE WHEN annual_revenue IS NULL THEN 1 ELSE 0 END AS no_revenue
         * FROM account
         * WHERE account_number = 'ACC-R301';
         * -- Expect: 1 row, 1, 1.
         * ===========================================================
         */
    }
}

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. Some columns
      name, account_number, id
  > 2. All columns
      17 attributes, status ACTIVE
  > 3. Empty and NULL
      website "", annual_revenue null, both present: True