MSDN Documentation

ADO.NET Commands and DataReaders

The ADO.NET Command and DataReader objects are fundamental components for interacting with databases. Commands are used to execute SQL statements or stored procedures, while DataReaders provide a fast, forward-only, read-only stream of data from a database.

ADO.NET Commands

The Command object represents a SQL statement or stored procedure to be executed against a data source. It can be created for a specific data provider (e.g., SqlCommand for SQL Server, OracleCommand for Oracle).

Key properties of a Command object include:

• CommandText: The SQL statement or stored procedure name to execute.
• CommandType: Specifies whether the CommandText is a text string, a stored procedure, or a table name.
• Connection: The Connection object used to connect to the data source.
• Parameters: A collection of parameters for the command.

Command Types

The CommandType enumeration defines how the CommandText is interpreted:

• Text: The CommandText is a SQL statement. This is the most common type.
• StoredProcedure: The CommandText is the name of a stored procedure.
• TableDirect: The CommandText is the name of a table. This is less commonly used.

Executing Commands

Commands can be executed in various ways, depending on the expected outcome:

• ExecuteReader(): Executes a command and returns a DataReader object, which provides a forward-only stream of results. This is efficient for retrieving large result sets.
• ExecuteNonQuery(): Executes a command that does not return a result set, such as an INSERT, UPDATE, or DELETE statement. It returns the number of rows affected.
• ExecuteScalar(): Executes a command and returns the first column of the first row in the result set. This is useful for retrieving a single value, like a count or an ID.
• ExecuteXmlReader(): Executes a command and returns an XmlReader object.

DataReaders

The DataReader object (e.g., SqlDataReader) provides a way to read a forward-only stream of rows from a data source. It's the most performant way to retrieve data when you only need to read through the results sequentially.

DataReader objects are lightweight and do not load the entire result set into memory, making them ideal for large datasets.

Reading Data

The primary method for advancing through the result set with a DataReader is Read(). This method returns true if there are more rows to read and advances the reader to the next row.

You can access data in the current row by column name or by ordinal index.

// Example using SqlCommand and SqlDataReader (C#)
using System;
using System.Data;
using System.Data.SqlClient;

public class DataReaderExample
{
    public static void Main(string[] args)
    {
        string connectionString = "YourConnectionStringHere";
        string sql = "SELECT CustomerID, CompanyName FROM Customers;";

        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())
                            {
                                // Access data by column name
                                int customerId = reader.GetInt32(reader.GetOrdinal("CustomerID"));
                                string companyName = reader.GetString(reader.GetOrdinal("CompanyName"));

                                Console.WriteLine($"ID: {customerId}, Name: {companyName}");

                                // Alternatively, access by ordinal index (0-based)
                                // int customerIdByIndex = reader.GetInt32(0);
                                // string companyNameByIndex = reader.GetString(1);
                            }
                        }
                        else
                        {
                            Console.WriteLine("No rows found.");
                        }
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine($"Error: {ex.Message}");
                }
            }
        }
    }
}
            

Important Considerations

• Resource Management: Always ensure connections and data readers are closed and disposed of properly to prevent resource leaks.
• Performance: DataReaders are optimized for reading data sequentially. Avoid calling Read() multiple times within a loop to fetch the same row's data, as this is inefficient. Access all required fields in a single Read() iteration.
• Data Types: Use appropriate methods (e.g., GetInt32(), GetString(), GetDateTime()) to retrieve data based on its underlying database type. Use IsDBNull() before accessing data to check for null values.
• Forward-Only: DataReaders do not support random access to rows. You can only move forward through the results.