Executing Commands with ADO.NET

ADO.NET provides classes to execute SQL commands against a database and retrieve the results. The primary class for executing commands is DbCommand (or its specific provider implementation like SqlCommand for SQL Server).

Executing a command typically involves the following steps:

1. Creating a DbCommand object.
2. Setting the command text (the SQL statement or stored procedure name).
3. Associating the command with a DbConnection.
4. Optionally, adding parameters to the command.
5. Executing the command.
6. Processing the results.

Creating and Configuring a Command

You can create a DbCommand object directly or by using the CreateCommand() method of a DbConnection object. The latter is generally preferred as it ensures compatibility with the connection's provider.

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

// Assuming 'connection' is an open SqlConnection object
string sql = "SELECT CustomerID, CompanyName FROM Customers WHERE City = @City";
using (SqlCommand command = new SqlCommand(sql, connection))
{
    // Configure command properties here
}
                

CommandText Property

The CommandText property holds the SQL statement or the name of the stored procedure to be executed.

CommandType Property

The CommandType enumeration specifies how the CommandText should be interpreted:

• Text (default): The CommandText is a SQL statement.
• StoredProcedure: The CommandText is the name of a stored procedure.
• TableDirect: The CommandText is the name of a table. (Less commonly used).

// For a stored procedure
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "GetCustomerOrders";
                

Executing Commands and Retrieving Data

ADO.NET offers several methods on the DbCommand object to execute commands:

1. ExecuteReader()

Use ExecuteReader() to retrieve a data reader (DbDataReader) that allows you to read rows from the result set. This is the most efficient way to retrieve large amounts of data row by row.

string sql = "SELECT ProductID, ProductName, UnitPrice FROM Products WHERE Discontinued = 0";
using (SqlCommand command = new SqlCommand(sql, connection))
{
    using (SqlDataReader reader = command.ExecuteReader())
    {
        if (reader.HasRows)
        {
            while (reader.Read())
            {
                int productId = reader.GetInt32(0);
                string productName = reader.GetString(1);
                decimal unitPrice = reader.GetDecimal(2);
                Console.WriteLine($"ID: {productId}, Name: {productName}, Price: {unitPrice:C}");
            }
        }
    }
}
                

2. ExecuteNonQuery()

Use ExecuteNonQuery() to execute SQL statements that do not return a result set, such as INSERT, UPDATE, DELETE, or CREATE TABLE statements. It returns the number of rows affected by the command.

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

3. ExecuteScalar()

Use ExecuteScalar() to retrieve a single value (the first column of the first row) from a query. This is useful for getting aggregate values like counts or sums.

string sql = "SELECT COUNT(*) FROM Orders";
using (SqlCommand command = new SqlCommand(sql, connection))
{
    int orderCount = (int)command.ExecuteScalar();
    Console.WriteLine($"Total number of orders: {orderCount}");
}
                

Using Parameters

It is highly recommended to use parameters to pass values into your SQL commands. This prevents SQL injection vulnerabilities and improves performance by allowing the database to cache execution plans.

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

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

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

You can also explicitly define the parameter type and size for better control and performance:

string sql = "INSERT INTO Employees (FirstName, LastName, HireDate) VALUES (@FirstName, @LastName, @HireDate)";
using (SqlCommand command = new SqlCommand(sql, connection))
{
    command.Parameters.Add("@FirstName", SqlDbType.NVarChar, 50).Value = "Jane";
    command.Parameters.Add("@LastName", SqlDbType.NVarChar, 50).Value = "Doe";
    command.Parameters.Add("@HireDate", SqlDbType.Date).Value = new DateTime(2023, 10, 27);

    int rowsAffected = command.ExecuteNonQuery();
    Console.WriteLine($"Inserted {rowsAffected} row(s).");
}
                

Executing Stored Procedures

To execute a stored procedure, set the CommandType to StoredProcedure and provide the procedure name in CommandText. Parameters are handled the same way as with SQL statements.

using (SqlCommand command = new SqlCommand("AddProductReview", connection))
{
    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.AddWithValue("@ProductID", 15);
    command.Parameters.AddWithValue("@ReviewerName", "John Smith");
    command.Parameters.AddWithValue("@Rating", 5);
    command.Parameters.AddWithValue("@Comments", "Excellent product!");

    // Example of an output parameter
    SqlParameter outputParam = new SqlParameter("@ReviewID", SqlDbType.Int);
    outputParam.Direction = ParameterDirection.Output;
    command.Parameters.Add(outputParam);

    connection.Open(); // Ensure connection is open
    command.ExecuteNonQuery();
    connection.Close();

    int reviewId = (int)outputParam.Value;
    Console.WriteLine($"New review added with ID: {reviewId}");
}