Every supported .NET type, and what SQL Server stores

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

using ArxSamples.MSSQL;
using ArxSamples.MSSQL.Operations.Create.RealCases;

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

The sample, as it is in the repository.

using ARX.SDK;
using ARX.SDK.Models;
using ARX.SDK.Services;
using static ArxSamples.MSSQL.SampleOutput;

namespace ArxSamples.MSSQL.Operations.Create.RealCases;

/// <summary>
/// Every kind of value the SQL Server provider writes, into data_type_showcase, and what SQL Server stored, in four
/// scenarios: every supported type, types converted to one that holds every value, null, and a type that's refused.
/// The comment on each line is the stored value as SQL Server prints it.
/// </summary>
public sealed class RealCase2_EveryDataType : ISample
{
    public string Name => "create-real-2-every-data-type";

    public string Title => "Every supported .NET type, and what SQL Server stores";

    public async Task RunAsync(SampleContext dbContext)
    {
        // The account the showcase row points at.
        var owner = new ArxEntity("account");
        owner["name"] = "Showcase owner";
        var ownerId = await dbContext.Service.CreateAsync(owner);

        await Scenario1_EverySupportedTypeAsync(dbContext, ownerId);

        await Scenario2_TypesThatAreConvertedAsync(dbContext);

        await Scenario3_NullIsStoredAsNullAsync(dbContext);

        await Scenario4_ATypeThatIsRefusedAsync(dbContext);
    }

    /// <summary>1. One row with a value of every supported kind.</summary>
    private static async Task Scenario1_EverySupportedTypeAsync(SampleContext dbContext, ArxRecordId ownerId)
    {
        Step("1. One row with a value of every supported kind");

        var row = new ArxEntity("data_type_showcase");
        row["text_value"] = "Hello, ARX";                                                        // Hello, ARX
        row["varchar_value"] = "Short text";                                                     // Short text
        row["char_value"] = "AB";                                                                // AB (nchar(3) pads to "AB ")
        row["tinyint_value"] = (byte)200;                                                        // 200
        row["smallint_value"] = (short)32_000;                                                   // 32000
        row["int_value"] = 2_000_000_000;                                                        // 2000000000
        row["bigint_value"] = 9_000_000_000_000L;                                                // 9000000000000
        row["decimal_value"] = 12_345.678901m;                                                   // 12345.678901
        row["money_value"] = 19.99m;                                                             // 19.99
        row["real_value"] = 3.25f;                                                               // 3.25
        row["float_value"] = 2.718281828459045;                                                  // 2.718281828459045
        row["bit_value"] = true;                                                                 // 1
        row["uniqueidentifier_value"] = Guid.Parse("6f9619ff-8b86-d011-b42d-00c04fc964ff");      // 6F9619FF-8B86-D011-B42D-00C04FC964FF
        row["datetimeoffset_value"] = new DateTimeOffset(2026, 11, 7, 22, 30, 0, TimeSpan.FromHours(8)); // 2026-11-07T22:30:00+08:00 (offset kept)
        row["datetime2_value"] = new DateTime(2026, 11, 7, 9, 0, 0, DateTimeKind.Unspecified);   // 2026-11-07T09:00:00
        row["datetime_value"] = new DateTime(2026, 11, 7, 9, 0, 0, 1);                           // 2026-11-07T09:00:00 (datetime rounds to 1/300 s)
        row["date_value"] = new DateOnly(2026, 11, 7);                                           // 2026-11-07
        row["time_value"] = new TimeOnly(17, 45, 30);                                            // 17:45:30
        row["varbinary_value"] = new byte[] { 0xCA, 0xFE };                                      // 0xCAFE
        row["json_value"] = new ArxJsonB { SerializedValue = "{\"plan\": \"gold\", \"seats\": 25}" };  // {"plan": "gold", "seats": 25} (text, checked by ISJSON)
        row["mood"] = new ArxSelectionSetValue("Mood", "happy");                                 // happy (text the CHECK allows)
        row["account_id"] = new ArxEntityReference("account", ownerId);                          // the account's id
        row["industry"] = new ArxSelectionSetValue("Industry", Industry.Technology);             // 5

        await dbContext.Service.CreateAsync(row);
        await ShowStoredAsync(dbContext, row);

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT text_value, char_value, tinyint_value, smallint_value, int_value, bigint_value, decimal_value, money_value,
         *        real_value, float_value, bit_value, uniqueidentifier_value, datetimeoffset_value, datetime2_value,
         *        datetime_value, date_value, time_value, varbinary_value, json_value, mood, industry
         * FROM data_type_showcase
         * WHERE text_value = 'Hello, ARX';
         * -- Expect: 1 row with the values in the comments next to each attribute.
         * ===========================================================
         */
    }

