MSDN Documentation

Command Objects in ADO.NET

Command objects are fundamental to interacting with databases in ADO.NET. They represent a SQL statement or a stored procedure that you can execute against a data source.

Core Command Objects

ADO.NET provides several classes for command objects, depending on the data provider you are using. The most common ones are:

• System.Data.SqlClient.SqlCommand: For SQL Server
• System.Data.OleDb.OleDbCommand: For OLE DB data sources (e.g., Access, Oracle via OLE DB)
• System.Data.Odbc.OdbcCommand: For ODBC data sources
• System.Data.OracleClient.OracleCommand: For Oracle (though often replaced by provider-specific libraries)

While the specific classes differ, their core functionality and properties are largely the same.

Key Properties of Command Objects

Here are some of the most important properties you'll use with command objects:

CommandText

This property holds the SQL statement, stored procedure name, or table name to be executed.

// Example: SQL SELECT statement
command.CommandText = "SELECT CustomerID, CompanyName FROM Customers";

// Example: Stored Procedure
command.CommandText = "usp_GetOrdersByCustomerID";
command.CommandType = CommandType.StoredProcedure;

CommandType

This enumeration specifies how the CommandText should be interpreted.

• CommandType.Text: The CommandText is a SQL statement or a command that is not a stored procedure. (Default)
• CommandType.StoredProcedure: The CommandText is the name of a stored procedure.
• CommandType.TableDirect: The CommandText is the name of a table. This is less common.

Connection

This property holds the DbConnection object that the command will execute against. You must establish a connection before executing a command.

SqlConnection connection = new SqlConnection("YourConnectionString");
SqlCommand command = new SqlCommand("SELECT * FROM Products", connection);
connection.Open();
// ... execute command ...
connection.Close();

Parameters

This property is a collection of DbParameter objects that represent the parameters for the command, particularly useful for stored procedures and parameterized queries to prevent SQL injection.

// Example with a parameter
SqlCommand command = new SqlCommand("SELECT * FROM Orders WHERE CustomerID = @CustomerID", connection);
command.Parameters.AddWithValue("@CustomerID", "ALFKI");

Executing Commands

Command objects have methods to execute them:

ExecuteNonQuery()

Executes a command that does not return a result set, such as an INSERT, UPDATE, or DELETE statement. It returns the number of rows affected.

int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine($"Rows affected: {rowsAffected}");

ExecuteReader()

Executes a command that returns a result set (like a SELECT statement). It returns a DbDataReader object, which provides a forward-only, read-only stream of data.

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

ExecuteScalar()

Executes a command that returns a single value (e.g., a count or an aggregate function). It returns the value of the first column of the first row in the result set.

int customerCount = (int)command.ExecuteScalar();
Console.WriteLine($"Total customers: {customerCount}");

Best Practices

• Always use parameterized queries (Parameters collection) to prevent SQL injection vulnerabilities.
• Set the CommandTimeout property if your queries might take a long time.
• Use using statements for SqlConnection and SqlCommand to ensure they are properly disposed of.
• Specify the correct CommandType for clarity and performance.

"The greatest glory in living lies not in never falling, but in rising every time we fall." - Nelson Mandela