ADO.NET Commands

Introduction to Commands

In ADO.NET, the System.Data.Common namespace and its derived namespaces (like System.Data.SqlClient for SQL Server) provide classes for executing commands against databases.

The primary class for representing a command is DbCommand. Specific implementations include SqlCommand for SQL Server, OleDbCommand for OLE DB providers, and OdbcCommand for ODBC drivers.

Key properties and methods of a command object include:

• CommandText: The SQL statement or stored procedure name to execute.
• CommandType: Specifies how the CommandText is interpreted (e.g., Text, StoredProcedure, TableDirect).
• Connection: The DbConnection object to which the command belongs.
• Parameters: A collection of DbParameter objects used to pass values into the command.
• ExecuteReader(): Executes the command and returns a DbDataReader.
• ExecuteNonQuery(): Executes the command and returns the number of rows affected.
• ExecuteScalar(): Executes the command and returns the first column of the first row in the result set.

Executing SQL Statements

The most common use of commands is to execute SQL queries and statements.

Example: Selecting Data

This example demonstrates how to select data from a table using ExecuteReader().

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

public class CommandExamples
{
    public static void SelectData(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string sql = "SELECT CustomerID, CompanyName FROM Customers WHERE City = 'London'";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                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 rows found.");
                    }
                }
            }
        }
    }
}
                

Example: Inserting Data

This example shows how to insert a new record using ExecuteNonQuery().

public static int InsertCustomer(string connectionString, string companyName, string city)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        string sql = "INSERT INTO Customers (CompanyName, City) VALUES (@CompanyName, @City)";
        using (SqlCommand command = new SqlCommand(sql, connection))
        {
            // Using parameters to prevent SQL injection
            command.Parameters.AddWithValue("@CompanyName", companyName);
            command.Parameters.AddWithValue("@City", city);

            connection.Open();
            int rowsAffected = command.ExecuteNonQuery();
            return rowsAffected;
        }
    }
}
                

Using Stored Procedures

Executing stored procedures is often more efficient and secure.

To execute a stored procedure, set the CommandType property to CommandType.StoredProcedure.

public static void ExecuteStoredProcedure(string connectionString, int customerId)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        // Assuming a stored procedure named 'GetCustomerOrders' exists
        using (SqlCommand command = new SqlCommand("GetCustomerOrders", connection))
        {
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@CustomerID", customerId);

            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                // Process results from stored procedure
                while (reader.Read())
                {
                    Console.WriteLine($"Order ID: {reader["OrderID"]}");
                }
            }
        }
    }
}
                

Command Parameters

Using parameters is crucial for security (preventing SQL injection) and performance. Parameters are represented by the DbParameter class and its derived types (e.g., SqlParameter).

Adding Parameters

Parameters can be added to the Parameters collection of a command object. You can specify the parameter name, data type, size, and direction (Input, Output, InputOutput, ReturnValue).

// Example of adding input and output parameters
using (SqlCommand command = new SqlCommand("sp_GetCustomerCountByCity", connection))
{
    command.CommandType = CommandType.StoredProcedure;

    // Input parameter
    command.Parameters.AddWithValue("@CityName", "Berlin");

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

    connection.Open();
    command.ExecuteNonQuery(); // Execute for stored procedures that don't return a result set directly

    int count = (int)outputParameter.Value;
    Console.WriteLine($"Number of customers in Berlin: {count}");
}
                

Command Timeouts

You can set a timeout for how long a command will attempt to execute before timing out.

The CommandTimeout property (in seconds) can be set on the DbCommand object.

SqlCommand command = new SqlCommand(sql, connection);
command.CommandTimeout = 60; // Set timeout to 60 seconds
                

Common Command Objects and Providers

Here's a quick reference for command objects used with different data providers:

Provider	Command Class	Connection Class	Parameter Class
SQL Server	SqlCommand	SqlConnection	SqlParameter
OLE DB	OleDbCommand	OleDbConnection	OleDbParameter
ODBC	OdbcCommand	OdbcConnection	OdbcParameter
Oracle	OracleCommand	OracleConnection	OracleParameter