MSDN Documentation

Executing Commands in ADO.NET

Executing commands is a fundamental operation when working with databases in ADO.NET. This involves sending SQL statements or stored procedure calls to the database and retrieving results. ADO.NET provides the Command object, along with its provider-specific implementations (e.g., SqlCommand for SQL Server, MySqlCommand for MySQL), to facilitate this process.

The Command Object

The Command object represents a SQL statement or stored procedure that you want to execute against a data source. Key properties include:

• CommandText: A string containing the SQL statement or the name of the stored procedure.
• CommandType: An enumeration that specifies how the CommandText property should be interpreted. Common values include Text (for SQL statements) and StoredProcedure.
• Connection: A reference to the Connection object that the command will use to execute.
• Parameters: A collection of Parameter objects that represent input, output, or input/output parameters for the command.

Executing Commands That Return Data

For commands that are expected to return a result set (e.g., SELECT statements), you typically use the ExecuteReader() method of the Command object. This method returns a DataReader object, which provides a forward-only, read-only stream of data from the database.

Example: Executing a SELECT Query

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

public class CommandExecutor
{
    public void GetData(string connectionString, string query)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.CommandType = CommandType.Text;

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        // Process each row
                        Console.WriteLine($"Column1: {reader["ColumnName1"]}, Column2: {reader["ColumnName2"]}");
                    }
                }
            }
        }
    }
}

Executing Commands That Do Not Return Data

For commands that do not return a result set, such as INSERT, UPDATE, DELETE statements, or stored procedures that perform actions, you use the ExecuteNonQuery() method. This method returns the number of rows affected by the command.

Example: Executing an INSERT Statement

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

public class CommandExecutor
{
    public int InsertData(string connectionString, string insertQuery)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (SqlCommand command = new SqlCommand(insertQuery, connection))
            {
                command.CommandType = CommandType.Text;
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"Number of rows affected: {rowsAffected}");
                return rowsAffected;
            }
        }
    }
}

Executing Commands 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 value of the first column of the first row in the result set.

Example: Executing a COUNT Query

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

public class CommandExecutor
{
    public object GetRecordCount(string connectionString, string countQuery)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (SqlCommand command = new SqlCommand(countQuery, connection))
            {
                command.CommandType = CommandType.Text;
                object result = command.ExecuteScalar();
                return result;
            }
        }
    }
}

Using Parameters

Parameters allow you to pass values to your SQL commands in a safe and efficient manner. They are crucial for preventing SQL injection and for improving performance by allowing the database to cache query plans.

Example: Inserting Data with Parameters

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

public class CommandExecutor
{
    public void AddProduct(string connectionString, string productName, decimal price)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

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

            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.CommandType = CommandType.Text;

                // Add parameters
                command.Parameters.AddWithValue("@Name", productName);
                command.Parameters.AddWithValue("@Price", price);

                command.ExecuteNonQuery();
                Console.WriteLine("Product added successfully.");
            }
        }
    }
}

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 are added to the Parameters collection as usual.

Example: Executing a Stored Procedure

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

public class CommandExecutor
{
    public void GetCustomerOrders(string connectionString, int customerId)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (SqlCommand command = new SqlCommand("usp_GetCustomerOrders", connection)) // Stored procedure name
            {
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@CustomerID", customerId);

                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"Order ID: {reader["OrderID"]}, Order Date: {reader["OrderDate"]}");
                    }
                }
            }
        }
    }
}

Understanding how to effectively execute commands is crucial for any data-driven application built with ADO.NET. By leveraging Command objects, ExecuteNonQuery(), ExecuteReader(), ExecuteScalar(), and parameterized queries, you can interact with your database securely and efficiently.