MSDN Documentation

Executing Commands with ADO.NET

This section delves into the core mechanisms for executing commands against a data source using ADO.NET. ADO.NET provides a rich set of classes to interact with databases, and command execution is a fundamental aspect of this interaction.

Overview of Command Execution

The primary class for executing commands is DbCommand (or its provider-specific implementations like SqlCommand for SQL Server or MySqlCommand for MySQL). A DbCommand object represents a Transact-SQL statement or a stored procedure to be executed against a data source.

Key components involved in command execution:

• DbCommand: Represents the command to be executed.
• DbConnection: Represents an open connection to a data source.
• DbParameter: Represents a parameter for a command.
• DbDataReader: Provides a way to read a forward-only stream of rows from a data source.
• DataTable, DataSet: In-memory cache of data.

Creating and Configuring a DbCommand

You typically create a DbCommand object associated with an existing DbConnection. You can set the CommandText property to the SQL statement or stored procedure name, and the CommandType property to specify whether it's a text command, a stored procedure, or a table name.

using System.Data.SqlClient; // For SQL Server

// Assume 'connection' is an open SqlConnection object
using (SqlCommand command = new SqlCommand("SELECT * FROM Customers WHERE City = @City", connection))
{
    command.CommandType = System.Data.CommandType.Text;
    // ... configure parameters and execute ...
}
        

Using Parameters

Using parameters is crucial for security (preventing SQL injection) and performance. Parameters allow you to pass values to your SQL statements or stored procedures cleanly.

You can add parameters to a command's Parameters collection:

// Continuing from the previous example
using (SqlCommand command = new SqlCommand("SELECT * FROM Customers WHERE City = @City", connection))
{
    command.CommandType = System.Data.CommandType.Text;

    // Add a parameter
    command.Parameters.AddWithValue("@City", "London");

    // Or specify type and size explicitly for better control
    // SqlParameter cityParam = new SqlParameter("@City", System.Data.SqlDbType.NVarChar, 50);
    // cityParam.Value = "London";
    // command.Parameters.Add(cityParam);

    // ... execute the command ...
}
        

Executing Commands That Return Data

For commands that retrieve data (e.g., SELECT statements), you can use ExecuteReader() to obtain a DbDataReader.

using (SqlCommand command = new SqlCommand("SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country", connection))
{
    command.Parameters.AddWithValue("@Country", "UK");

    using (SqlDataReader reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
        }
    }
}
        

Alternatively, for simpler scenarios or when you need the entire result set at once, you can use ExecuteScalar() (for a single value) or Fill() a DataTable or DataSet.

// ExecuteScalar for a single value (e.g., count)
int customerCount = (int)command.ExecuteScalar();

// Fill a DataTable
SqlDataAdapter adapter = new SqlDataAdapter(command);
DataTable dataTable = new DataTable();
adapter.Fill(dataTable);
        

Executing Commands That Do Not Return Data

For commands that modify data (e.g., INSERT, UPDATE, DELETE) or execute stored procedures that don't return a result set, use ExecuteNonQuery(). This method returns the number of rows affected by the command.

using (SqlCommand command = new SqlCommand("UPDATE Products SET UnitPrice = UnitPrice * 1.10 WHERE CategoryID = @CategoryID", connection))
{
    command.Parameters.AddWithValue("@CategoryID", 1);
    int rowsAffected = command.ExecuteNonQuery();
    Console.WriteLine($"{rowsAffected} rows updated.");
}
        

Executing Stored Procedures

To execute a stored procedure, set the CommandType to CommandType.StoredProcedure and set the CommandText to the name of the stored procedure.

using (SqlCommand command = new SqlCommand("usp_GetCustomerOrders", connection))
{
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@CustomerID", "ALFKI");

    using (SqlDataReader reader = command.ExecuteReader())
    {
        // Process results
    }
}
        

Managing Command Execution Lifecycle

Ensure that connections are properly opened and closed, and that command and reader objects are disposed of using using statements to release resources efficiently.