Executing Commands in ADO.NET

ADO.NET provides a rich set of classes for interacting with data sources. A fundamental aspect of this interaction is executing commands, which can range from simple queries to complex stored procedures. This document explores the various ways to execute commands using ADO.NET, focusing on the core objects involved and best practices.

The Role of IDbCommand

The IDbCommand interface, and its concrete implementations like SqlCommand (for SQL Server), MySqlCommand (for MySQL), and OracleCommand (for Oracle), represent an SQL statement or stored procedure to be executed against a data source. Key properties and methods include:

Executing Non-Query Commands

For operations that modify data but do not return a result set, ExecuteNonQuery() is the appropriate method. 


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

public class DataUpdater
{
    private string connectionString = "YourConnectionStringHere";

    public void UpdateProductPrice(int productId, decimal newPrice)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "UPDATE Products SET Price = @Price WHERE ProductID = @ProductID";
            SqlCommand command = new SqlCommand(query, connection);

            // Add parameters to prevent SQL injection
            command.Parameters.AddWithValue("@Price", newPrice);
            command.Parameters.AddWithValue("@ProductID", productId);

            try
            {
                connection.Open();
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) updated.");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error updating product price: {ex.Message}");
            }
        }
    }
}

Always use parameterized queries with Parameters.AddWithValue() or by specifying the data type explicitly to prevent SQL injection vulnerabilities.

Executing Queries that Return Data

When you need to retrieve data from the database, ExecuteReader() is commonly used. It returns an IDataReader, which is a forward-only, read-only stream of data. 


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

public class DataReaderExample
{
    private string connectionString = "YourConnectionStringHere";

    public void GetCustomerNames()
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "SELECT CustomerID, CompanyName FROM Customers WHERE Country = @Country";
            SqlCommand command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@Country", "USA");

            try
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
                        }
                    }
                    else
                    {
                        Console.WriteLine("No customers found for the specified country.");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error retrieving customer names: {ex.Message}");
            }
        }
    }
}

The IDataReader object allows you to iterate through the result set row by row using the Read() method. You can access column values by index or by column name.

Executing Commands that Return a Single Value

For queries that are expected to return a single column and a single row, such as aggregate functions (e.g., COUNT(), SUM()), ExecuteScalar() is highly efficient. 


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

public class ScalarExample
{
    private string connectionString = "YourConnectionStringHere";

    public int GetProductCount()
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "SELECT COUNT(*) FROM Products";
            SqlCommand command = new SqlCommand(query, connection);

            try
            {
                connection.Open();
                object result = command.ExecuteScalar();
                if (result != null && result != DBNull.Value)
                {
                    return Convert.ToInt32(result);
                }
                return 0;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error getting product count: {ex.Message}");
                return -1; // Indicate error
            }
        }
    }
}

Executing Stored Procedures

To execute a stored procedure, set the CommandType to StoredProcedure and set the CommandText to the name of the stored procedure. Parameters should be added appropriately. 


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

public class StoredProcedureExample
{
    private string connectionString = "YourConnectionStringHere";

    public void GetOrderDetails(int orderId)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand("usp_GetOrderDetails", connection); // Stored procedure name
            command.CommandType = CommandType.StoredProcedure;

            // Add input parameter
            command.Parameters.AddWithValue("@OrderID", orderId);

            try
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"Product: {reader["ProductName"]}, Quantity: {reader["Quantity"]}");
                        }
                    }
                    else
                    {
                        Console.WriteLine($"No details found for Order ID: {orderId}");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error executing stored procedure: {ex.Message}");
            }
        }
    }
}

When dealing with stored procedures that return multiple result sets, you can call ExecuteReader() multiple times on the same IDataReader object as long as the previous result set has been fully consumed.

Transactions

For operations that must be atomic (either all succeed or all fail), ADO.NET supports transactions. This ensures data integrity. 


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

public class TransactionExample
{
    private string connectionString = "YourConnectionStringHere";

    public void TransferFunds(int fromAccountId, int toAccountId, decimal amount)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlTransaction transaction = null;

            try
            {
                transaction = connection.BeginTransaction();

                // Debit from account
                string debitQuery = "UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @AccountID";
                SqlCommand debitCommand = new SqlCommand(debitQuery, connection, transaction);
                debitCommand.Parameters.AddWithValue("@Amount", amount);
                debitCommand.Parameters.AddWithValue("@AccountID", fromAccountId);
                debitCommand.ExecuteNonQuery();

                // Credit to account
                string creditQuery = "UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @AccountID";
                SqlCommand creditCommand = new SqlCommand(creditQuery, connection, transaction);
                creditCommand.Parameters.AddWithValue("@Amount", amount);
                creditCommand.Parameters.AddWithValue("@AccountID", toAccountId);
                creditCommand.ExecuteNonQuery();

                // Commit the transaction
                transaction.Commit();
                Console.WriteLine("Funds transferred successfully.");
            }
            catch (Exception ex)
            {
                // Rollback the transaction if any error occurred
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                Console.WriteLine($"Transaction failed: {ex.Message}");
            }
        }
    }
}

Conclusion

Understanding how to execute commands effectively in ADO.NET is crucial for any .NET developer working with databases. By utilizing the appropriate methods like ExecuteNonQuery(), ExecuteReader(), and ExecuteScalar(), and by employing parameterized queries and transactions, you can build robust, secure, and efficient data-driven applications.