    /// <summary>2. Types without a SQL Server type of their own are converted to one that holds every value.</summary>
    private static async Task Scenario2_TypesThatAreConvertedAsync(SampleContext dbContext)
    {
        Step("2. Types without a SQL Server type of their own are converted to one that holds every value");

        var converted = new ArxEntity("data_type_showcase");
        converted["smallint_value"] = (sbyte)-100;                                               // -100 (sbyte → smallint)
        converted["int_value"] = (ushort)65_000;                                                 // 65000 (ushort → int)
        converted["bigint_value"] = 4_000_000_000u;                                              // 4000000000 (uint → bigint)
        converted["decimal_value"] = ulong.MaxValue;                                             // 18446744073709551615.000000 (ulong → decimal)
        converted["char_value"] = 'X';                                                           // X (char → text)
        converted["text_value"] = Industry.Finance.ToString();                                   // Finance (an enum: its name, or (int) for its number)
        converted["datetimeoffset_value"] = new DateTime(2026, 11, 7, 14, 30, 0, DateTimeKind.Utc); // 2026-11-07T14:30:00Z (a UTC DateTime: offset +00:00)
        converted["varbinary_value"] = new ArxBytea { Data = new byte[] { 1, 2 } };              // 0x0102

        await dbContext.Service.CreateAsync(converted);
        await ShowStoredAsync(dbContext, converted);

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT smallint_value, int_value, bigint_value, decimal_value, char_value, text_value, datetimeoffset_value, varbinary_value
         * FROM data_type_showcase
         * WHERE text_value = 'Finance';
         * -- Expect: 1 row: -100, 65000, 4000000000, 18446744073709551615.000000, X, Finance, 2026-11-07 14:30:00.0000000 +00:00, 0x0102.
         * ===========================================================
         */
    }

    /// <summary>3. null is stored as NULL, whatever the column's type.</summary>
    private static async Task Scenario3_NullIsStoredAsNullAsync(SampleContext dbContext)
    {
        Step("3. null is stored as NULL");

        var empty = new ArxEntity("data_type_showcase");
        empty["text_value"] = null;
        empty["int_value"] = null;
        empty["json_value"] = null;

        await dbContext.Service.CreateAsync(empty);
        await ShowStoredAsync(dbContext, empty);

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT id, text_value, int_value, json_value
         * FROM data_type_showcase
         * WHERE text_value IS NULL AND int_value IS NULL AND json_value IS NULL
         *   AND time_value IS NULL AND mood IS NULL AND uniqueidentifier_value IS NULL;
         * -- Expect: 1 row, all NULL.
         * ===========================================================
         */
    }

    /// <summary>4. A type the provider can't write is refused before anything is sent.</summary>
    private static async Task Scenario4_ATypeThatIsRefusedAsync(SampleContext dbContext)
    {
        Step("4. A type the provider can't write is refused before anything is sent");

        var unsupported = new ArxEntity("data_type_showcase");
        unsupported["text_value"] = new Uri("https://northwind.example");

        try
        {
            await dbContext.Service.CreateAsync(unsupported);
        }
        catch (ArxValidationException error) when (error.Code == ArxErrorCode.UnsupportedValueType)
        {
            ShowError(error);
            Show("write uri.ToString() instead");
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT count(*)
         * FROM data_type_showcase
         * WHERE text_value LIKE 'https://northwind%';
         * -- Expect: 0: the Uri was refused before anything was sent.
         * ===========================================================
         */
    }

    /// <summary>Prints each attribute's .NET type and the value SQL Server stored for it.</summary>
    private static async Task ShowStoredAsync(SampleContext dbContext, ArxEntity entity)
    {
        var columns = entity.Attributes.Keys.ToArray();
        var stored = await dbContext.Direct.TextRowAsync("data_type_showcase", entity.RecordId!.AsInt64(), columns);

        foreach (var column in columns)
        {
            Show($"{column,-18} {entity[column]?.GetType().Name ?? "null",-22} {stored[column] ?? "NULL"}");
        }
    }
}

  > 1. One row with a value of every supported kind
      text_value         String                 Hello, ARX
      varchar_value      String                 Short text
      char_value         String                 AB 
      tinyint_value      Byte                   200
      smallint_value     Int16                  32000
      int_value          Int32                  2000000000
      bigint_value       Int64                  9000000000000
      decimal_value      Decimal                12345.678901
      money_value        Decimal                19.9900
      real_value         Single                 3.2500000e+000
      float_value        Double                 2.718281828459045e+000
      bit_value          Boolean                1
      uniqueidentifier_value Guid                   6F9619FF-8B86-D011-B42D-00C04FC964FF
      datetimeoffset_value DateTimeOffset         2026-11-07T14:30:00+00:00
      datetime2_value    DateTime               2026-11-07T09:00:00
      datetime_value     DateTime               2026-11-07T09:00:00
      date_value         DateOnly               2026-11-07
      time_value         TimeOnly               17:45:30
      varbinary_value    Byte[]                 ﻊ
      json_value         ArxJsonB               {"plan": "gold", "seats": 25}
      mood               ArxSelectionSetValue   happy
      account_id         ArxEntityReference     1
      industry           ArxSelectionSetValue   5
  > 2. Types without a SQL Server type of their own are converted to one that holds every value
      smallint_value     SByte                  -100
      int_value          UInt16                 65000
      bigint_value       UInt32                 4000000000
      decimal_value      UInt64                 18446744073709551615.000000
      char_value         Char                   X  
      text_value         String                 Finance
      datetimeoffset_value DateTime               2026-11-07T14:30:00+00:00
      varbinary_value    ArxBytea               ȁ
  > 3. null is stored as NULL
      text_value         null                   NULL
      int_value          null                   NULL
      json_value         null                   NULL
  > 4. A type the provider can't write is refused before anything is sent
      caught ArxValidationException: ARX1007 UnsupportedValueType: Attribute 'text_value' on 'data_type_showcase' has a value of type System.Uri, which the SQL Server provider cannot write.
      write uri.ToString() instead