Connecting to SQL Server
Install Microsoft.Data.SqlClient via NuGet. Build a connection string or use the builder class.
SQL Server connection
SqlConnect.cs
using Microsoft.Data.SqlClient;
var builder = new SqlConnectionStringBuilder
{
DataSource = @"(localdb)\MSSQLLocalDB",
InitialCatalog = "Northwind",
IntegratedSecurity = true
};
using var conn = new SqlConnection(builder.ConnectionString);
await conn.OpenAsync();
Console.WriteLine($"Connected: {conn.Database}");Executing Commands
Use ExecuteNonQuery for INSERT/UPDATE/DELETE, ExecuteScalar for a single value, and ExecuteReader for row-by-row reading.
ADO commands
ADOCommands.cs
// ExecuteScalar — get product count
using var cmd = new SqlCommand("SELECT COUNT(*) FROM Products", conn);
int count = (int)(await cmd.ExecuteScalarAsync() ?? 0);
Console.WriteLine($"Products: {count}");
// ExecuteReader — read rows
cmd.CommandText = "SELECT ProductName, UnitPrice FROM Products WHERE UnitPrice > @min";
cmd.Parameters.AddWithValue("@min", 50m);
using var reader = await cmd.ExecuteReaderAsync();
while (await reader.ReadAsync())
Console.WriteLine($"{reader[0]}: {reader[1]:C2}");Transactions
Wrap multiple commands in a transaction so that either all succeed or all are rolled back.
Transaction
Transaction.cs
using var tx = conn.BeginTransaction();
try
{
var cmd1 = new SqlCommand("UPDATE Accounts SET Balance -= @amt WHERE Id=1", conn, tx);
cmd1.Parameters.AddWithValue("@amt", 100m);
await cmd1.ExecuteNonQueryAsync();
var cmd2 = new SqlCommand("UPDATE Accounts SET Balance += @amt WHERE Id=2", conn, tx);
cmd2.Parameters.AddWithValue("@amt", 100m);
await cmd2.ExecuteNonQueryAsync();
await tx.CommitAsync();
Console.WriteLine("Transfer complete.");
}
catch
{
await tx.RollbackAsync();
throw;
}