C# / .NET: Microsoft.Data.SqlClient (SQL Server / TDS)

Connects over TDS to a MachSQL database created with the T-SQL dialect; uses OBJECT_ID, IDENTITY and OUTPUT INSERTED. The code is the same against the original database and against MachSQL; only the address changes.

The only change

DB_CONNECTION="Server=old-sqlserver,1433;Database=app;User ID=app;Password=...;Encrypt=True"
DB_CONNECTION="Server=machsql-host,1433;Database=app;User ID=app;Password=...;Encrypt=True"

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

Versions

• .NET 10
• Microsoft.Data.SqlClient 7.1.1

Run

export DB_CONNECTION="Server=machsql-host,1433;Database=app;User ID=app;Password=...;Encrypt=True"
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.

Program.cs

// Microsoft.Data.SqlClient over TDS (the SQL Server protocol).
using Microsoft.Data.SqlClient;

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

await using (var create = new SqlCommand("IF OBJECT_ID(N'dbo.todo', N'U') IS NULL CREATE TABLE dbo.todo (id BIGINT IDENTITY PRIMARY KEY, title NVARCHAR(200) NOT NULL, done BIT NOT NULL DEFAULT 0)", conn))
{
    await create.ExecuteNonQueryAsync();
}

await using var insert = new SqlCommand("INSERT INTO dbo.todo (title) OUTPUT INSERTED.id VALUES (@title)", conn);
insert.Parameters.AddWithValue("@title", "write the examples");
var id = (long)(await insert.ExecuteScalarAsync())!;

await using var count = new SqlCommand("SELECT count(*) FROM dbo.todo WHERE done = 0", conn);
var open = (int)(await count.ExecuteScalarAsync())!;

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

Example.csproj

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

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

  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="7.1.1" />
  </ItemGroup>

</Project>