Data Commands in ADO.NET

Data commands are fundamental to interacting with data sources in ADO.NET. They represent SQL statements or stored procedures that are executed against a database.

The DbCommand Class

The abstract base class DbCommand provides a common interface for command objects. Concrete implementations are provided by specific data providers, such as SqlCommand for SQL Server, OracleCommand for Oracle, and MySql.Data.MySqlClient.MySqlCommand for MySQL.

Key Properties:

• CommandText: A string containing the SQL statement or stored procedure name.
• CommandType: An enumeration value indicating how the CommandText should be interpreted (e.g., Text, StoredProcedure, TableDirect).
• Connection: A reference to the DbConnection object used to execute the command.
• Parameters: A collection of DbParameter objects that represent parameters passed to the command.
• Transaction: A reference to the DbTransaction object within which the command is executed.

Key Methods:

• ExecuteNonQuery(): Executes a Transact-SQL statement that returns no rows (e.g., INSERT, UPDATE, DELETE). Returns the number of rows affected.
• ExecuteReader(): Executes a query and returns a DbDataReader object.
• ExecuteScalar(): Executes a query and returns the first column of the first row in the result set. Useful for retrieving single values (e.g., COUNT(*)).
• ExecuteResultSet(): (Specific to some providers) Executes a stored procedure that returns multiple result sets.

Working with SQL Commands

Here's a typical workflow for using a DbCommand:

1. Create a DbConnection object and open it.
2. Create a DbCommand object, setting its CommandText and Connection properties.
3. Optionally, set the CommandType and add parameters.
4. Execute the command using one of the Execute... methods.
5. Process the results or the number of rows affected.
6. Close the connection.

Example: Inserting Data

using System.Data;
using System.Data.SqlClient; // Example for SQL Server

// ...

using (SqlConnection connection = new SqlConnection("Your_Connection_String"))
{
    connection.Open();

    string sql = "INSERT INTO Products (ProductName, Price) VALUES (@Name, @Price)";

    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        // Add parameters to prevent SQL injection
        command.Parameters.AddWithValue("@Name", "Example Product");
        command.Parameters.AddWithValue("@Price", 19.99);

        int rowsAffected = command.ExecuteNonQuery();

        Console.WriteLine($"{rowsAffected} row(s) affected.");
    }
}
            

Example: Retrieving a Single Value

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

// ...

using (SqlConnection connection = new SqlConnection("Your_Connection_String"))
{
    connection.Open();

    string sql = "SELECT COUNT(*) FROM Customers";

    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        object result = command.ExecuteScalar();
        if (result != null)
        {
            int customerCount = Convert.ToInt32(result);
            Console.WriteLine($"Total customers: {customerCount}");
        }
    }
}
            

Stored Procedures

ADO.NET fully supports executing stored procedures. Set the CommandType to CommandType.StoredProcedure and provide the name of the stored procedure in CommandText. Parameters are still essential for passing arguments to stored procedures.

Example: Executing a Stored Procedure

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

// ...

using (SqlConnection connection = new SqlConnection("Your_Connection_String"))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand("GetProductDetails", connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@ProductID", 123);

        // You would typically use ExecuteReader() or ExecuteNonQuery() here
        // depending on what the stored procedure does.
        // For demonstration, let's assume it returns data.
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"Name: {reader["ProductName"]}, Price: {reader["Price"]}");
            }
        }
    }
}