ADO.NET Commands

This document provides an in-depth look at ADO.NET Commands, a fundamental component for interacting with databases. Commands are used to execute SQL statements or stored procedures against a data source.

What are ADO.NET Commands?

An ADO.NET Command object represents a SQL statement or stored procedure that you want to execute against a data source. You can use command objects to perform operations such as retrieving data, inserting, updating, or deleting records, and executing stored procedures.

The primary classes for commands in ADO.NET are:

DbCommand (abstract base class)
SqlCommand (for SQL Server)
OleDbCommand (for OLE DB providers)
OdbcCommand (for ODBC providers)
OracleCommand (for Oracle)
MySqlCommand (for MySQL)

Creating and Executing Commands

To execute a command, you typically follow these steps:

Create a DbConnection object and open the connection.
Create a Command object (e.g., SqlCommand) and associate it with the connection.
Set the CommandText property to the SQL statement or stored procedure name.
Optionally, set the CommandType property (e.g., Text, StoredProcedure, TableDirect).
Add any necessary parameters using the Parameters collection.
Execute the command using methods like ExecuteNonQuery(), ExecuteReader(), or ExecuteScalar().
Close the connection when done.

Example: Executing a SELECT Statement

This example demonstrates how to execute a SELECT statement to retrieve data.


using System;
using System.Data;
using Microsoft.Data.SqlClient; // For SQL Server

public class CommandExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=your_server;Database=your_database;User Id=your_user;Password=your_password;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string sqlQuery = "SELECT CustomerID, CompanyName FROM Customers WHERE City = @City";

            using (SqlCommand command = new SqlCommand(sqlQuery, connection))
            {
                // Add a parameter to prevent SQL injection
                command.Parameters.AddWithValue("@City", "London");

                try
                {
                    connection.Open();
                    Console.WriteLine("Connection opened successfully.");

                    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.");
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error: {ex.Message}");
                }
            }
        }
    }
}

Command Execution Methods

ADO.NET provides several methods to execute commands, each suited for different scenarios:

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 command.
```
int rowsAffected = command.ExecuteNonQuery();
```
ExecuteReader(): Used for SQL statements that return a result set (e.g., SELECT). It returns a DbDataReader object, which provides a forward-only, read-only stream of data.
```
using (DbDataReader reader = command.ExecuteReader()) { ... }
```
ExecuteScalar(): Used for SQL statements that return a single value (e.g., counting rows or retrieving a single column from a single row). It returns the value of the first column of the first row in the result set.
```
object result = command.ExecuteScalar();
```
ExecuteXmlReader(): Used for SQL statements that return XML data.
```
using (XmlReader reader = command.ExecuteXmlReader()) { ... }
```

Example: Executing an INSERT Statement

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


string insertQuery = "INSERT INTO Products (ProductName, UnitPrice) VALUES (@Name, @Price)";
using (var command = new SqlCommand(insertQuery, connection))
{
    command.Parameters.AddWithValue("@Name", "Chai");
    command.Parameters.AddWithValue("@Price", 18.00m);
    int result = command.ExecuteNonQuery();
    Console.WriteLine($"{result} row(s) inserted.");
}

Example: Executing a Stored Procedure

Executing stored procedures is common for complex database operations.


using (var command = new SqlCommand("usp_GetProductCountBySupplier", connection))
{
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@SupplierID", 5);

    object count = command.ExecuteScalar();
    Console.WriteLine($"Product count: {count}");
}

Command Properties

Key properties of a DbCommand object include:

CommandText: The SQL statement or stored procedure to execute.
Connection: The DbConnection to execute the command against.
CommandType: Specifies how the CommandText should be interpreted (Text, StoredProcedure, TableDirect).
CommandTimeout: The time in seconds to wait for the command to execute before timing out. Defaults to 30 seconds.
Parameters: A collection of DbParameter objects that represent the parameters for the command.
Transaction: The DbTransaction within which the command executes.

Properly using command objects, including parameterization and choosing the correct execution method, is crucial for building efficient and secure data access applications in .NET.