ADO.NET Commands

This document provides a comprehensive guide to using commands within ADO.NET to interact with your data sources. Commands are fundamental objects that allow you to execute SQL statements or stored procedures against a database.

The DbCommand Object

The DbCommand class (and its provider-specific implementations like SqlCommand for SQL Server, MySqlCommand for MySQL, etc.) is used to represent a command to execute against a data source. Key properties include:

  • CommandText: The SQL statement or stored procedure name to execute.
  • CommandType: Specifies how the CommandText property should be interpreted (e.g., Text for SQL, StoredProcedure).
  • Connection: The DbConnection object to which the command belongs.
  • Parameters: A collection of DbParameter objects used for parameterized queries.
  • Transaction: The DbTransaction object within which the command should be executed.

Executing Commands

Commands can be executed in several ways:

  • ExecuteReader(): Returns a DbDataReader object, ideal for retrieving multiple rows of data.
  • ExecuteNonQuery(): Returns the number of rows affected by the command, typically used for INSERT, UPDATE, DELETE statements.
  • ExecuteScalar(): Returns the value of the first column of the first row of the result set, useful for aggregate functions like COUNT, SUM.

Creating and Executing a Simple Command

Here's a basic example of how to create and execute a SELECT statement:


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

// Assume 'connectionString' is defined elsewhere
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    string sql = "SELECT CustomerID, CompanyName FROM Customers WHERE City = @City";

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

        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
            }
        }
    }
}

Executing Stored Procedures

To execute a stored procedure, set the CommandType to StoredProcedure and provide the procedure name in CommandText.

Important: Always use parameterized queries or stored procedures to prevent SQL injection vulnerabilities. 

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

// Assume 'connectionString' is defined elsewhere
using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand("GetProductsByCategory", connection))
    {
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.AddWithValue("@CategoryID", 5);

        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"Product Name: {reader["ProductName"]}");
            }
        }
    }
}

DbCommandBuilder

For operations that modify data through a DataAdapter, the DbCommandBuilder class can automatically generate INSERT, UPDATE, and DELETE statements. This simplifies data manipulation by deriving the necessary SQL from the SELECT statement.

Example: A SqlCommandBuilder would analyze the results of a SELECT statement and create the corresponding UPDATE statement based on the returned schema.

Best Practices

  • Always use using statements for DbCommand and related objects to ensure proper resource disposal.
  • Employ parameterized queries to protect against SQL injection.
  • Close connections when they are no longer needed, or use connection pooling.
  • Set the appropriate CommandType.
  • Handle exceptions gracefully.