• Home
• .NET Framework
• Data Access
• ADO.NET
• Commands

ADO.NET Command Class

The Command classes in ADO.NET are fundamental for interacting with data sources. They represent an SQL statement or stored procedure to be executed against a data source.

Core Concepts

The primary classes involved in commanding are:

• DbCommand: An abstract base class providing common functionality for command objects.
• SqlCommand (for SQL Server): Represents an SQL statement or stored procedure.
• OleDbCommand (for OLE DB providers): Represents an OLE DB command.
• OdbcCommand (for ODBC providers): Represents an ODBC command.

Key properties and methods of a Command object include:

• CommandText: The SQL statement or stored procedure name to execute.
• CommandType: Specifies how the CommandText property should be interpreted (e.g., Text for SQL, StoredProcedure for a stored procedure).
• Connection: The DbConnection object associated with the command.
• Parameters: A collection of parameters for the command.
• ExecuteReader(): Executes the command and returns a DbDataReader.
• ExecuteNonQuery(): Executes the command and returns the number of rows affected.
• ExecuteScalar(): Executes the command and returns the first column of the first row in the result set.

Creating and Executing Commands

Here's a typical workflow for creating and executing a command:

1. Create a DbConnection object and open it.
2. Create a DbCommand object, setting its Connection, CommandText, and CommandType.
3. (Optional) Add any necessary parameters to the command's Parameters collection.
4. Execute the command using one of the appropriate execution methods (ExecuteReader, ExecuteNonQuery, or ExecuteScalar).
5. Process the results.
6. Close the connection.

Example: Executing a SELECT Statement

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

public class CommandExample
{
    public static void ReadData(string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            // Open the connection
            connection.Open();

            // Create a SqlCommand object
            string sql = "SELECT CustomerID, CompanyName FROM Customers WHERE City = @City";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                // Set the CommandType
                command.CommandType = CommandType.Text;

                // Add a parameter
                command.Parameters.AddWithValue("@City", "London");

                // Execute the command and get a DataReader
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    Console.WriteLine("Customers in London:");
                    while (reader.Read())
                    {
                        Console.WriteLine($"- ID: {reader["CustomerID"]}, Name: {reader["CompanyName"]}");
                    }
                }
            }
        }
    }
}
                

Example: Executing a Non-Query Statement (INSERT)

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

public class CommandExample
{
    public static int AddNewProduct(string connectionString, string productName, decimal price)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            string sql = "INSERT INTO Products (ProductName, UnitPrice) VALUES (@ProductName, @UnitPrice)";
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.CommandType = CommandType.Text;
                command.Parameters.AddWithValue("@ProductName", productName);
                command.Parameters.AddWithValue("@UnitPrice", price);

                // Execute the command and get the number of rows affected
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) inserted.");
                return rowsAffected;
            }
        }
    }
}
                

Using Stored Procedures

To execute a stored procedure, set the CommandType to CommandType.StoredProcedure and set the CommandText to the name of the stored procedure.

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

public class CommandExample
{
    public static int GetCustomerCountByCity(string connectionString, string city)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Assume a stored procedure named 'sp_GetCustomerCountByCity' exists
            using (SqlCommand command = new SqlCommand("sp_GetCustomerCountByCity", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                // Add input parameter
                command.Parameters.AddWithValue("@CityName", city);

                // Add output parameter (assuming the SP returns a count)
                SqlParameter returnParameter = command.Parameters.Add("@CustomerCount", SqlDbType.Int);
                returnParameter.Direction = ParameterDirection.Output;

                command.ExecuteNonQuery(); // Execute the stored procedure

                return (int)command.Parameters["@CustomerCount"].Value;
            }
        }
    }
}
                

Best Practices

• Always use parameterized queries to prevent SQL injection vulnerabilities.
• Use using statements for Connection, Command, and DataReader objects to ensure they are properly disposed of.
• Set the appropriate CommandType.
• Choose the correct execution method based on your needs (ExecuteReader for result sets, ExecuteNonQuery for DML statements, ExecuteScalar for single values).