C# SQL Server

last modified October 18, 2026

In this article we show how to program SQL Server databases in C# with the Microsoft.Data.SqlClient provider.

SQL Server

SQL Server is a relational database management system developed by Microsoft. It comes in several editions. The Developer edition is free for development and testing, and the Express edition is free for small production workloads. SQL Server runs on Windows and on Linux.

The easiest way to get a server for the examples is a Docker container.

$ docker run -d --name mssql -e ACCEPT_EULA=Y -e 'MSSQL_SA_PASSWORD=Passw0rd!2026' \
    -p 1433:1433 mcr.microsoft.com/mssql/server:2022-latest
$ docker exec mssql /opt/mssql-tools18/bin/sqlcmd -S localhost -U sa \
    -P 'Passw0rd!2026' -C -Q "CREATE DATABASE testdb"

We start the server and create a testdb database. The sa account and its password are meant only for local experiments. On Windows, we can use SQL Server Express with Windows authentication instead; only the connection string changes.

ADO.NET and Microsoft.Data.SqlClient

ADO.NET is the part of .NET that unifies access to relational databases. Each database has its own provider with a connection, a command, a data reader, and a transaction. Microsoft.Data.SqlClient is the current ADO.NET provider for SQL Server and Azure SQL. It replaces the older System.Data.SqlClient package, which receives no new features.

$ dotnet add package Microsoft.Data.SqlClient

We add the package to our project.

The SqlConnection creates a connection to the server. The SqlCommand executes an SQL statement. The SqlDataReader reads a forward-only stream of rows. The SqlTransaction groups commands into an atomic unit.

C# SQL Server version

In the first program, we check the version of the SQL Server.

using Microsoft.Data.SqlClient;

string cs = "Server=localhost,1433;Database=testdb;User Id=sa;Password=Passw0rd!2026;TrustServerCertificate=True";

using var con = new SqlConnection(cs);
con.Open();

using var cmd = new SqlCommand("SELECT @@VERSION", con);
string? version = cmd.ExecuteScalar()?.ToString();

Console.WriteLine(version);

We connect to the database and get information about the server.

string cs = "Server=localhost,1433;Database=testdb;User Id=sa;Password=Passw0rd!2026;TrustServerCertificate=True";

This is the connection string. Since version 4, the provider encrypts the connection by default and validates the server certificate. The Docker container uses a self-signed certificate, so we add TrustServerCertificate=True. Do not use this option in production; install a proper certificate instead. A real application should also read the password from configuration or a secret store, not from the source code.

With a local SQL Server Express on Windows, the connection string would be Server=localhost\SQLEXPRESS;Database=testdb;Integrated Security=True;TrustServerCertificate=True.

using var cmd = new SqlCommand("SELECT @@VERSION", con);
string? version = cmd.ExecuteScalar()?.ToString();

The @@VERSION function returns the system and build information of the installation. The ExecuteScalar method returns the first column of the first row, which suits queries that produce a single value.

$ dotnet run
Microsoft SQL Server 2022 (RTM-CU27-GDR) (KB5126934) - 16.0.4305.5 (X64) 
	Oct  2 2026 17:28:11 
	Copyright (C) 2022 Microsoft Corporation
	Developer Edition (64-bit) on Linux (Ubuntu 22.04.5 LTS) <X64>

C# SQL Server create table

In the following example, we create a database table and fill it with data.

using Microsoft.Data.SqlClient;

string cs = "Server=localhost,1433;Database=testdb;User Id=sa;Password=Passw0rd!2026;TrustServerCertificate=True";

using var con = new SqlConnection(cs);
con.Open();

using var cmd = con.CreateCommand();

cmd.CommandText = "DROP TABLE IF EXISTS cars";
cmd.ExecuteNonQuery();

cmd.CommandText = """
    CREATE TABLE cars(
        id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        price INT
    )
    """;
cmd.ExecuteNonQuery();

cmd.CommandText = """
    INSERT INTO cars(name, price) VALUES
        ('Audi', 52642), ('Mercedes', 57127), ('Skoda', 9000), ('Volvo', 29000),
        ('Bentley', 350000), ('Citroen', 21000), ('Hummer', 41400), ('Volkswagen', 21600)
    """;
int n = cmd.ExecuteNonQuery();

Console.WriteLine($"Table cars created, {n} rows inserted");

We create a cars table and insert eight rows with a single statement.

cmd.CommandText = "DROP TABLE IF EXISTS cars";
cmd.ExecuteNonQuery();

We use ExecuteNonQuery for statements that return no result set, such as DROP, CREATE, INSERT, or DELETE. It returns the number of affected rows.

cmd.CommandText = """
    CREATE TABLE cars(
        id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        price INT
    )
    """;

The cars table is created. In SQL Server, an auto-incremented column is declared with the IDENTITY property. A raw string literal keeps the multi-line SQL readable.

cmd.CommandText = """
    INSERT INTO cars(name, price) VALUES
        ('Audi', 52642), ('Mercedes', 57127), ('Skoda', 9000), ('Volvo', 29000),
        ('Bentley', 350000), ('Citroen', 21000), ('Hummer', 41400), ('Volkswagen', 21600)
    """;
int n = cmd.ExecuteNonQuery();

A multi-row VALUES clause inserts all rows in one round trip.

$ dotnet run
Table cars created, 8 rows inserted

C# SQL Server parameterized commands

Parameterized commands guard against SQL injection. We use placeholders instead of writing values directly into the SQL text; the server receives the statement and the values separately.

using Microsoft.Data.SqlClient;
using System.Data;

string cs = "Server=localhost,1433;Database=testdb;User Id=sa;Password=Passw0rd!2026;TrustServerCertificate=True";

