ADO.NET DataCommand Concepts

The DbCommand object in ADO.NET is a core component used to execute SQL statements or stored procedures against a database. It represents a command that you want to send to a data source.

Overview of DbCommand

The DbCommand object allows you to:

Key Properties

The DbCommand class has several important properties:

Property Description
CommandText Gets or sets the SQL statement or stored procedure to execute.
CommandType Gets or sets a value indicating how to interpret the CommandText property. Possible values include Text, StoredProcedure, and TableDirect.
Connection Gets or sets the DbConnection object for the command.
Parameters Gets a DbParameterCollection object that contains all parameters associated with this command.
Transaction Gets or sets the DbTransaction within which the command executes.
CommandTimeout Gets or sets the wait time in seconds before the command times out.

Key Methods

The DbCommand class provides the following methods for executing commands:

Method Description
ExecuteNonQuery() Executes a Transact-SQL statement that does not return a result set. Typically used for INSERT, UPDATE, DELETE statements. Returns the number of rows affected.
ExecuteReader() Executes the command and returns a DbDataReader object. Used for SELECT statements.
ExecuteScalar() Executes the command and returns the first column of the first row in the result set. Typically used for aggregate functions (e.g., COUNT, SUM).
ExecuteXmlReader() Executes the command and returns an XmlReader object.
Prepare() Prepares the command for execution. This can improve performance if the command is executed multiple times.
Cancel() Attempts to cancel the execution of a command.

Working with Parameters

Using parameters is crucial for security (preventing SQL injection) and performance. You add parameters to the Parameters collection.

Tip: Always use parameterized queries instead of concatenating strings to build SQL commands.

Example: Executing a SELECT statement with parameters

This example demonstrates how to create a DbCommand, add parameters, and execute it to retrieve data using a DbDataReader.


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

// Assuming you have an open DbConnection 'connection'
// DbConnection connection = new SqlConnection("Your_Connection_String");
// connection.Open();

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

using (DbCommand command = connection.CreateCommand())
{
    command.CommandText = sql;
    command.CommandType = CommandType.Text;

    // Add parameters
    DbParameter paramCountry = command.CreateParameter();
    paramCountry.ParameterName = "@Country";
    paramCountry.Value = "USA";
    command.Parameters.Add(paramCountry);

    DbParameter paramCity = command.CreateParameter();
    paramCity.ParameterName = "@City";
    paramCity.Value = "New York";
    command.Parameters.Add(paramCity);

    try
    {
        using (DbDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
            }
        }
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Error executing command: {ex.Message}");
    }
}

Example: Executing an INSERT statement

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


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

using (DbCommand command = connection.CreateCommand())
{
    command.CommandText = insertSql;
    command.CommandType = CommandType.Text;

    // Add parameters
    DbParameter paramName = command.CreateParameter();
    paramName.ParameterName = "@Name";
    paramName.Value = "New Gadget";
    command.Parameters.Add(paramName);

    DbParameter paramPrice = command.CreateParameter();
    paramPrice.ParameterName = "@Price";
    paramPrice.Value = 49.99;
    command.Parameters.Add(paramPrice);

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

Command Types

The CommandType enum specifies how the CommandText should be interpreted:

Provider-Specific Command Objects

While DbCommand is the abstract base class, you will typically use provider-specific implementations:

These classes inherit from DbCommand and provide implementations for the underlying data access operations.