MSDN Documentation

ADO.NET Data Commands

Data commands are fundamental to interacting with data sources in ADO.NET. They represent the operations you want to perform, such as retrieving data, inserting new records, updating existing ones, or deleting data. ADO.NET provides specific classes for executing commands, with the most common being SqlCommand for SQL Server and other relational databases.

Understanding Data Commands

A data command object encapsulates an SQL statement or a stored procedure that you want to execute against a data source. Key components of a data command include:

• Command Text: The SQL query or stored procedure name to be executed.
• Connection: The DbConnection object that establishes a link to the data source.
• Command Type: Specifies whether the Command Text is a T-SQL statement, a stored procedure, or a table name.
• Parameters: Allows you to pass values into the command securely and efficiently, preventing SQL injection attacks.

Executing Commands

The primary method for executing a data command is through its associated DbCommand object. The specific method used depends on the type of operation:

• ExecuteNonQuery(): Used for SQL statements that do not return a result set, such as INSERT, UPDATE, and DELETE statements. It returns the number of rows affected by the command.
• ExecuteReader(): Used for SQL statements that return a result set, such as SELECT statements. It returns a DbDataReader object, which provides a forward-only, read-only stream of data.
• ExecuteScalar(): Used for SQL statements that return a single value, such as an aggregate function (e.g., COUNT(*)) or a single column value. It returns the value of the first column of the first row in the result set.
• ExecuteXmlReader(): Used to execute SQL statements that return XML data.

Key Classes for Data Commands

ADO.NET provides a set of classes for working with data commands, often within specific data provider namespaces. The most common ones include:

Class	Namespace	Description
SqlCommand	System.Data.SqlClient	Represents a SQL statement or stored procedure to execute against a SQL Server database.
OleDbCommand	System.Data.OleDb	Represents a SQL statement or stored procedure to execute against any OLE DB-compliant data source.
OdbcCommand	System.Data.Odbc	Represents a SQL statement or stored procedure to execute against an ODBC data source.
OracleCommand	Oracle.ManagedDataAccess.Client	Represents a SQL statement or stored procedure to execute against an Oracle database.

Using Parameters

Employing parameters is crucial for security and performance. Instead of concatenating values directly into your SQL strings, use parameters. This prevents SQL injection vulnerabilities and can improve query plan caching.

Example: Using `SqlCommand` with Parameters

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

public class CommandExample
{
    public void AddProduct(string productName, decimal price)
    {
        string connectionString = "Your_Connection_String_Here";
        string sql = "INSERT INTO Products (ProductName, Price) VALUES (@ProductName, @Price)";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                command.Parameters.AddWithValue("@ProductName", productName);
                command.Parameters.AddWithValue("@Price", price);

                try
                {
                    connection.Open();
                    int rowsAffected = command.ExecuteNonQuery();
                    Console.WriteLine($"{rowsAffected} row(s) inserted.");
                }
                catch (SqlException ex)
                {
                    Console.WriteLine($"Error: {ex.Message}");
                }
            }
        }
    }
}
                

Example: Using `ExecuteReader`

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

public class CommandExample
{
    public void GetProductNames()
    {
        string connectionString = "Your_Connection_String_Here";
        string sql = "SELECT ProductName FROM Products";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                try
                {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                Console.WriteLine(reader["ProductName"]);
                            }
                        }
                        else
                        {
                            Console.WriteLine("No products found.");
                        }
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine($"Error: {ex.Message}");
                }
            }
        }
    }
}
                

Command Types

The CommandType enumeration specifies how to interpret the CommandText property:

• Text: The CommandText is a SQL statement or a comma-separated list of SQL statements. This is the default.
• StoredProcedure: The CommandText is the name of a stored procedure.
• TableDirect: The CommandText is the name of a table. This option is not supported by all providers.

Understanding and effectively utilizing ADO.NET data commands is essential for building robust and efficient data-driven applications.