MSDN Documentation

Executing Commands with ADO.NET

Executing commands is a fundamental aspect of interacting with a data source using ADO.NET. This involves sending SQL statements or stored procedure calls to the database and retrieving the results. ADO.NET provides robust mechanisms for executing commands efficiently and securely.

The DbCommand Object

The core object for executing commands in ADO.NET is the DbCommand class (or its specific provider-derived types like SqlCommand for SQL Server or MySqlCommand for MySQL). A DbCommand object represents a Transact-SQL statement or stored procedure to execute against a data source.

Key properties of a DbCommand include:

• CommandText: A string containing the SQL statement or stored procedure name.
• CommandType: An enumeration that specifies how to interpret the CommandText (e.g., Text, StoredProcedure, TableDirect).
• Connection: A reference to the DbConnection object that represents the connection to the data source.
• Parameters: A collection of DbParameter objects that represent parameters passed to the command.

Executing Non-Query Commands

For commands that do not return a result set, such as INSERT, UPDATE, DELETE, or CREATE TABLE statements, you use the ExecuteNonQuery() method of the DbCommand object. This method returns the number of rows affected by the execution of the command.

Example:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string sql = "INSERT INTO Products (ProductName, UnitPrice) VALUES (@Name, @Price)";
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.AddWithValue("@Name", "New Gadget");
        command.Parameters.AddWithValue("@Price", 19.99);

        int rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine($"Number of rows affected: {rowsAffected}");
    }
}
                

Executing Queries that Return a Single Value

If your command is expected to return a single scalar value (e.g., a count, a sum, or a single column from a single row), you can use the ExecuteScalar() method. This method returns the first column of the first row in the result set. If the result set contains no rows, or if the first column is a database null, null is returned.

Example:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string sql = "SELECT COUNT(*) FROM Customers";
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        object result = command.ExecuteScalar();
        if (result != null)
        {
            int customerCount = Convert.ToInt32(result);
            Console.WriteLine($"Total customers: {customerCount}");
        }
    }
}
                

Executing Queries that Return a Data Reader

For commands that return multiple rows and columns (i.e., a result set), the most efficient way to retrieve the data is by using the ExecuteReader() method. This method returns a DbDataReader object, which provides a forward-only, read-only stream of data from the data source.

Example:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    string sql = "SELECT ProductID, ProductName, UnitPrice FROM Products WHERE UnitPrice > @MinPrice";
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.AddWithValue("@MinPrice", 50.00);
        using (SqlDataReader reader = command.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine($"{reader["ProductName"]} - ${reader.GetDecimal(reader.GetOrdinal("UnitPrice"))}");
                }
            }
            else
            {
                Console.WriteLine("No products found.");
            }
        }
    }
}
                

Executing Stored Procedures

Executing stored procedures is a common practice for encapsulating database logic. To execute a stored procedure, you set the CommandType property to CommandType.StoredProcedure and set the CommandText property to the name of the stored procedure.

Example:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (SqlCommand command = new SqlCommand("GetCustomerOrders", connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        command.Parameters.AddWithValue("@CustomerID", "ALFKI");

        using (SqlDataReader reader = command.ExecuteReader())
        {
            // Process results from stored procedure
        }
    }
}
                

Parameterization and Security

It is crucial to use parameterized queries to prevent SQL injection attacks. ADO.NET's parameterization feature allows you to safely pass values to your SQL commands, separating the SQL logic from the data.

Always prefer using DbParameter objects and adding them to the command's Parameters collection over concatenating strings directly into your CommandText.

Example of Parameter Addition:

// Using AddWithValue (convenient for simple types)
command.Parameters.AddWithValue("@ParameterName", parameterValue);

// Using specific DbParameter (more control over type, size, etc.)
SqlParameter param = new SqlParameter("@ParameterName", SqlDbType.VarChar, 50);
param.Value = parameterValue;
command.Parameters.Add(param);