MSDN Documentation

DataReaders in ADO.NET

ADO.NET DataReaders provide a forward-only, read-only stream of data from a data source. They are highly efficient for scenarios where you need to retrieve data quickly and process it row by row without needing to load the entire dataset into memory. This makes them ideal for tasks like populating grids, performing iterative calculations, or generating reports.

What is a DataReader?

A DataReader is an object that implements the IDataReader interface. The most common implementations you'll encounter are:

• SqlDataReader: For SQL Server databases.
• OleDbDataReader: For OLE DB compliant data sources (which can include Excel, Access, etc.).
• OracleDataReader: For Oracle databases.

The core functionality is consistent across these implementations, allowing for a largely database-agnostic approach when working with them.

Key Features and Benefits

• Forward-Only, Read-Only: Data is accessed sequentially from beginning to end. You cannot move backward or modify data through the reader.
• High Performance: Minimizes memory overhead as data is read one row at a time. This is significantly more efficient than loading data into a DataSet for simple read operations.
• Resource Efficiency: Keeps the database connection open only for the duration of the read operation, releasing it as soon as possible.
• Live Data: Provides direct access to the data as it exists in the database at the time of the query.

Using a DataReader

The typical workflow for using a DataReader involves the following steps:

1. Establish a connection to the data source.
2. Create a command object.
3. Execute the command using ExecuteReader(), which returns a DataReader object.
4. Iterate through the rows using a while (reader.Read()) loop.
5. Access column values within the loop using appropriate methods (e.g., reader["ColumnName"], reader.GetString(index), reader.GetInt32(index)).
6. Close the DataReader and the connection when finished.

Example: Retrieving Product Names from a Database

using System;
using System.Data;
using System.Data.SqlClient; // For SQL Server

public class ProductReader
{
    public void GetProductNames(string connectionString)
    {
        string query = "SELECT ProductName, UnitPrice FROM Products";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                try
                {
                    connection.Open();
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                string productName = reader.GetString(reader.GetOrdinal("ProductName"));
                                decimal unitPrice = reader.GetDecimal(reader.GetOrdinal("UnitPrice"));

                                Console.WriteLine($"Product: {productName}, Price: {unitPrice:C}");
                            }
                        }
                        else
                        {
                            Console.WriteLine("No products found.");
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error: {ex.Message}");
                }
            }
        }
    }
}
            

Common DataReader Methods

• Read(): Advances the reader to the next record in the result set. Returns true if there are more rows, false otherwise.
• Close(): Closes the DataReader object.
• FieldCount: Gets the number of columns in the current row.
• GetName(int i): Gets the name of the column at the specified index.
• GetOrdinal(string name): Gets the zero-based column index of the specified column name.
• GetValue(int i): Retrieves the value of the specified column as an object.
• GetString(int i), GetInt32(int i), GetDecimal(int i), GetDateTime(int i), etc.: Typed methods for retrieving column values, providing better performance and type safety than GetValue.
• IsDBNull(int i): Checks if the value in the specified column is DBNull.Value.