MSDN Documentation

Commands and Parameters in ADO.NET

This section delves into the core components of executing commands against a data source in ADO.NET: the Command object and its associated Parameter objects.

The Command Object

The Command object represents a SQL statement or stored procedure to be executed against a data source. ADO.NET provides provider-specific implementations for commands, such as SqlCommand for SQL Server, OleDbCommand for OLE DB, and OdbcCommand for ODBC.

Key Properties of a Command Object:

• CommandText: A string containing the SQL statement or the name of the stored procedure.
• CommandType: Specifies how the CommandText property is to be interpreted. Common values include Text (for SQL statements), StoredProcedure, and TableDirect.
• Connection: An instance of a connection object (e.g., SqlConnection) that establishes a link to the data source.
• Parameters: A collection of Parameter objects used to pass values to the command.
• Transaction: The transaction within which the command executes.

Executing Commands:

Commands can be executed using several methods, depending on the expected outcome:

• ExecuteNonQuery(): Executes a command that returns no rows, such as an INSERT, UPDATE, or DELETE statement. It returns the number of rows affected.
• ExecuteReader(): Executes a command that returns a result set and returns a DataReader object (e.g., SqlDataReader) to iterate through the rows.
• ExecuteScalar(): Executes a command that returns a single value (e.g., the result of an aggregate function like COUNT(*)) and returns the first column of the first row in the result set.
• ExecuteXmlReader(): Executes a command that returns XML data and returns an XmlReader object.

Parameters

Using parameters is crucial for security (preventing SQL injection) and performance (allowing the database to cache execution plans). Parameters are represented by the Parameter object (e.g., SqlParameter).

Key Properties of a Parameter Object:

• ParameterName: The name of the parameter, typically prefixed with '@' or '?'.
• DbType: The data type of the parameter in the data source.
• Direction: Indicates whether the parameter is an input, output, input/output, or return value parameter.
• Value: The actual value to be passed to the parameter.
• Size: The maximum size of the parameter value.

Adding Parameters:

Parameters are added to the Parameters collection of the Command object.

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

// ...

using (SqlConnection connection = new SqlConnection("YourConnectionString"))
{
    connection.Open();
    string sql = "SELECT CustomerName FROM Customers WHERE City = @City";
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        // Add a parameter
        command.Parameters.AddWithValue("@City", "London");

        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine(reader["CustomerName"]);
            }
        }
    }
}
                

Output Parameters

Output parameters allow stored procedures to return values back to the application. They are declared with Direction.Output.

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

// ...

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

        // Input parameter
        command.Parameters.AddWithValue("@City", "Berlin");

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

        command.ExecuteNonQuery();

        int customerCount = (int)outputParameter.Value;
        Console.WriteLine($"Number of customers in Berlin: {customerCount}");
    }
}
                

Best Practices

• Always use parameterized queries to prevent SQL injection.
• Set the appropriate CommandType.
• Close connections and dispose of command and reader objects properly using using statements or explicit Dispose() calls.
• Choose the most efficient Execute... method for your needs.