ADO.NET Command Objects

ADO.NET Command objects provide a way to execute SQL statements or stored procedures against a database. They are central to interacting with data sources in ADO.NET.

Introduction to Command Objects

A Command object represents a Transact-SQL statement or a stored procedure to be executed against a data source. When you use a Command object, you can specify its text, its parameters, and the type of command it is.

Key Properties of Command Objects

• CommandText: This property holds the SQL statement or stored procedure name to be executed.
• CommandType: This property specifies how the CommandText should be interpreted (e.g., Text, StoredProcedure, TableDirect).
• Connection: A reference to the Connection object that the command will execute against.
• Parameters: A collection of DbParameter objects that represent parameters for the command.
• Transaction: The transaction within which the command should be executed.

Common Command Object Implementations

ADO.NET provides specific implementations of the abstract DbCommand class for various data providers:

• SqlCommand for SQL Server
• OracleCommand for Oracle
• OleDbCommand for OLE DB providers
• OdbcCommand for ODBC drivers

Executing Commands

Command objects can be used to perform various data operations:

• Executing Queries that Return Data: Use methods like ExecuteReader() to retrieve data as a DataReader, or ExecuteDataset() (with a DataAdapter) to populate a DataSet.
• Executing Non-Query Statements: Use ExecuteNonQuery() for commands that do not return rows, such as INSERT, UPDATE, DELETE, or CREATE TABLE statements. This method returns the number of rows affected.
• Executing Scalar Queries: Use ExecuteScalar() to retrieve a single value from a query, typically the first column of the first row of the result set.

Example: Using SqlCommand with ExecuteNonQuery

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

public class CommandExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        string sql = "INSERT INTO Products (ProductName, UnitPrice) VALUES (@Name, @Price)";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@Name", "Chai");
                command.Parameters.AddWithValue("@Price", 18.00);

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

Example: Using SqlCommand with ExecuteReader

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

public class CommandReaderExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
        string sql = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                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 in USA.");
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error: {ex.Message}");
                }
            }
        }
    }
}
                

Command Parameters

Using parameters is crucial for security (preventing SQL injection) and performance (allowing query plan caching). Parameters are added to the Command.Parameters collection.

Stored Procedures

Command objects can also execute stored procedures. To do this, set the CommandType property to CommandType.StoredProcedure and set the CommandText to the name of the stored procedure. Parameters for stored procedures are handled similarly.

By mastering Command objects, you gain significant control over data manipulation and retrieval within your ADO.NET applications.