Access SQL Server from a .NET Console Application

Not every database program is a web app. Import jobs, scheduled tasks, one-off fixes, and diagnostics tools are console applications — and talking to SQL Server from one takes exactly one NuGet package and a handful of types you'll reuse for a career: SqlConnection, SqlCommand, and SqlDataReader. This walkthrough builds a complete .NET 10 console app against SQL Server, with every output captured from a real run.

Access SQL Server from a .NET Console Application

Setup

dotnet new console -n SqlConsoleApp
cd SqlConsoleApp
dotnet add package Microsoft.Data.SqlClient

Use Microsoft.Data.SqlClient — the actively developed driver. The old System.Data.SqlClient is in maintenance mode; the namespaces are drop-in compatible, so there's no reason to start anything new on it.

No SQL Server handy? Docker gives you one in a minute (Azure SQL Edge runs natively on ARM Macs too):

docker run -d --name sql-edge -p 1433:1433 \
  -e ACCEPT_EULA=1 -e MSSQL_SA_PASSWORD='Strong@Password123' \
  mcr.microsoft.com/azure-sql-edge

Connecting

using Microsoft.Data.SqlClient;

var connectionString = Environment.GetEnvironmentVariable("SQL_CONN")
    ?? "Server=localhost,1433;Database=master;User Id=sa;Password=...;TrustServerCertificate=True";

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();
Console.WriteLine($"Connected to: {connection.DataSource}, database: {connection.Database}");

Three habits in six lines:

The connection string comes from the environment, with a local fallback. Secrets never belong in source — in production this arrives from a secret store.
await using disposes the connection even when exceptions fly. A leaked connection isn't closed until the GC gets around to it, and under load that exhausts the pool.
Async everywhere. Console apps can block without hurting a thread pool, but building the async habit means the same code moves into services untouched.

TrustServerCertificate=True is for local containers with self-signed certs only; production connections should validate real certificates.

Executing commands

Create a table and seed it (idempotently, so reruns are safe):

await using (var setup = new SqlCommand("""
    if object_id('dbo.Products') is null
    begin
        create table dbo.Products (Id int identity primary key,
            Name nvarchar(120) not null, Price decimal(10,2) not null);
        insert into dbo.Products (Name, Price)
        values (N'Mechanical keyboard', 89.00), (N'4K monitor', 329.99), (N'USB-C dock', 149.50);
    end
    """, connection))
{
    await setup.ExecuteNonQueryAsync();
}

ExecuteNonQueryAsync is for statements that return no rows (DDL, INSERT, UPDATE, DELETE); it returns the affected-row count.

Reading rows — with parameters, always

await using var command = new SqlCommand(
    "select Id, Name, Price from dbo.Products where Price >= @minPrice order by Price desc",
    connection);
command.Parameters.Add("@minPrice", System.Data.SqlDbType.Decimal).Value = 100m;

await using var reader = await command.ExecuteReaderAsync();
while (await reader.ReadAsync())
    Console.WriteLine($"{reader.GetInt32(0)}  {reader.GetString(1),-22} {reader.GetDecimal(2),10:N2}");

Verified output:

Connected to: localhost,1433, database: master
2  4K monitor                 329.99
3  USB-C dock                 149.50
Total products: 3

The non-negotiable rule: values enter SQL as parameters (@minPrice), never string concatenation. Parameters eliminate SQL injection outright and let SQL Server reuse the query plan. Typed Parameters.Add(..., SqlDbType.Decimal) beats AddWithValue, which has to guess types and occasionally guesses wrong (the classic case: AddWithValue sending an nvarchar against a varchar index column, silently killing index usage).

For single values there's ExecuteScalarAsync:

await using var count = new SqlCommand("select count(*) from dbo.Products", connection);
Console.WriteLine($"Total products: {await count.ExecuteScalarAsync()}");

Reader discipline

SqlDataReader is a firehose, not a list — it streams rows over the open connection:

Read what you need and get out; don't hold a reader open while doing slow work per row.
Access columns by ordinal (GetInt32(0)) in hot loops; by name (reader["Name"]) when clarity wins. GetOrdinal("Name") once before the loop gives you both.
Check IsDBNull(i) before typed getters on nullable columns — GetString on a NULL throws.
One open reader per connection at a time (unless you enable MARS, which you usually shouldn't).

Transactions for multi-statement work

When a job makes several related writes, wrap them:

await using var tx = (SqlTransaction)await connection.BeginTransactionAsync();
try
{
    var cmd = new SqlCommand("update dbo.Products set Price = Price * 1.05 where Id = @id", connection, tx);
    cmd.Parameters.AddWithValue("@id", 2);
    await cmd.ExecuteNonQueryAsync();
    await tx.CommitAsync();
}
catch
{
    await tx.RollbackAsync();
    throw;
}

Half-applied batch jobs are miserable to clean up; transactions make them impossible.

Where this fits vs. EF Core and Dapper

Raw SqlClient is the floor everything else stands on — Dapper wraps exactly these APIs with automatic mapping, and EF Core adds change tracking and LINQ on top. For a console utility with three queries, raw SqlClient with parameters is perfectly professional; when mapping boilerplate grows, Dapper is one package away; when a domain model emerges, EF Core earns its place. Knowing this layer means the abstractions above it never surprise you.

The complete runnable project is in the companion repository, alongside the SqlBulkCopy bulk-loading sample that continues this scenario.