C# / .NET: MySqlConnector (MySQL)

Connects, creates a todo table if it does not exist, inserts a row and counts open rows. The code is the same against the original database and against MachSQL; only the address changes.

The only change

DB_CONNECTION="Server=old-mysql;Port=3306;Database=app;User ID=app;Password=..."
DB_CONNECTION="Server=machsql-host;Port=3306;Database=app;User ID=app;Password=..."

Nothing else changes: not the code, not the driver, not its version, not the SQL.

Versions

.NET 10
MySqlConnector 2.6.2

Run

export DB_CONNECTION="Server=machsql-host;Port=3306;Database=app;User ID=app;Password=..."
dotnet run

Expected output: inserted todo <id>, <n> open.

Source

The program as it is kept and tested in the Mach Database for PostgreSQL repository; highlighted lines read the connection setting.

// MySqlConnector: ADO.NET over the MySQL protocol.
using MySqlConnector;

var connectionString = Environment.GetEnvironmentVariable("DB_CONNECTION")
    ?? throw new InvalidOperationException("set DB_CONNECTION");
await using var conn = new MySqlConnection(connectionString);
await conn.OpenAsync();

await using (var create = new MySqlCommand("CREATE TABLE IF NOT EXISTS todo (id BIGINT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(200) NOT NULL, done BOOLEAN NOT NULL DEFAULT FALSE)", conn))
{
    await create.ExecuteNonQueryAsync();
}

await using var insert = new MySqlCommand("INSERT INTO todo (title) VALUES (@title)", conn);
insert.Parameters.AddWithValue("@title", "write the examples");
await insert.ExecuteNonQueryAsync();
var id = insert.LastInsertedId;

await using var count = new MySqlCommand("SELECT count(*) FROM todo WHERE NOT done", conn);
var open = Convert.ToInt64(await count.ExecuteScalarAsync());

Console.WriteLine($"inserted todo {id}, {open} open");

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net10.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="MySqlConnector" Version="2.6.2" />
  </ItemGroup>

</Project>