Microsoft Docs

Commands and Parameters in ADO.NET

ADO.NET provides a rich set of classes for interacting with data sources, and at the core of data manipulation are Command objects. These objects are used to execute SQL statements and stored procedures against a database.

Understanding Command Objects

A Command object represents a SQL statement or stored procedure that you want to execute. The specific type of Command object depends on the data provider you are using (e.g., SqlCommand for SQL Server, OracleCommand for Oracle, MySql.Data.MySqlClient.MySqlCommand for MySQL).

Key properties of a Command object include:

• CommandText: A string containing the SQL statement or the name of the stored procedure to execute.
• CommandType: Specifies how the CommandText property should be interpreted (e.g., Text for a SQL statement, StoredProcedure for a stored procedure, TableDirect for a table).
• Connection: The Connection object to which this command belongs.
• Parameters: A collection of Parameter objects that represent parameters passed to the command.
• Transaction: The Transaction in which the command should be executed.

Working with Parameters

Parameters are essential for several reasons:

• Security: They help prevent SQL injection attacks by ensuring that input is treated as data, not executable code.
• Performance: Prepared statements (which utilize parameters) can be cached by the database for reuse, leading to better performance.
• Readability and Maintainability: Makes SQL statements cleaner and easier to manage.

The Parameters collection of a Command object is used to add, remove, and manage parameters. Each parameter is represented by a Parameter object (e.g., SqlParameter, OracleParameter).

When adding a parameter, you typically specify:

• Parameter Name: The name of the parameter, often prefixed with a symbol like '@' (for SQL Server) or ':' (for Oracle).
• Data Type: The database data type of the parameter.
• Size: The maximum size of the data for string types.
• Direction: Whether the parameter is for input, output, or both.
• Value: The actual data value for the parameter.

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

public class CustomerDataAccess
{
    private string connectionString = "YourConnectionStringHere";

    public string GetCustomerName(int customerId)
    {
        string customerName = null;
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "SELECT CustomerName FROM Customers WHERE CustomerID = @CustomerID";

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                // Add the parameter
                SqlParameter customerIdParam = new SqlParameter("@CustomerID", SqlDbType.Int);
                customerIdParam.Value = customerId;
                command.Parameters.Add(customerIdParam);

                connection.Open();
                object result = command.ExecuteScalar(); // ExecuteScalar returns the first column of the first row

                if (result != null)
                {
                    customerName = result.ToString();
                }
            }
        }
        return customerName;
    }
}
                

Parameter Naming Conventions

Different database providers use different conventions for parameter names. It's crucial to match these conventions:

• SQL Server: Typically uses a prefix like @ (e.g., @CustomerID).
• Oracle: Typically uses a prefix like : (e.g., :p_customer_id).
• MySQL: Can use ? placeholders or named parameters with a prefix like @.

It's generally recommended to use named parameters as they are more readable and less prone to ordering errors, especially when dealing with multiple parameters.

Output Parameters and Return Values

Stored procedures can also use output parameters and return values to pass information back to the application. These are configured using the Direction property of the Parameter object.

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

public class StoredProcedureExample
{
    private string connectionString = "YourConnectionStringHere";

    public string GetCustomerNameFromSP(int customerId)
    {
        string customerName = null;
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand("GetCustomerNameSP", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                // Input parameter
                command.Parameters.AddWithValue("@CustomerID", customerId);

                // Output parameter
                SqlParameter outputParam = new SqlParameter("@CustomerName", SqlDbType.NVarChar, 100);
                outputParam.Direction = ParameterDirection.Output;
                command.Parameters.Add(outputParam);

                connection.Open();
                command.ExecuteNonQuery(); // ExecuteNonQuery for stored procedures that don't return a result set

                customerName = outputParam.Value.ToString();
            }
        }
        return customerName;
    }
}
                

Best Practices

• Always use parameterized queries to prevent SQL injection.
• Dispose of Command and Connection objects properly using using statements.
• Specify the correct CommandType.
• Use appropriate SqlDbType for parameters to ensure data integrity and performance.
• For stored procedures, explicitly set CommandType to StoredProcedure.