MSDN Documentation

DataReader Objects

The DataReader object in ADO.NET provides a way to retrieve a forward-only, read-only stream of data from a data source. It is highly efficient for scenarios where you need to read through a result set row by row without the need to load the entire dataset into memory.

Key Features and Benefits

• Performance: DataReader objects are generally faster than DataSet objects because they don't require loading the entire result set into memory.
• Forward-Only Access: Data can only be read sequentially from the first row to the last. You cannot move backward or jump to a specific row.
• Read-Only: The data retrieved through a DataReader is read-only. You cannot modify data directly using the DataReader.
• Resource Efficiency: DataReader objects consume less memory and network bandwidth compared to DataSet objects, making them ideal for large result sets.

Working with DataReader

To use a DataReader, you typically follow these steps:

1. Open a connection to the data source.
2. Create a command object and associate it with the connection and a SQL query.
3. Execute the command using ExecuteReader() to obtain a DataReader object.
4. Iterate through the rows using a loop (e.g., while (reader.Read())).
5. Access column data within the loop using methods like reader.GetString(), reader.GetInt32(), reader.GetDateTime(), etc., or by index.
6. Close the DataReader and the connection when done.

Example: Retrieving Data with SqlDataReader

The following C# code demonstrates how to use SqlDataReader to fetch data from a SQL Server database.

using System;
using System.Data.SqlClient;

public class DataReaderExample
{
    public static void Main(string[] args)
    {
        string connectionString = "YourConnectionStringHere"; // Replace with your actual connection string
        string sqlQuery = "SELECT CustomerID, CompanyName, ContactName FROM Customers";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            while (reader.Read())
                            {
                                // Access data by column name or ordinal position
                                int customerId = reader.GetInt32(reader.GetOrdinal("CustomerID"));
                                string companyName = reader.GetString(reader.GetOrdinal("CompanyName"));
                                string contactName = reader.IsDBNull(reader.GetOrdinal("ContactName")) ? "N/A" : reader.GetString(reader.GetOrdinal("ContactName"));

                                Console.WriteLine($"ID: {customerId}, Company: {companyName}, Contact: {contactName}");
                            }
                        }
                        else
                        {
                            Console.WriteLine("No rows found.");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}
            

Common DataReader Methods

• Read(): Advances the DataReader 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.
• GetName(int ordinal): Gets the name of the column at the specified ordinal position.
• GetOrdinal(string name): Gets the zero-based column ordinal for the specified column name.
• IsDBNull(int ordinal): Returns true if the specified column contains a null value.
• Various GetXXX(int ordinal) or GetXXX(string name) methods (e.g., GetInt32, GetString, GetDateTime, GetValue) to retrieve column data as specific types.

When to Use DataReader

• When you need to process a large amount of data sequentially.
• When performance is critical and you want to minimize memory usage.
• When you only need to read data and do not require the ability to update it or navigate backward.

For scenarios requiring disconnected data, caching, or in-memory manipulation, consider using the DataSet object instead.