ADO.NET Commands

Commands are fundamental to interacting with data sources in ADO.NET. They represent instructions that you send to a data provider to execute operations such as querying data, inserting, updating, or deleting records, or calling stored procedures.

Core Concepts

The primary classes for working with commands in ADO.NET are:

DbCommand (abstract base class)
SqlCommand (for SQL Server)
OleDbCommand (for OLE DB providers)
OdbcCommand (for ODBC providers)
And provider-specific implementations for other data sources.

A command object typically has the following key properties:

CommandText: A string containing the SQL statement or the name of the stored procedure to execute.
Connection: A reference to the DbConnection object that the command will execute against.
CommandType: An enumeration that specifies how the CommandText property should be interpreted. Common values include:
- 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.
Parameters: A collection of DbParameter objects used to pass values into or out of SQL statements and stored procedures. This is crucial for security and performance.

Executing Commands

Commands are executed using methods provided by the DbCommand object, typically invoked on the command's associated connection:

ExecuteNonQuery(): Used for SQL statements that do not return a result set, such as INSERT, UPDATE, DELETE, or CREATE TABLE. It returns the number of rows affected by the operation.
ExecuteReader(): Used for SQL statements that return a single result set. It returns a DbDataReader object, which provides a forward-only, read-only stream of data.
ExecuteScalar(): Used for SQL statements that return a single value (e.g., the result of an aggregate function like COUNT(*)). It returns the first column of the first row in the result set.
ExecuteXmlReader(): Used to execute a SQL query that returns an XML result set.

Using Parameters

Employing parameters is a best practice for several reasons:

Security: Prevents SQL injection attacks by ensuring that input is treated as data, not executable code.
Performance: Allows the database to cache execution plans for parameterized queries.
Readability: Makes SQL statements cleaner and easier to understand.

Best Practice: Use Parameters

Always use parameterized queries instead of concatenating user input directly into SQL strings.

Example: Using `SqlCommand` with Parameters

using System;
using System.Data;
using Microsoft.Data.SqlClient; // Or System.Data.SqlClient

public class CommandExample
{
    private string connectionString = "Your_Connection_String_Here";

    public void AddNewProduct(string productName, decimal price)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string sql = "INSERT INTO Products (ProductName, Price) VALUES (@Name, @Price)";

            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                // Add parameters to prevent SQL injection and improve performance
                command.Parameters.AddWithValue("@Name", productName);
                command.Parameters.AddWithValue("@Price", price);

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

    public object GetProductCount()
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string sql = "SELECT COUNT(*) FROM Products";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                connection.Open();
                // ExecuteScalar returns the first column of the first row
                return command.ExecuteScalar();
            }
        }
    }
}

Stored Procedures

When CommandType is set to StoredProcedure, the CommandText property should contain the name of the stored procedure. Parameters are used similarly to regular SQL statements, but you might also need to handle output parameters and return values.

Example: Calling a Stored Procedure

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

public class StoredProcedureExample
{
    private string connectionString = "Your_Connection_String_Here";

    public void GetCustomerOrderCount(int customerId, out int orderCount)
    {
        orderCount = 0;
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand("sp_GetCustomerOrderCount", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                // Input parameter
                command.Parameters.AddWithValue("@CustomerID", customerId);

                // Output parameter
                SqlParameter outputParam = new SqlParameter("@OrderCount", SqlDbType.Int);
                outputParam.Direction = ParameterDirection.Output;
                command.Parameters.Add(outputParam);

                try
                {
                    connection.Open();
                    command.ExecuteNonQuery();

                    // Retrieve the output parameter value
                    orderCount = (int)outputParam.Value;
                    Console.WriteLine($"Customer {customerId} has {orderCount} orders.");
                }
                catch (SqlException ex)
                {
                    Console.WriteLine($"Error executing stored procedure: {ex.Message}");
                }
            }
        }
    }
}

Important Considerations

Always close your connections when you are finished with them, preferably using using statements to ensure proper disposal.
Handle exceptions gracefully to manage potential database errors.
Understand the different CommandType options and choose the most appropriate one for your operation.
For complex transactions, consider using TransactionScope or explicit transaction management on the connection.

Commands are a cornerstone of ADO.NET, enabling robust and efficient data manipulation. Mastering their usage is key to building data-driven applications.

Last Updated: October 26, 2023