ADO.NET DataReaders - MSDN Documentation

Using DataReaders in ADO.NET

DataReaders provide a forward-only, read-only stream of data from a data source. They are highly efficient for retrieving large result sets when you need to process rows sequentially.

What is a DataReader?

A DataReader object, such as SqlDataReader for SQL Server or OleDbDataReader for OLE DB providers, allows you to retrieve data row by row. This streaming approach minimizes memory consumption compared to filling a DataTable or DataSet.

Key Characteristics:

Forward-Only: You can only move forward through the rows. You cannot go back to a previous row.
Read-Only: Data retrieved through a DataReader cannot be modified directly.
Performance: Offers excellent performance for reading large datasets.
Resource Efficient: Does not load the entire result set into memory at once.

Creating and Using a DataReader

You typically obtain a DataReader by executing a command that returns data, such as a SELECT statement. The most common method is to use the ExecuteReader() method of a DbCommand object.

C# Example: Reading Data with SqlDataReader


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 queryString = "SELECT ProductID, ProductName, UnitPrice FROM Production.Products;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(queryString, connection);

            try
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            // Access data by column name or ordinal index
                            int productId = reader.GetInt32(0); // Or reader["ProductID"]
                            string productName = reader.GetString(1); // Or reader["ProductName"]
                            decimal unitPrice = reader.GetDecimal(2); // Or reader["UnitPrice"]

                            Console.WriteLine($"ID: {productId}, Name: {productName}, Price: {unitPrice:C}");
                        }
                    }
                    else
                    {
                        Console.WriteLine("No rows found.");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"An error occurred: {ex.Message}");
            }
        }
    }
}

Common DataReader Methods:

Read(): Advances the reader to the next record. Returns true if there are more rows, false otherwise.
Close(): Closes the DataReader object.
FieldCount: Gets the number of columns in the current row.
GetOrdinal(string name): Returns the zero-based column ordinal (index) of the specified column name.
IsDBNull(int index): Checks if the value at the specified column index is DBNull.Value.
GetString(int index), GetInt32(int index), GetDecimal(int index), etc.: Methods to retrieve the value of a column as a specific data type.

When to Use DataReaders:

When you need to process a large number of records sequentially.
When you only need to read data and don't need to update it or manipulate it in memory.
When performance is critical.

Important Note:

Always ensure that the DataReader and the associated connection are closed (or disposed using a using statement) to release database resources promptly.

Performance Tip:

When accessing columns, it's generally more performant to use the column ordinal index (e.g., reader.GetString(1)) if you know it, rather than looking up by column name (e.g., reader["ProductName"]) repeatedly inside a loop, as the lookup can have a small overhead.