This lesson shows how to pass values into SQL Server commands with parameters. You will create a named placeholder, specify a parameter’s type and size, and add it to the command before executing a query.
- Keep user values separate from SQL command text.
- Match parameter metadata to the database schema.
- Handle optional values and reader lifetime deliberately.
Updated technical examples: 4 October 2026. Target: .NET 10, SDK 10.0.401, Microsoft.Data.SqlClient 7.1.1. Examples compile; database-independent checks run locally. No SQL Server was available during verification, so the SQL queries and server-backed output have not been executed.
Why use parameters?
Building a query by concatenating a user’s city into its SQL text can let that value alter the statement. A parameter sends the value separately, so apostrophes and SQL-looking text remain data for this query. Microsoft’s parameter documentation explains this distinction.
Parameterization applies to values. It does not make a dynamic table name, column name, or ORDER BY fragment safe. Keep identifiers fixed or choose them from a small application-controlled allowlist. Stored procedures also need parameterized handling when they construct dynamic SQL internally.
Set up the current provider
The original lesson used System.Data.SqlClient. The updated example uses Microsoft.Data.SqlClient, Microsoft’s SQL Server provider. Its namespace guide describes migration considerations. Installing a package changes the provider; it does not create SQL Server or install the Northwind database.
dotnet new console --framework net10.0
dotnet add package Microsoft.Data.SqlClient --version 7.1.1Use a disposable test database containing dbo.Customers with CompanyName as a required string, ContactName as a nullable string, and City as nullable nvarchar(15). This matches the columns used by the original Northwind examples. Supply the connection string through CSHARP_STATION_SQL in your local environment. Keep credentials out of source control; use authentication and certificate settings appropriate to your server. The examples do not disable certificate validation.
Construct, define, and attach the parameter
The SQL text contains @City. The helper adds a parameter with that name, SqlDbType.NVarChar, and size 15, matching the example column. The command’s Parameters collection associates the value with the placeholder.
Use metadata that matches your actual column. For a varchar column choose VarChar; for decimal inputs establish the intended precision and scale. AddWithValue infers metadata from the supplied .NET value, which may differ from the schema. Explicit metadata makes that choice visible. See Microsoft’s SQL Server type mappings.
Listing 1: Adding parameters to queries
Replace Program.cs with this complete example. It keeps the original city-filtered customer task and uses explicit selected columns.
using System;
using System.Data;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Data.SqlClient;
class ParamDemo
{
public static SqlCommand CreateCommand(SqlConnection connection, string? city)
{
if (city is { Length: > 15 })
throw new ArgumentException("City must contain at most 15 characters.", nameof(city));
var command = new SqlCommand(
"SELECT CompanyName, ContactName FROM dbo.Customers " +
"WHERE (@City IS NULL OR City = @City) ORDER BY CompanyName;", connection);
command.Parameters.Add("@City", SqlDbType.NVarChar, 15).Value =
(object?)city ?? DBNull.Value;
return command;
}
static async Task<int> Main(string[] args)
{
string? connectionString = Environment.GetEnvironmentVariable("CSHARP_STATION_SQL");
if (string.IsNullOrWhiteSpace(connectionString))
{
Console.Error.WriteLine("Set CSHARP_STATION_SQL to your test database connection string.");
return 2;
}
string city = args.Length == 0 ? "London" : args[0];
using var timeout = new CancellationTokenSource(TimeSpan.FromSeconds(30));
using var connection = new SqlConnection(connectionString);
SqlCommand command;
try
{
command = CreateCommand(connection, city);
}
catch (ArgumentException)
{
Console.Error.WriteLine("City must contain at most 15 characters.");
return 1;
}
using (command)
{
await connection.OpenAsync(timeout.Token);
using SqlDataReader reader = await command.ExecuteReaderAsync(timeout.Token);
while (await reader.ReadAsync(timeout.Token))
{
string company = reader.GetString(0);
string? contact = reader.IsDBNull(1) ? null : reader.GetString(1);
Console.WriteLine($"{company} | {contact ?? "(null)"}");
}
return 0;
}
}
}With your test database configured, run dotnet run -- London. Each matching row is formatted as CompanyName | ContactName, using (null) for a missing contact. The exact customers depend on your dataset; no sample database result is claimed here.
Without a connection string, this verified guard prints the following to standard error and returns exit code 2 before opening a connection:
Set CSHARP_STATION_SQL to your test database connection string.What null and size mean
The helper rejects city strings longer than 15 characters before opening the connection. As Microsoft documents for SqlParameter.Size, a string parameter’s size limits the amount of data transmitted. Parameter size is not a substitute for application validation. The length check prevents an oversized value from being silently shortened to the parameter size and querying a different value. An oversized console argument prints the length message to standard error and returns exit code 1 before opening the connection. Empty strings and null are also different values; choose their meaning according to your application.
SQL null is represented by DBNull.Value when assigning a parameter. In this example, a null helper argument intentionally disables the city filter through @City IS NULL OR City = @City. The console path supplies London by default and never passes null. The optional-filter predicate illustrates null handling; evaluate its query plan against your actual schema and workload before adopting it in production. Select a query strategy from measured behavior rather than applying a universal plan hint. To find rows whose City is SQL NULL, write an explicit City IS NULL condition; City = @City with a null parameter does not provide that behavior.
Close resources and observe completion
The connection, command, and reader are disposed by using declarations, in reverse creation order, including when an exception leaves the scope. The reader is closed before its connection. The asynchronous open, execute, and read calls receive a cancellation token; the caller must await them. Cancellation requests and a timeout do not prove a server-side operation was never started or that a write was rolled back.
The example performs a SELECT. For writes, define transaction boundaries and retry behavior explicitly. Avoid logging credentials or parameter values that contain private data. Microsoft’s SqlClient asynchronous programming guide describes the asynchronous APIs.
Verification and next lesson
Local checks compiled the complete program against the pinned package, verified the missing-configuration guard, and inspected commands created from ordinary, empty, apostrophe-containing, SQL-looking, and null values, and confirmed that an oversized value is rejected before a command is created. Those checks confirm command construction and validation; they do not test SQL Server execution, query plans, permissions, collation, encryption, or result rows.
To complete integration testing, run the query against a disposable database with known rows, test a city with an apostrophe, a nonmatching city, and the intended null-filter semantics, then verify the reader closes on failures. Keep the database schema and actual results with your test record.
Review Lesson 04: Reading Data with a SqlDataReader, then continue to Lesson 07: Using Stored Procedures.