MSDN Documentation

When working with databases in ADO.NET, especially with SQL commands, it's crucial to understand and utilize parameters effectively. Parameters help prevent SQL injection attacks, improve query performance by allowing the database to cache execution plans, and simplify the process of passing values to your database commands.

This document explores the concepts, best practices, and common usage patterns for parameters in ADO.NET.

• Security: The primary reason for using parameters is to prevent SQL injection. By treating user-supplied input as data rather than executable SQL code, you significantly enhance the security of your application.
• Performance: Database systems can often cache the execution plan for parameterized queries. When the same query is executed multiple times with different parameter values, the database can reuse the cached plan, leading to improved performance.
• Readability and Maintainability: Parameterized queries can be cleaner and easier to read compared to constructing SQL strings with embedded values.
• Data Type Handling: Parameters explicitly define the data type of the value being passed, reducing the chance of type conversion errors and ensuring data integrity.

ADO.NET supports several types of parameters, depending on the data provider you are using (e.g., SqlClient for SQL Server, OracleClient for Oracle, OleDbClient for OLE DB providers, MySqlClient for MySQL).

The most common are:

• Input Parameters: Used to pass values from your application to the database. This is the most frequent use case.
• Output Parameters: Used to retrieve values from the database back into your application. This is common for stored procedures that return status codes or calculated values.
• InputOutput Parameters: A combination of input and output. A value is passed into the stored procedure, and a modified value can be returned.
• ReturnValue Parameters: Specifically used to capture the return value of a stored procedure.

The core objects involved in working with parameters are:

• DbCommand (or specific provider command like SqlCommand): This object represents the SQL statement or stored procedure to be executed.
• DbParameter (or specific provider parameter like SqlParameter): This object represents a single parameter within the command.
• DbParameterCollection (or specific provider collection like SqlParameterCollection): A collection of DbParameter objects associated with a DbCommand.

Adding Parameters

You can add parameters to a command in several ways:

1. Programmatically: Create DbParameter objects and add them to the command's Parameters collection.
2. Using DbCommandBuilder: For DataAdapter objects, DbCommandBuilder can automatically generate SQL commands and their parameters for simple INSERT, UPDATE, and DELETE operations.
3. By Association: When executing a stored procedure that has parameters, some providers allow you to pass parameter values directly.

Parameter Syntax

The syntax for parameter placeholders varies by data provider:

• SQL Server (SqlClient): Uses named parameters prefixed with an '@' symbol. Example: @ProductName.
• OLE DB and ODBC (OleDbClient): Uses question marks (?) as placeholders. Parameter values must be added to the collection in the exact order they appear in the SQL statement.
• Oracle (OracleClient): Uses named parameters prefixed with a ':' symbol. Example: :product_name.
• MySQL (MySqlClient): Can use named parameters prefixed with '@' or '?' (positional). Example: @product_name or ?.

// Assume conn is an open SqlConnection
SqlCommand cmd = new SqlCommand("SELECT ProductID, ProductName FROM Products WHERE CategoryID = @CategoryID", conn);
cmd.Parameters.AddWithValue("@CategoryID", 5); // Data type is inferred

// Assume conn is an open OleDbConnection
OleDbCommand cmd = new OleDbCommand("SELECT CustomerID, CompanyName FROM Customers WHERE City = ?", conn);
cmd.Parameters.AddWithValue("?", "London"); // Question mark placeholder

Key Parameter Properties

When creating parameters programmatically, you'll typically set these properties:

• ParameterName: The name of the parameter (e.g., @CategoryID). Required for named parameters.
• Value: The actual value to be passed to the parameter.
• DbType: The ADO.NET database type (e.g., DbType.Int32, DbType.String).
• Size: The maximum size for variable-length data types like strings and byte arrays.
• Direction: Specifies whether the parameter is Input, Output, InputOutput, or ReturnValue.

SqlCommand cmd = new SqlCommand("INSERT INTO Orders (CustomerID, OrderDate) VALUES (@CustomerID, @OrderDate)", conn);

// Using AddWithValue (convenient but can be less efficient)
cmd.Parameters.AddWithValue("@CustomerID", customerId);

// More explicit and often preferred
SqlParameter customerParam = new SqlParameter("@CustomerID", SqlDbType.Int);
customerParam.Value = customerId;
cmd.Parameters.Add(customerParam);

SqlParameter dateParam = new SqlParameter("@OrderDate", SqlDbType.DateTime);
dateParam.Value = orderDate;
cmd.Parameters.Add(dateParam);

Output and ReturnValue Parameters

These are typically used with stored procedures.

SqlCommand cmd = new SqlCommand("GetCustomerCountByCategory", conn);
cmd.CommandType = CommandType.StoredProcedure;

// Input parameter
cmd.Parameters.AddWithValue("@CategoryID", categoryId);

// Output parameter
SqlParameter outputParam = new SqlParameter("@CustomerCount", SqlDbType.Int);
outputParam.Direction = ParameterDirection.Output;
cmd.Parameters.Add(outputParam);

conn.Open();
cmd.ExecuteNonQuery(); // Execute the stored procedure

// Retrieve the output value
int customerCount = (int)outputParam.Value;

SqlCommand cmd = new SqlCommand("AddProduct", conn);
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@ProductName", productName);
cmd.Parameters.AddWithValue("@Price", price);

// ReturnValue parameter
SqlParameter returnParam = new SqlParameter();
returnParam.Direction = ParameterDirection.ReturnValue;
returnParam.SqlDbType = SqlDbType.Int; // Or appropriate type for return value
cmd.Parameters.Add(returnParam);

conn.Open();
cmd.ExecuteNonQuery();

// Retrieve the return value
int statusCode = (int)returnParam.Value;

Passing null directly to a parameter's Value property often works, but it's best practice to explicitly use DBNull.Value for database NULLs.

string nullableString = null;
SqlParameter param = new SqlParameter("@NullableColumn", SqlDbType.NVarChar, 50);

if (nullableString == null)
{
    param.Value = DBNull.Value;
}
else
{
    param.Value = nullableString;
}
cmd.Parameters.Add(param);

While parameters generally improve performance, be mindful of:

• AddWithValue: This method is convenient but can sometimes lead to less optimal query plans. It infers the DbType from the .NET type of the value, which might not always be the most efficient mapping. For maximum control and performance, it's often better to explicitly specify the SqlDbType or DbType.
• Parameter Discovery: For stored procedures, using SqlCommandBuilder.DeriveParameters(command) can automatically populate the Parameters collection. While convenient, it adds an extra round trip to the database and might impact performance in high-frequency scenarios. It's usually better to define parameters manually or based on documentation.

• Always use parameters for user input.
• Be explicit with data types when creating parameters programmatically (SqlParameter, OracleParameter, etc.) rather than relying solely on AddWithValue.
• Use DBNull.Value for database NULLs.
• Match parameter data types to column data types in your database for optimal performance and integrity.
• Consider using CommandType.StoredProcedure when executing stored procedures.
• Properly dispose of database connections and commands using using statements or by explicitly calling Dispose().

Mastering ADO.NET parameters is fundamental for building secure, performant, and maintainable data-driven applications. By adhering to the principles outlined in this guide, you can significantly improve the quality and robustness of your database interactions.