ADO.NET Commands

Introduction to ADO.NET Commands

In ADO.NET, the Command object represents a SQL statement, a stored procedure, or a table name that you want to execute against a data source. It's the primary mechanism for interacting with databases to retrieve, modify, or delete data. Commands are typically associated with a Connection object, which establishes the link to the database.

The specific implementation of the Command object varies depending on the data provider you are using (e.g., SqlCommand for SQL Server, OracleCommand for Oracle, MySqlCommand for MySQL). However, the core concepts and usage patterns remain consistent across providers.

The Command Object

The DbCommand class (and its provider-specific descendants like SqlCommand) provides the foundation for executing commands. Key properties include:

Connection: A reference to the DbConnection object that will be used to execute the command.
CommandText: The SQL statement, stored procedure name, or table name to be executed.
CommandType: Specifies how the CommandText property should be interpreted. Common values are:
- Text (default): CommandText is a SQL statement.
- StoredProcedure: CommandText is the name of a stored procedure.
- TableDirect: CommandText is the name of a table.
Parameters: A collection of DbParameter objects that represent input, output, and return value parameters for the command.
Transaction: Specifies the DbTransaction object within which the command should be executed.

The DbCommand object also offers methods for executing the command and retrieving results:

ExecuteNonQuery(): Executes a Transact-SQL statement and returns the number of rows affected. This method is typically used for INSERT, UPDATE, and DELETE statements.
ExecuteReader(): Executes the command and returns a DbDataReader object, which can be used to read the results row by row.
ExecuteScalar(): Executes the command and returns the value of the first column of the first row in the result set. This is useful for retrieving a single value, such as a count or an ID.
ExecuteXmlReader(): Executes the command and returns an XmlReader object.

Executing Commands

Here's a typical workflow for executing a command:

Create a DbConnection object and open it.
Create a DbCommand object, setting its Connection and CommandText properties.
Optionally, configure the CommandType and add Parameters.
Call the appropriate execution method (e.g., ExecuteNonQuery(), ExecuteReader()).
Process the results.
Close the connection.

It's highly recommended to use using statements for both DbConnection and DbCommand objects to ensure proper resource management and that connections are closed and disposed of automatically, even if exceptions occur.

Best Practice: Always use parameterized queries to prevent SQL injection vulnerabilities.

Example: Executing a Simple Query


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

public class CommandExample
{
    public void GetCustomerName(int customerId)
    {
        string connectionString = "Your_Connection_String_Here";
        string query = "SELECT CompanyName FROM Customers WHERE CustomerID = @CustomerID";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                // Add parameter to prevent SQL injection
                command.Parameters.AddWithValue("@CustomerID", customerId);

                try
                {
                    connection.Open();
                    object result = command.ExecuteScalar(); // Returns the first column of the first row

                    if (result != null)
                    {
                        Console.WriteLine($"Customer Name: {result}");
                    }
                    else
                    {
                        Console.WriteLine($"Customer with ID {customerId} not found.");
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine($"An error occurred: {ex.Message}");
                }
            }
        }
    }
}

Command Parameters

Parameters are crucial for security and flexibility. They allow you to pass values to your SQL statements without concatenating strings, thus preventing SQL injection attacks and improving performance by allowing the database to cache execution plans.

You add parameters to the Parameters collection of the DbCommand object. Each parameter has properties like:

ParameterName: The name of the parameter (e.g., @CustomerID).
DbType: The data type of the parameter on the database.
Size: The maximum size of the parameter value (important for string types).
Direction: Indicates whether the parameter is an input, output, or return value parameter.

Tip: Use AddWithValue() for convenience when the type inference is reliable, but for explicit control, create a DbParameter object (e.g., SqlParameter) and explicitly set its type and value.

Example: Using Output Parameters


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

public class CommandParameterExample
{
    public int AddProduct(string productName, decimal price)
    {
        string connectionString = "Your_Connection_String_Here";
        // Assuming a stored procedure that returns the new product ID
        string storedProcName = "sp_AddProduct";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(storedProcName, connection))
            {
                command.CommandType = CommandType.StoredProcedure; // Explicitly set to StoredProcedure

                command.Parameters.AddWithValue("@ProductName", productName);
                command.Parameters.AddWithValue("@Price", price);

                // Define an output parameter to get the generated ProductID
                SqlParameter outputParameter = new SqlParameter("@NewProductID", SqlDbType.Int)
                {
                    Direction = ParameterDirection.Output
                };
                command.Parameters.Add(outputParameter);

                try
                {
                    connection.Open();
                    command.ExecuteNonQuery(); // Execute the stored procedure

                    // Retrieve the output parameter value
                    int newProductId = (int)outputParameter.Value;
                    return newProductId;
                }
                catch (SqlException ex)
                {
                    Console.WriteLine($"An error occurred: {ex.Message}");
                    return -1; // Indicate failure
                }
            }
        }
    }
}

Executing Stored Procedures

Stored procedures are pre-compiled SQL statements stored on the database server. They offer benefits like encapsulation, security, and performance. To execute a stored procedure using ADO.NET:

Set the CommandType property of the DbCommand object to CommandType.StoredProcedure.
Set the CommandText property to the name of the stored procedure.
Add any necessary input and output parameters.
Use ExecuteNonQuery(), ExecuteReader(), or ExecuteScalar() as appropriate.

The example in the "Command Parameters" section already demonstrates executing a stored procedure with output parameters.

Conclusion

The DbCommand object is a fundamental component of ADO.NET for interacting with databases. By understanding its properties and methods, and by employing best practices like using parameters and using statements, you can build robust and secure data access solutions. Always refer to the specific data provider documentation for detailed information on provider-specific features and types.

.NET Documentation