ADO.NET Command Object

Introduction to the Command Object

The Command object in ADO.NET is a fundamental component used to execute SQL statements or stored procedures against a data source. It represents a command to be run, such as a query to retrieve data, an update to modify data, or a call to a stored procedure.

Key responsibilities of a Command object include:

• Defining the SQL statement or stored procedure to be executed.
• Associating the command with a specific Connection.
• Managing parameters for the command.
• Returning results, such as a DataReader for queries or the number of rows affected for DML statements.

Core Properties and Methods

Properties

• Connection: Gets or sets the Connection object associated with the command.
• CommandText: Gets or sets the SQL statement or stored procedure name to execute.
• CommandType: Gets or sets a value indicating how the CommandText property is to be interpreted. Common values include Text (default), StoredProcedure, and TableDirect.
• Parameters: Gets a collection of parameters associated with the command.
• Transaction: Gets or sets the Transaction within which the command executes.

Methods

• ExecuteNonQuery(): Executes a Transact-SQL statement that does not return a result set and returns the number of rows affected.
• ExecuteReader(): Executes the CommandText against the Connection and returns a DataReader object.
• ExecuteScalar(): Executes the query and returns the first column of the first row in the result set.
• ExecuteXmlReader(): Executes the CommandText and returns an XmlReader object.
• Prepare(): Creates a precompiled version of the command on the data source.
• Cancel(): Attempts to cancel the execution of a DbCommand.

Working with the Command Object

Creating a Command

You typically create a Command object through a specific data provider's factory, such as SqlCommand for SQL Server or MySqlCommand for MySQL. The command object is usually instantiated with a CommandText and a Connection.

using System;
using System.Data;
using Microsoft.Data.SqlClient; // Or your specific provider

public class CommandExample
{
    public static void ExecuteSimpleQuery(string connectionString)
    {
        string sql = "SELECT COUNT(*) FROM Customers";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                try
                {
                    connection.Open();
                    object result = command.ExecuteScalar();
                    Console.WriteLine($"Number of customers: {result}");
                }
                catch (SqlException ex)
                {
                    Console.WriteLine($"Error: {ex.Message}");
                }
            }
        }
    }
}

Using Parameters

Using parameters is crucial for security (to prevent SQL injection) and performance (as it allows the database to cache execution plans). Parameters are added to the Parameters collection of the Command object.

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

public class ParameterExample
{
    public static void GetDataByCustomerID(string connectionString, int customerId)
    {
        string sql = "SELECT CustomerName, Email FROM Customers WHERE CustomerID = @ID";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                // Add the parameter
                command.Parameters.AddWithValue("@ID", customerId);

                try
                {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.Read())
                        {
                            Console.WriteLine($"Name: {reader["CustomerName"]}, Email: {reader["Email"]}");
                        }
                        else
                        {
                            Console.WriteLine($"Customer with ID {customerId} not found.");
                        }
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine($"Error: {ex.Message}");
                }
            }
        }
    }
}

Executing Stored Procedures

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

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

public class StoredProcExample
{
    public static void UpdateProductPrice(string connectionString, int productId, decimal newPrice)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand("UpdateProductPriceSP", connection)) // Stored procedure name
            {
                command.CommandType = CommandType.StoredProcedure;

                // Add parameters for the stored procedure
                command.Parameters.AddWithValue("@ProductID", productId);
                command.Parameters.AddWithValue("@NewPrice", newPrice);

                try
                {
                    connection.Open();
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"{rowsAffected} row(s) updated.");
                }
                catch (SqlException ex)
                {
                    Console.WriteLine($"Error executing stored procedure: {ex.Message}");
                }
            }
        }
    }
}

Command Types

The CommandType enumeration provides flexibility in how the CommandText is interpreted:

• Text: The CommandText is a SQL statement or stored procedure call.
• StoredProcedure: The CommandText is the name of a stored procedure.
• TableDirect: The CommandText is the name of a table. This is less common and provider-specific.

Best Practices

• Always use parameterized queries to prevent SQL injection vulnerabilities.
• Use using statements for Connection, Command, and DataReader objects to ensure proper resource management (disposal).
• Choose the appropriate Execute method based on the expected return value of the command.
• For performance-critical operations, consider using the Prepare() method if you intend to execute the same command multiple times with different parameters.
• Set the CommandTimeout property if you need to override the default command execution timeout.