using var con = new SqlConnection(cs);
con.Open();

using var cmd = new SqlCommand(
    "INSERT INTO cars(name, price) OUTPUT INSERTED.id VALUES(@name, @price)", con);

cmd.Parameters.Add("@name", SqlDbType.VarChar, 255).Value = "BMW";
cmd.Parameters.Add("@price", SqlDbType.Int).Value = 36600;

int id = (int)cmd.ExecuteScalar()!;
Console.WriteLine($"row inserted, id {id}");

We add a new car to the cars table and learn its generated id.

using var cmd = new SqlCommand(
    "INSERT INTO cars(name, price) OUTPUT INSERTED.id VALUES(@name, @price)", con);

@name and @price are placeholders. The OUTPUT INSERTED.id clause makes the INSERT return the identity value of the new row, so we can read it with ExecuteScalar.

cmd.Parameters.Add("@name", SqlDbType.VarChar, 255).Value = "BMW";
cmd.Parameters.Add("@price", SqlDbType.Int).Value = 36600;

We bind the values with Parameters.Add, stating the SQL type explicitly. Specifying the type and size lets the server reuse the cached execution plan; with AddWithValue, the type is inferred from the value. The old Prepare call is not necessary, because SQL Server caches plans for parameterized statements anyway.

$ dotnet run
row inserted, id 9

C# SqlDataReader

The SqlDataReader retrieves rows returned by a query. It provides fast, forward-only, read-only access to the results, and it is the most efficient way to read data. We create it with the ExecuteReader method of the command.

using Microsoft.Data.SqlClient;

string cs = "Server=localhost,1433;Database=testdb;User Id=sa;Password=Passw0rd!2026;TrustServerCertificate=True";

using var con = new SqlConnection(cs);
con.Open();

using var cmd = new SqlCommand("SELECT * FROM cars", con);
using SqlDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
    Console.WriteLine($"{rdr.GetInt32(0)} {rdr.GetString(1)} {rdr.GetInt32(2)}");
}

We get all rows from the cars table and print them.

while (rdr.Read())
{
    Console.WriteLine($"{rdr.GetInt32(0)} {rdr.GetString(1)} {rdr.GetInt32(2)}");
}

The Read method advances the reader to the next row and returns false when there are no more rows. The typed getters take a column ordinal and avoid the boxing that the indexer would cause. For nullable columns, check IsDBNull first.

$ dotnet run
1 Audi 52642
2 Mercedes 57127
3 Skoda 9000
4 Volvo 29000
5 Bentley 350000
6 Citroen 21000
7 Hummer 41400
8 Volkswagen 21600
9 BMW 36600

C# SQL Server column headers

In the following example, we print column headers together with the data.

using Microsoft.Data.SqlClient;

string cs = "Server=localhost,1433;Database=testdb;User Id=sa;Password=Passw0rd!2026;TrustServerCertificate=True";

using var con = new SqlConnection(cs);
con.Open();

using var cmd = new SqlCommand("SELECT * FROM cars", con);
using SqlDataReader rdr = cmd.ExecuteReader();

Console.WriteLine($"{rdr.GetName(0),-4} {rdr.GetName(1),-10} {rdr.GetName(2),8}");

while (rdr.Read())
{
    Console.WriteLine($"{rdr.GetInt32(0),-4} {rdr.GetString(1),-10} {rdr.GetInt32(2),8}");
}

The GetName method returns the name of a column. The alignment components of the interpolated strings left-align or right-align the values in fixed-width fields.

$ dotnet run
id   name          price
1    Audi          52642
2    Mercedes      57127
3    Skoda          9000
4    Volvo         29000
5    Bentley      350000
6    Citroen       21000
7    Hummer        41400
8    Volkswagen    21600
9    BMW           36600

C# SQL Server transaction rollback

A transaction groups several statements into one atomic unit. If something fails, we roll back and the database is left unchanged.

using Microsoft.Data.SqlClient;
using System.Data;

string cs = "Server=localhost,1433;Database=testdb;User Id=sa;Password=Passw0rd!2026;TrustServerCertificate=True";

using var con = new SqlConnection(cs);
con.Open();

using var tx = con.BeginTransaction();
try
{
    using var upd = new SqlCommand(
        "UPDATE cars SET price = price - @amount WHERE name = @name", con, tx);
    upd.Parameters.Add("@amount", SqlDbType.Int).Value = 1000;
    upd.Parameters.Add("@name", SqlDbType.VarChar, 255).Value = "Audi";
    upd.ExecuteNonQuery();

    throw new InvalidOperationException("something went wrong");
}
catch (Exception e)
{
    tx.Rollback();
    Console.WriteLine($"rolled back: {e.Message}");
}

using var chk = new SqlCommand("SELECT price FROM cars WHERE name = 'Audi'", con);
Console.WriteLine($"Audi price: {chk.ExecuteScalar()}");

We lower the price of the Audi, then simulate a failure before committing.

using var tx = con.BeginTransaction();

BeginTransaction starts a transaction on the connection. Every command that takes part in it must be associated with the transaction; here we pass it as the third argument of the SqlCommand constructor. SQL Server throws an exception if a command runs on a connection with an active transaction but is not enlisted in it.

catch (Exception e)
{
    tx.Rollback();
    Console.WriteLine($"rolled back: {e.Message}");
}

In the catch block we call Rollback, which discards the update. A successful path would call Commit instead.

$ dotnet run
rolled back: something went wrong
Audi price: 52642

The price is unchanged, so the update was undone.

Source

Microsoft ADO.NET for SQL Server

In this article we have shown how to program SQL Server databases in C# with Microsoft.Data.SqlClient.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all C# tutorials.