ADO.NET Parameters

This document explores the fundamental role of parameters in ADO.NET for securely and efficiently interacting with databases.

Understanding Parameters

Parameters are essential for building robust database applications. They help prevent SQL injection vulnerabilities by separating SQL code from user-provided data. Additionally, parameters allow the database engine to cache query plans, leading to improved performance.

Types of Parameters

ADO.NET supports several ways to define parameters, primarily through the use of placeholders in your SQL commands:

• Named Parameters: Use a name preceded by a colon (e.g., :CustomerID) or an at sign (e.g., @CustomerID), depending on the data provider.
• Positional Parameters: Use a question mark (?) as a placeholder. The order in which parameters are added to the Parameters collection must match their order in the SQL statement.

Working with Parameters

The core class for managing parameters is DbParameter, which has provider-specific implementations like SqlParameter (for SQL Server) and OracleParameter (for Oracle).

Adding Parameters

You typically add parameters to a command's Parameters collection.

using System.Data.SqlClient;

// ...

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string query = "SELECT CustomerName FROM Customers WHERE CustomerID = @ID";
    SqlCommand command = new SqlCommand(query, connection);

    // Add a named parameter
    command.Parameters.AddWithValue("@ID", customerId);

    // Or explicitly define parameter type and size
    // SqlParameter param = new SqlParameter("@ID", SqlDbType.Int);
    // param.Value = customerId;
    // command.Parameters.Add(param);

    // ... execute command ...
}
                

Imports System.Data.SqlClient

' ...

Using connection As New SqlConnection(connectionString)
    connection.Open()
    Dim query As String = "SELECT CustomerName FROM Customers WHERE CustomerID = @ID"
    Dim command As New SqlCommand(query, connection)

    ' Add a named parameter
    command.Parameters.AddWithValue("@ID", customerId)

    ' Or explicitly define parameter type and size
    ' Dim param As New SqlParameter("@ID", SqlDbType.Int)
    ' param.Value = customerId
    ' command.Parameters.Add(param)

    ' ... execute command ...
End Using
                

Parameter Direction

Parameters can be used for input, output, or both. The Direction property of a DbParameter object controls this.

• ParameterDirection.Input (default): The parameter passes a value from the application to the database.
• ParameterDirection.Output: The parameter returns a value from the database to the application.
• ParameterDirection.InputOutput: The parameter passes a value from the application to the database and returns a value from the database to the application.
• ParameterDirection.ReturnValue: The parameter returns the value from a stored procedure's return value.

Parameter Types and Size

It's good practice to specify the DbType or the provider-specific type (e.g., SqlDbType) and size for parameters. This improves clarity and can help the database optimize operations.

Common Parameter Properties

Example: Stored Procedure with Input and Output Parameters

Consider a stored procedure that updates a record and returns the number of rows affected.

CREATE PROCEDURE UpdateProductPrice
    @ProductID INT,
    @NewPrice DECIMAL(10, 2),
    @RowsAffected INT OUTPUT
AS
BEGIN
    UPDATE Products
    SET UnitPrice = @NewPrice
    WHERE ProductID = @ProductID;

    SET @RowsAffected = @@ROWCOUNT;
END
                

using System.Data;
using System.Data.SqlClient;

// ...

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    SqlCommand command = new SqlCommand("UpdateProductPrice", connection);
    command.CommandType = CommandType.StoredProcedure;

    // Input parameter for ProductID
    command.Parameters.AddWithValue("@ProductID", 101);

    // Input parameter for NewPrice
    command.Parameters.AddWithValue("@NewPrice", 25.99);

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

    command.ExecuteNonQuery();

    // Retrieve the output value
    int affectedCount = (int)outputParam.Value;
    Console.WriteLine($"Rows affected: {affectedCount}");
}
                

Parameter Discovery

Some data providers support parameter discovery, where ADO.NET can automatically retrieve the parameter information for a stored procedure from the database. This can be useful for dynamically building commands.

SqlCommandBuilder.DeriveParameters(command);
// Now the command.Parameters collection is populated.
        

Conclusion

Mastering ADO.NET parameters is crucial for writing secure, efficient, and maintainable data access code. By understanding the different types of parameters, their properties, and best practices, you can significantly improve the quality of your .NET database applications.