MSDN Documentation

ADO.NET Parameters

Parameters are a crucial aspect of working with ADO.NET, providing a secure and efficient way to pass values to SQL statements and stored procedures. They help prevent SQL injection attacks and improve performance by allowing the database to cache execution plans.

What are Parameters?

In ADO.NET, a parameter represents a placeholder in a SQL command that will be replaced by a specific value at runtime. This is typically achieved using objects like DbParameter (or its specific implementations like SqlParameter or OracleParameter).

Why Use Parameters?

• Security: Prevents SQL injection by separating the SQL command from the data. The database engine treats parameter values strictly as data, not executable code.
• Performance: Allows the database to reuse cached execution plans for parameterized queries, leading to faster execution over time.
• Readability: Makes SQL statements cleaner and easier to understand.
• Data Type Handling: Ensures that data is correctly converted and passed to the database according to its specified type.

Working with Parameters

The primary objects involved in parameter management are:

• DbCommand (or derived classes like SqlCommand): Represents the SQL statement or stored procedure to be executed.
• DbParameterCollection: A collection of DbParameter objects associated with a command.
• DbParameter (or derived classes): Represents a single parameter, including its name, data type, direction, and value.

Adding Parameters to a Command

You can add parameters to a command in several ways. The most common is by creating parameter objects and adding them to the Parameters collection of the DbCommand.

using System.Data.SqlClient;

// Assuming you have an open SqlConnection named 'connection'

string sql = "SELECT CustomerName, City FROM Customers WHERE Country = @Country AND City = @City";
SqlCommand command = new SqlCommand(sql, connection);

// Create and add parameters
SqlParameter paramCountry = new SqlParameter();
paramCountry.ParameterName = "@Country";
paramCountry.SqlDbType = System.Data.SqlDbType.NVarChar;
paramCountry.Value = "USA";
command.Parameters.Add(paramCountry);

SqlParameter paramCity = new SqlParameter("@City", System.Data.SqlDbType.NVarChar, 50);
paramCity.Value = "New York";
command.Parameters.Add(paramCity);

// Alternative way to add parameters
command.Parameters.AddWithValue("@Region", "West"); // Inferring type and size

// ... execute the command ...
                

Parameter Direction

Parameters can have different directions, indicating how data flows between the application and the database:

• Input (Input or In): The default. Data flows from the application to the database.
• Output (Output or Out): Data flows from the database to the application. Useful for retrieving values from stored procedure output parameters.
• InputOutput (InputOutput or InOut): Data flows in both directions.
• ReturnValue (ReturnValue): Used to retrieve the return value of a stored procedure.

using System.Data.SqlClient;

// Assuming you have an open SqlConnection named 'connection'

string storedProc = "GetCustomerCountByCountry";
SqlCommand command = new SqlCommand(storedProc, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;

// Input parameter
SqlParameter paramCountry = new SqlParameter("@Country", System.Data.SqlDbType.NVarChar, 50);
paramCountry.Value = "Canada";
command.Parameters.Add(paramCountry);

// Output parameter
SqlParameter paramCount = new SqlParameter("@CustomerCount", System.Data.SqlDbType.Int);
paramCount.Direction = System.Data.ParameterDirection.Output;
command.Parameters.Add(paramCount);

command.ExecuteNonQuery(); // Execute the stored procedure

// Retrieve the output value
int customerCount = (int)paramCount.Value;
Console.WriteLine($"Number of customers in Canada: {customerCount}");
                    

Parameter Value and Size

When setting parameter values, it's important to consider the data type and size. ADO.NET often handles this automatically when using AddWithValue, but explicit setting provides more control and can sometimes prevent issues.

• Value: The actual data to be passed.
• Size: For string and binary data types, this specifies the maximum size.
• Precision and Scale: For numeric types, these define the maximum total digits and the number of digits to the right of the decimal point.

Parameter Markers

Different database providers use different syntax for parameter markers:

• SQL Server: @ParameterName (e.g., @CustomerID)
• ODBC, OLE DB: ? (e.g., ?)
• Oracle: :ParameterName (e.g., :customer_id)
• MySQL: ? (e.g., ?) or @ParameterName

Always ensure you use the correct marker syntax for your specific database provider.

Stored Procedures and Parameters

Parameters are especially critical when executing stored procedures, as they are the standard mechanism for passing input values and retrieving output values or return codes.

using System.Data.SqlClient;

// Assuming you have an open SqlConnection named 'connection'

SqlCommand command = new SqlCommand("usp_UpdateProductPrice", connection);
command.CommandType = System.Data.CommandType.StoredProcedure;

// Input parameter for Product ID
command.Parameters.AddWithValue("@ProductID", 123);

// Input parameter for New Price
SqlParameter priceParam = new SqlParameter("@NewPrice", System.Data.SqlDbType.Decimal, 10, 2);
priceParam.Value = 25.99m;
command.Parameters.Add(priceParam);

// Output parameter to indicate success/failure
SqlParameter statusParam = new SqlParameter("@Status", System.Data.SqlDbType.Int);
statusParam.Direction = System.Data.ParameterDirection.Output;
command.Parameters.Add(statusParam);

command.ExecuteNonQuery();

int status = (int)statusParam.Value;
if (status == 0) {
    Console.WriteLine("Product price updated successfully.");
} else {
    Console.WriteLine("Failed to update product price.");
}
                

By mastering ADO.NET parameters, you can write more robust, secure, and performant data access code.