MSDN Documentation

Command Objects in ADO.NET

Command objects are fundamental to interacting with data sources in ADO.NET. They represent SQL statements, stored procedures, or other commands that you want to execute against a database. The primary command objects are DbCommand (the abstract base class) and its concrete implementations like SqlCommand for SQL Server, OleDbCommand for OLE DB providers, and OdbcCommand for ODBC providers.

Key Properties of Command Objects

Command objects expose several important properties that control their behavior and execution:

• CommandText: This property holds the SQL statement, stored procedure name, or other command text to be executed.
• Connection: This property specifies the DbConnection object that the command will execute against. The connection must be open before executing the command.
• CommandType: This enumeration determines how the CommandText property is interpreted. The common values are:
  • Text (default): The CommandText is a SQL statement or table name.
  • StoredProcedure: The CommandText is the name of a stored procedure.
  • TableDirect: The CommandText is the name of a table.
• Parameters: This property is a collection of DbParameter objects that represent input, output, or input/output parameters for the command. Using parameters is crucial for security (preventing SQL injection) and performance.
• Transaction: This property allows you to associate the command with an existing transaction.
• CommandTimeout: Specifies the number of seconds to wait for the command to execute before timing out.

Executing Commands

Command objects provide methods to execute various types of commands:

• ExecuteNonQuery(): Used for commands that do not return a result set, such as INSERT, UPDATE, DELETE statements, or CREATE TABLE. It returns the number of rows affected by the command.
• ExecuteReader(): Used for commands that return a result set, like SELECT statements. It returns a DbDataReader object, which allows you to read the results row by row efficiently.
• ExecuteScalar(): Used for commands that return a single value, typically the first column of the first row of a result set (e.g., SELECT COUNT(*) FROM Orders). It returns the value as an object.
• ExecuteXmlReader(): (Specific to SQL Server) Used for commands that return XML results.

Example: Executing a SELECT Statement

Here's a C# example demonstrating how to use a SqlCommand to retrieve data:

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

public class CommandExample
{
    public static void GetCustomerNames(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string sql = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                // Add a parameter to prevent SQL injection
                command.Parameters.AddWithValue("@Country", "USA");

                try
                {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
                            }
                        }
                        else
                        {
                            Console.WriteLine("No customers found for the specified country.");
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"An error occurred: {ex.Message}");
                }
            }
        }
    }
}
            

Best Practices

• Always use parameterized queries (via the Parameters collection) to prevent SQL injection vulnerabilities.
• Use the using statement for DbConnection, DbCommand, and DbDataReader to ensure resources are properly disposed of.
• Set an appropriate CommandTimeout for long-running operations.
• Choose the correct CommandType for your command.

Understanding and effectively using command objects is crucial for building robust and secure data-driven applications with ADO.NET.