Every supported .NET type, and what MySQL stores

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

using ArxSamples.MySQL;
using ArxSamples.MySQL.Operations.Create.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_MYSQL")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_MYSQL environment variable to your MySQL 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.MySQL.SampleOutput;

namespace ArxSamples.MySQL.Operations.Create.RealCases;

/// <summary>
/// Every kind of value the MySQL provider writes, into data_type_showcase, and what MySQL 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 MySQL 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 MySQL 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 (CHAR(3) pads it when stored and drops the spaces when read)
        row["tinyint_value"] = (byte)100;                                                        // 100
        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["unsigned_value"] = 4_000_000_000u;                                                  // 4000000000 (INT UNSIGNED)
        row["decimal_value"] = 12_345.678901m;                                                   // 12345.678901
        row["float_value"] = 3.25f;                                                              // 3.25
        row["double_value"] = 2.718281828459045;                                                 // 2.718281828459045
        row["bool_value"] = true;                                                                // 1 (BOOLEAN is TINYINT(1))
        row["uuid_value"] = Guid.Parse("6f9619ff-8b86-d011-b42d-00c04fc964ff");                  // 6f9619ff-8b86-d011-b42d-00c04fc964ff (CHAR(36), lower case)
        row["uuid_binary_value"] = Guid.Parse("6f9619ff-8b86-d011-b42d-00c04fc964ff").ToByteArray(bigEndian: true); // 0x6F9619FF8B86D011B42D00C04FC964FF
        row["timestamp_value"] = new DateTimeOffset(2026, 11, 7, 22, 30, 0, TimeSpan.FromHours(8)); // 2026-11-07 14:30:00.000000 (the instant, stored as UTC)
        row["datetime_value"] = new DateTime(2026, 11, 7, 9, 0, 0, DateTimeKind.Unspecified);    // 2026-11-07 09:00:00.000000
        row["datetime0_value"] = new DateTime(2026, 11, 7, 9, 0, 0, 600);                        // 2026-11-07 09:00:01 (a plain DATETIME rounds to the second)
        row["date_value"] = new DateOnly(2026, 11, 7);                                           // 2026-11-07
        row["time_value"] = new TimeOnly(17, 45, 30);                                            // 17:45:30.000000
        row["blob_value"] = new byte[] { 0xCA, 0xFE };                                           // 0xCAFE
        row["json_value"] = new ArxJsonB { SerializedValue = "{\"plan\": \"gold\", \"seats\": 25}" };  // {"plan": "gold", "seats": 25} (MySQL's own form of the document)
        row["mood"] = new ArxSelectionSetValue("Mood", "happy");                                 // happy (an ENUM label)
        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]
         * -----------------------------------------------------------
         * SET time_zone = '+00:00';
         * SELECT text_value, char_value, tinyint_value, smallint_value, int_value, bigint_value, unsigned_value, decimal_value,
         *        float_value, double_value, bool_value, uuid_value, HEX(uuid_binary_value), timestamp_value, datetime_value,
         *        datetime0_value, date_value, time_value, HEX(blob_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 MySQL 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 MySQL 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["timestamp_value"] = new DateTime(2026, 11, 7, 14, 30, 0, DateTimeKind.Utc);   // 2026-11-07 14:30:00.000000 (a UTC DateTime: the instant)
        converted["blob_value"] = new ArxBytea { Data = new byte[] { 1, 2 } };                   // 0x0102

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

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SET time_zone = '+00:00';
         * SELECT smallint_value, int_value, bigint_value, decimal_value, char_value, text_value, timestamp_value, HEX(blob_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.000000, 0102.
         * ===========================================================
         */
    }

    /// <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 uuid_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 MySQL 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                   100
      smallint_value     Int16                  32000
      int_value          Int32                  2000000000
      bigint_value       Int64                  9000000000000
      unsigned_value     UInt32                 4000000000
      decimal_value      Decimal                12345.678901
      float_value        Single                 3.25
      double_value       Double                 2.718281828459045
      bool_value         Boolean                1
      uuid_value         Guid                   6f9619ff-8b86-d011-b42d-00c04fc964ff
      uuid_binary_value  Byte[]                 0x6F9619FF8B86D011B42D00C04FC964FF
      timestamp_value    DateTimeOffset         2026-11-07 14:30:00.000000
      datetime_value     DateTime               2026-11-07 09:00:00.000000
      datetime0_value    DateTime               2026-11-07 09:00:01
      date_value         DateOnly               2026-11-07
      time_value         TimeOnly               17:45:30.000000
      blob_value         Byte[]                 0xCAFE
      json_value         ArxJsonB               {"plan": "gold", "seats": 25}
      mood               ArxSelectionSetValue   happy
      account_id         ArxEntityReference     1
      industry           ArxSelectionSetValue   5
  > 2. Types without a MySQL 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
      timestamp_value    DateTime               2026-11-07 14:30:00.000000
      blob_value         ArxBytea               0x0102
  > 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 MySQL provider cannot write.
      write uri.ToString() instead