DataReader - ADO.NET Data Access

Using DataReaders in ADO.NET

A DataReader provides a way to retrieve a forward-only, read-only stream of data from a data source. It is the most efficient way to retrieve data because it retrieves data row by row, without caching the entire result set in memory. This makes it ideal for scenarios where you need to process large amounts of data or when memory usage is a concern.

What is a DataReader?

DataReader objects are created by executing a Command object against a data source. They offer a high-performance mechanism for reading data, especially when dealing with large result sets. The primary object for this is DbDataReader, which provides an abstract base class for data providers.

Key Characteristics

Forward-Only: You can only move forward through the rows. You cannot go back to previous rows.
Read-Only: You cannot modify the data through the DataReader.
Row-by-Row Access: Data is read as it becomes available, reducing memory footprint.
High Performance: Generally faster than DataSet for simple data retrieval.

Core Methods and Properties

Here are some of the most commonly used members of a DataReader:

Read(): Advances the DataReader to the next record. 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.
GetValue(int ordinal): Gets the value of the specified column.
GetOrdinal(string name): Gets the zero-based column ordinal based on the column name.
Close(): Closes the DataReader object.

Example: Reading Data with SqlDataReader

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


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

            SqlCommand command = new SqlCommand(queryString, connection);

            try
            {
                connection.Open();
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            // Access data by column ordinal
                            int productId = reader.GetInt32(0);
                            string productName = reader.GetString(1);
                            decimal unitPrice = reader.GetDecimal(2);

                            Console.WriteLine($"ID: {productId}, Name: {productName}, Price: {unitPrice:C}");

                            // Alternatively, access data by column name
                            // int productIdByName = reader.GetInt32(reader.GetOrdinal("ProductID"));
                            // string productNameByName = reader.GetString(reader.GetOrdinal("ProductName"));
                            // decimal unitPriceByName = reader.GetDecimal(reader.GetOrdinal("UnitPrice"));
                            // Console.WriteLine($"ID: {productIdByName}, Name: {productNameByName}, Price: {unitPriceByName:C}");
                        }
                    }
                    else
                    {
                        Console.WriteLine("No rows found.");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}

Note: Always enclose your DataReader objects within a using statement to ensure that the underlying resources are properly disposed of, even if exceptions occur.

When to Use DataReaders

When you need to iterate through a result set one row at a time.
When memory efficiency is a primary concern.
When you only need to read data and not modify it.
For simple queries where you don't need the advanced features of a DataSet, such as data manipulation, caching, and synchronization.

Important: Keep the DataReader and its associated Connection object open for the shortest duration possible. Unnecessary open connections can impact performance.

Working with Different Data Types

DataReader provides typed accessor methods like GetInt32(), GetString(), GetDecimal(), etc. Using these typed methods is more efficient than using the generic GetValue() method, which returns an object that you would then need to cast.

MSDN