MSDN Documentation - Windows Data Access

DataReader Object

The DataReader object provides a way to retrieve a forward-only, read-only stream of data from a data source. It's a performant mechanism for consuming data when you don't need the full functionality of a dataset, such as client-side manipulation or navigation.

Purpose and Benefits

Performance: Optimized for speed and minimal memory overhead, as it reads data row by row.
Resource Efficiency: Avoids loading the entire result set into memory, making it suitable for large datasets.
Forward-Only Access: Data can only be read sequentially. You cannot move backward or jump to arbitrary rows.
Read-Only: Data retrieved through a DataReader cannot be modified.

Key Methods and Properties

Commonly used members of the DataReader object include:

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, releasing the associated connection.
FieldCount: Gets the number of columns in the current row.
GetName(int ordinal): Gets the name of the column at the specified zero-based ordinal.
GetOrdinal(string name): Gets the zero-based column ordinal for the specified column name.
GetValue(int ordinal): Gets the value of the specified column as an object.
IsDBNull(int ordinal): Checks if the specified column contains a null value.
Methods for typed data retrieval (e.g., GetInt32(int ordinal), GetString(int ordinal), GetDateTime(int ordinal)): Provide direct access to data as specific .NET types, improving performance by avoiding boxing/unboxing.

Example Usage (C# with ADO.NET)

Here's a basic example demonstrating how to use DataReader to fetch data from a SQL Server database:


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

public class DataReaderExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";
        string query = "SELECT CustomerID, CompanyName, ContactName FROM Customers";

        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())
                            {
                                Console.WriteLine($"ID: {reader.GetInt32(0)}, Company: {reader.GetString(1)}, Contact: {reader.GetString(2)}");
                            }
                        }
                        else
                        {
                            Console.WriteLine("No rows found.");
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine("An error occurred: " + ex.Message);
                }
            }
        }
    }
}

Important Considerations

Always wrap your DataReader and connection objects in using statements to ensure proper disposal of resources, even if errors occur.

Comparison with DataSet