Create one record and get its id

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

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

/// <summary>
/// The smallest create: one record, in four scenarios. Read them in order: the usual way first (the id column declared
/// in the schema), then the other way (declared on the entity), then two tables with no id column: one identified by a
/// compound key, one with no key at all.
/// </summary>
public sealed class Basic1_CreateOneRecord : ISample
{
    public string Name => "create-basics-1-create-one-record";

    public string Title => "Create one record and get its id";

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

        await Scenario2_IdColumnOnTheEntityAsync(dbContext.ConnectionString);

        await Scenario3_NoIdColumn_ACompoundKeyAsync(dbContext.ConnectionString);

        await Scenario4_NoIdColumn_NoKeyAsync(dbContext.ConnectionString);
    }

    /// <summary>1. The usual way: the id column declared in the schema, the id read from what CreateAsync returns.</summary>
    private static async Task Scenario1_IdColumnInTheSchemaAsync(string connectionString)
    {
        Step("1. Id column in the schema");

        // Declare each table's id column once, at startup. ARX never guesses it.
        var schema = ArxSchemaCatalog.Create()
            .Table("account", ArxIdColumn.Generated<long>("id"))
            .Build();

        var service = new ArxMsSqlDbService(connectionString, schema);

        var entity = new ArxEntity("account");
        entity["name"] = "Northwind Traders";
        entity["account_number"] = "ACC-0001";
        entity["employee_count"] = 240;

        // CreateAsync returns the id of the new record.
        ArxRecordId id = await service.CreateAsync(entity);

        // The account's id is a bigint column, so read it as a number.
        long accountId = id.AsInt64();

        Show($"created account {accountId}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT id, name, account_number, employee_count
         * FROM account
         * WHERE account_number = 'ACC-0001';
         * -- Expect: 1 row, Northwind Traders, employee_count 240.
         * ===========================================================
         */
    }

    /// <summary>2. The other way: the id column declared on the entity, the id read from the entity.</summary>
    private static async Task Scenario2_IdColumnOnTheEntityAsync(string connectionString)
    {
        Step("2. Id column on the entity");

        // No schema catalog: the entity says what its id column is, for this record only.
        var service = new ArxMsSqlDbService(connectionString);

        var entity = new ArxEntity("account");
        entity.IdColumn = ArxIdColumn.Generated<long>("id");
        entity["name"] = "Contoso Pharmaceuticals";
        entity["account_number"] = "ACC-0006";
        entity["employee_count"] = 85;

        await service.CreateAsync(entity);

        // The id is also set on the entity, as entity.RecordId.
        Show($"created account {entity.RecordId} (read from entity.RecordId)");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT id, name, account_number, employee_count
         * FROM account
         * WHERE account_number = 'ACC-0006';
         * -- Expect: 1 row, Contoso Pharmaceuticals, employee_count 85.
         * ===========================================================
         */
    }

    /// <summary>3. A table with no id column, where a compound key (region + sku) identifies each record.</summary>
    private static async Task Scenario3_NoIdColumn_ACompoundKeyAsync(string connectionString)
    {
        Step("3. Compound key");

        var schema = ArxSchemaCatalog.Create()
            .Table("country", ArxIdColumn.Supplied<string>("code"))
            .Table("price_list", ArxIdColumn.NoIdColumn).Key("price_list", "region", "sku")
            .Build();

        var service = new ArxMsSqlDbService(connectionString, schema);

        // A price's region must be a country that exists, so add the country first.
        var italy = new ArxEntity("country");
        italy["code"] = "IT";
        italy["name"] = "Italy";
        await service.CreateAsync(italy);

        var price = new ArxEntity("price_list");
        price["region"] = "IT";
        price["sku"] = "SKU-000001";
        price["unit_price"] = 9.50m;
        price["valid_from"] = new DateOnly(2026, 1, 1);

        // There is no id column, so there is no id to return: ArxRecordId.None. The region and SKU identify the record.
        ArxRecordId id = await service.CreateAsync(price);

        Show($"created price region {price["region"]}, sku {price["sku"]} (id {id})");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT p.region, c.name AS country, p.sku, p.unit_price, p.valid_from
         * FROM price_list p
         * JOIN country c ON c.code = p.region
         * WHERE p.region = 'IT' AND p.sku = 'SKU-000001';
         * -- Expect: 1 row, Italy, unit_price 9.50.
         * ===========================================================
         */
    }

    /// <summary>4. A table with no id column and no key: rows may repeat, like a log.</summary>
    private static async Task Scenario4_NoIdColumn_NoKeyAsync(string connectionString)
    {
        Step("4. No key");

        var schema = ArxSchemaCatalog.Create()
            .Table("activity_log", ArxIdColumn.NoIdColumn)
            .Build();

        var service = new ArxMsSqlDbService(connectionString, schema);

        var entry = new ArxEntity("activity_log");
        entry["action"] = "signed in";

        ArxRecordId id = await service.CreateAsync(entry);

        Show($"logged \"{entry["action"]}\" (id {id})");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT action, logged_at
         * FROM activity_log
         * WHERE action = 'signed in';
         * -- Expect: 1 row (the table has no id column).
         * ===========================================================
         */
    }
}

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. Id column in the schema
      created account 1
  > 2. Id column on the entity
      created account 2 (read from entity.RecordId)
  > 3. Compound key
      created price region IT, sku SKU-000001 (id (none))
  > 4. No key
      logged "signed in" (id (none))