Microsoft Learn

Executing Commands

This document explains how to execute commands against a data source using ADO.NET. ADO.NET provides a rich set of classes for interacting with data, and executing commands is a fundamental part of this interaction.

Introduction to Commands

Commands are used to send SQL statements or stored procedure calls to a data source. ADO.NET uses specific command objects for different data providers, such as SqlCommand for SQL Server, OracleCommand for Oracle, and OleDbCommand for OLE DB compliant data sources.

Key Command Objects

Executing Non-Query Commands

Non-query commands, such as INSERT, UPDATE, DELETE, and CREATE TABLE, do not return a result set. The ExecuteNonQuery() method is used to execute these commands. It returns the number of rows affected by the command.

Example: Inserting Data

C# Example: Inserting a New Product
using System;
using System.Data;
using System.Data.SqlClient;

public class DataAccess
{
    public static void InsertProduct(string connectionString, string productName, decimal price)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "INSERT INTO Products (Name, Price) VALUES (@Name, @Price)";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@Name", productName);
                command.Parameters.AddWithValue("@Price", price);

                connection.Open();
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) inserted.");
            }
        }
    }
}

Executing Scalar Commands

Scalar commands return a single value, such as the result of an aggregate function (e.g., COUNT(*), SUM(Price)). The ExecuteScalar() method is used for this purpose. It returns the first column of the first row in the result set as an object.

Example: Counting Records

C# Example: Counting Customers
using System;
using System.Data;
using System.Data.SqlClient;

public class DataAccess
{
    public static int CountCustomers(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "SELECT COUNT(*) FROM Customers";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                connection.Open();
                object result = command.ExecuteScalar();
                return (result != null && result != DBNull.Value) ? Convert.ToInt32(result) : 0;
            }
        }
    }
}

Executing Reader Commands

Reader commands are used when you expect to retrieve multiple rows and columns from the data source, such as a SELECT statement. The ExecuteReader() method returns an instance of a DataReader object (e.g., SqlDataReader), which allows you to read data row by row.

Example: Retrieving Product Information

C# Example: Retrieving Product Details
using System;
using System.Data;
using System.Data.SqlClient;

public class DataAccess
{
    public static void GetProductDetails(string connectionString, int productId)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "SELECT Name, Price FROM Products WHERE ProductID = @ProductID";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@ProductID", productId);

                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"Product Name: {reader["Name"]}, Price: {reader["Price"]}");
                        }
                    }
                    else
                    {
                        Console.WriteLine("No product found with the specified ID.");
                    }
                }
            }
        }
    }
}

Using Stored Procedures

ADO.NET also supports executing stored procedures. To do this, set the CommandType property of the DbCommand object to CommandType.StoredProcedure and provide the name of the stored procedure in the CommandText property.

Example: Executing a Stored Procedure

C# Example: Executing a Stored Procedure
using System;
using System.Data;
using System.Data.SqlClient;

public class DataAccess
{
    public static void AddNewCustomer(string connectionString, string customerName)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Assuming you have a stored procedure named 'usp_AddCustomer'
            using (SqlCommand command = new SqlCommand("usp_AddCustomer", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@CustomerName", customerName);

                // You might also have an output parameter to get the new customer ID
                // SqlParameter outputIdParam = new SqlParameter("@NewCustomerID", SqlDbType.Int) { Direction = ParameterDirection.Output };
                // command.Parameters.Add(outputIdParam);

                connection.Open();
                command.ExecuteNonQuery();
                // int newCustomerId = (int)outputIdParam.Value;
                // Console.WriteLine($"New customer added with ID: {newCustomerId}");
                Console.WriteLine("Stored procedure executed successfully.");
            }
        }
    }
}

Important: Parameterization

Always use parameterized queries (like in the examples above) to prevent SQL injection vulnerabilities. Never concatenate user input directly into SQL strings.

Command Behavior Options

The ExecuteReader() method accepts an optional CommandBehavior enum value to control how the data reader operates. Some common values include:

Conclusion

Understanding how to execute commands effectively is crucial for building robust data-driven applications with ADO.NET. By leveraging ExecuteNonQuery(), ExecuteScalar(), and ExecuteReader(), along with proper parameterization and command behavior settings, you can efficiently interact with your data sources.