Microsoft Learn

DataCommand in ADO.NET

The DataCommand class is a fundamental component of ADO.NET, representing a SQL statement or stored procedure that you want to execute against a data source.

Overview

A DataCommand object is used to encapsulate the details of executing a command, such as:

• The SQL statement or stored procedure name to execute.
• The parameters to pass to the command.
• The connection to use for executing the command.
• The type of command (e.g., text, stored procedure).

Key Properties

Here are some of the most important properties of a DataCommand object:

• CommandText: Gets or sets the SQL statement or stored procedure to execute.
• CommandType: Gets or sets a value indicating how to interpret the CommandText property (e.g., Text, StoredProcedure, TableDirect).
• Connection: Gets or sets the DataConnection object used to execute the command.
• Parameters: Gets a collection of parameters associated with the command.
• Transaction: Gets or sets the DataTransaction object to use for the command.
• CommandTimeout: Gets or sets the time in seconds to wait for the command to execute before terminating.

Key Methods

The primary methods for executing a DataCommand are:

• ExecuteNonQuery(): Executes a command that does not return a result set (e.g., INSERT, UPDATE, DELETE, or a stored procedure that performs an action). It returns the number of rows affected.
• ExecuteReader(): Executes the command and returns a DataReader object that can be used to read the rows in the result set.
• ExecuteScalar(): Executes the command and returns the first column of the first row in the result set. This is useful for retrieving a single value, such as a count or an ID.
• ExecuteXmlReader(): Executes the command and returns an XmlReader object.

Example: Executing a SELECT Statement

This example demonstrates how to use DataCommand to retrieve data using a SELECT statement.

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

public class DataCommandExample
{
    public static void Main(string[] args)
    {
        string connectionString = "YourConnectionStringHere"; // Replace with your actual connection string

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            // Define the SQL query
            string sqlQuery = "SELECT CustomerID, CompanyName FROM Customers WHERE City = @City";

            // Create a DataCommand object
            using (SqlCommand command = new SqlCommand(sqlQuery, connection))
            {
                // Set the CommandType (optional for text queries, but good practice)
                command.CommandType = CommandType.Text;

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

                Console.WriteLine("Customers in London:");

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

Example: Executing an INSERT Statement

This example shows how to use DataCommand to insert data into a table.

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

public class DataCommandInsertExample
{
    public static void Main(string[] args)
    {
        string connectionString = "YourConnectionStringHere";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

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

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

                // Add parameters for the new product
                command.Parameters.AddWithValue("@Name", "New Gadget");
                command.Parameters.AddWithValue("@Price", 19.99);

                // Execute the command and get the number of rows affected
                int rowsAffected = command.ExecuteNonQuery();

                Console.WriteLine($"{rowsAffected} row(s) inserted successfully.");
            }
        }
    }
}
            

Best Practices

• Always use parameterized queries to prevent SQL injection vulnerabilities.
• Dispose of DataCommand and related objects (like DataReader, Connection) using using statements to ensure resources are properly released.
• Set an appropriate CommandTimeout for long-running queries.
• Choose the correct CommandType for better performance and clarity.

Stored Procedures

When executing stored procedures, set CommandType to CommandType.StoredProcedure and set CommandText to the name of the stored procedure.

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

public class DataCommandStoredProcExample
{
    public static void Main(string[] args)
    {
        string connectionString = "YourConnectionStringHere";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            using (SqlCommand command = new SqlCommand("usp_GetCustomerOrders", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@CustomerID", "ALFKI");

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