Parameterized Queries
Parameters are the single most important ADO.NET habit. They keep your app safe, correct and fast.
On this page · 5 min · Intermediate
On this page
- In simple words
- Learning objectives
- Explanation
- Code example
- Step by step
- Real-world use
- Common mistakes
- Best practices
- Remember this
- Practice exercise
- Mini quiz
- Related topics
- My notes
- Reading time
- 5 min
- Difficulty
- Intermediate
- Updated
- Oct 29, 2026
Related tutorials
In simple words#
Parameters are like filling a printed form: the form (SQL) is fixed, you only write in the boxes (values). No one can change the form by what they write in a box.
- Parameter
- @name placeholder with a separate value.
- SqlDbType
- The exact SQL type of the value.
- DBNull.Value
- How to send NULL.
Learning objectives#
- Add parameters with explicit types
- Explain why AddWithValue can hurt performance
- Parameterise LIKE searches and NULLs
- Handle optional filters
Explanation#
A parameter is a placeholder (@name) in the SQL with its value sent separately. SQL Server never treats the value as code.
Parameters.Add("@name", SqlDbType.NVarChar, 100).Value = … states the exact type and size. AddWithValue guesses from the .NET value — for strings it uses NVARCHAR sized to the value, which can cause implicit conversions on VARCHAR columns and many slightly different cached plans.
For LIKE, put the wildcards in the value, not the SQL: Value = "%" + term + "%".
To send NULL, use DBNull.Value. For optional filters, use (@cat IS NULL OR CategoryId = @cat).
Code example: A safe product search#
static async Task<List<string>> SearchAsync(string cs, string? term, int? categoryId, decimal maxPrice)
{
const string sql = """
SELECT Name FROM Products
WHERE (@term IS NULL OR Name LIKE @term)
AND (@cat IS NULL OR CategoryId = @cat)
AND Price <= @max
ORDER BY Name
""";
await using var conn = new SqlConnection(cs);
await conn.OpenAsync();
await using var cmd = new SqlCommand(sql, conn);
cmd.Parameters.Add("@term", SqlDbType.NVarChar, 102).Value = term is null ? DBNull.Value : quot;%{term}%";
cmd.Parameters.Add("@cat", SqlDbType.Int).Value = (object?)categoryId ?? DBNull.Value;
cmd.Parameters.Add(new SqlParameter("@max", SqlDbType.Decimal) { Precision = 10, Scale = 2, Value = maxPrice });
var names = new List<string>();
await using var r = await cmd.ExecuteReaderAsync();
while (await r.ReadAsync()) names.Add(r.GetString(0));
return names;
}
Console.WriteLine(string.Join(", ", await SearchAsync(cs, "o", null, 60000)));
Keyboard, Laptop, Mouse
This example is illustrative. Copy or download it and run it in your own environment; the expected output is shown above.
Step-by-step explanation#
- The SQL uses placeholders only — no user input is concatenated.
- term may be null; then the filter is skipped.
- Wildcards are added to the value, so “o” becomes “%o%”.
- Decimal precision and scale match the column definition.
Real-world use case#
The search box on a shop page passes whatever the user types straight into @term — even input like ' OR 1=1 -- is just text to search for.
Common mistakes#
"WHERE Name = '@name'" searches for the literal text @name. Write WHERE Name = @name.
A parameter with Value = null is not sent at all, causing “parameter was not supplied” errors.
Best practices#
- Specify SqlDbType and size
- Prefer Parameters.Add over AddWithValue
- Use DBNull.Value for NULL
- Use table-valued parameters for lists of values
Remember this#
@placeholder in SQL, value in Parameters.
Wildcards go in the value.
null → DBNull.Value.
Practice exercise#
Your task: Write the parameter for a VARCHAR(20) column called Sku without causing an NVARCHAR conversion.
Show solution
cmd.Parameters.Add("@sku", SqlDbType.VarChar, 20).Value = sku;
Mini quiz#
How do you send a NULL value in a SqlParameter?
Want more? Practise all ADO.NET MCQs · ADO.NET interview questions
My notes#
Saved in this browser.