DataReader (ADO.NET)

The DataReader class in ADO.NET provides a way to stream rows from a data source. It offers a forward-only, read-only cursor for retrieving data. This approach is highly efficient for scenarios where you need to process data record by record without loading the entire result set into memory, such as displaying large lists or performing iterative operations on data.

Key Features and Benefits

Forward-Only, Read-Only: Data can only be read sequentially, and the data cannot be modified through the DataReader.
Memory Efficiency: Retrieves data row by row, minimizing memory consumption, especially for large datasets.
Performance: Generally faster than using Datasets for simple data retrieval and processing due to less overhead.
Resource Management: Implements IDisposable, so it should always be used within a using statement to ensure resources are properly released.

Using DataReader

The process typically involves creating a Command object, executing it against the data source using ExecuteReader(), and then iterating through the results using the DataReader.

Example: Retrieving Data with SqlDataReader

The following example demonstrates how to use SqlDataReader to retrieve data from a SQL Server database.

C# Example


using System;
using System.Data.SqlClient;

public class DataReaderExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            string query = "SELECT CustomerID, CompanyName FROM Customers;";
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            // Access data by column ordinal or name
                            int customerId = reader.GetInt32(0); // Index of CustomerID
                            string companyName = reader.GetString(1); // Index of CompanyName

                            Console.WriteLine($"ID: {customerId}, Name: {companyName}");
                        }
                    }
                    else
                    {
                        Console.WriteLine("No rows found.");
                    }
                } // DataReader is automatically closed and disposed here
            } // Command object is disposed here
        } // Connection is automatically closed and disposed here
    }
}

Common DataReader Methods

Read(): Advances the DataReader to the next record in the result set. Returns true if there is another record; otherwise, false.
FieldCount: Gets the number of columns in the current row.
GetName(int ordinal): Gets the name of the specified column.
GetOrdinal(string name): Gets the zero-based column ordinal of the specified column name.
GetValue(int ordinal): Gets the value of the specified column.
IsDBNull(int ordinal): Checks if the value in the specified column is DBNull.Value.
Specific type retrieval methods like GetInt32(int ordinal), GetString(int ordinal), GetDateTime(int ordinal), etc.

When to Use DataReader

When you need to retrieve a large number of records and memory is a concern.
When you only need to read data and do not require the ability to update it or navigate backward.
For scenarios requiring high performance data streaming.

Considerations

Ensure that the connection to the data source remains open while the DataReader is in use. Closing the connection before you finish reading will result in an error. Always use the using statement for proper resource management.