MSDN Documentation

Reading Data with DataReader

The DataReader object provides a forward-only, read-only stream of data from a data source. It is an efficient way to retrieve a result set when you need to process rows as they are read, rather than loading the entire result set into memory.

When to Use DataReader

• When you need to access data quickly without loading the entire result set.
• When you are processing large amounts of data row by row.
• When you only need to read the data and don't require updating capabilities.

The DataReader Workflow

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

1. Create a Command object and set its CommandText and Connection properties.
2. Execute the command using the ExecuteReader() method of the Command object. This returns a DataReader object.
3. Open the connection to the data source.
4. Use a loop (typically a while loop) with the Read() method of the DataReader to iterate through the rows.
5. Access column values within the loop using the DataReader's indexer (e.g., reader[0] or reader["ColumnName"]) or specific methods like GetString(), GetInt32(), etc.
6. Close the DataReader when you are finished with it. This also releases the connection.

Example: Reading Data with SqlDataReader

Here's a C# example demonstrating how to read data using SqlDataReader:

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

public class DataReaderExample
{
    public static void Main(string[] args)
    {
        string connectionString = "Your_Connection_String_Here";
        string queryString = "SELECT ProductID, ProductName, UnitPrice FROM dbo.Products;";

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

            try
            {
                connection.Open();
                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        // Access data by column ordinal or name
                        int productId = reader.GetInt32(0); // Using ordinal index
                        string productName = reader.GetString(1); // Using ordinal index
                        decimal unitPrice = reader.GetDecimal(reader.GetOrdinal("UnitPrice")); // Using GetOrdinal

                        Console.WriteLine($"ID: {productId}, Name: {productName}, Price: {unitPrice:C}");
                    }
                }
                else
                {
                    Console.WriteLine("No rows found.");
                }
                reader.Close(); // Ensure the reader is closed
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
            // The 'using' statement ensures the connection is closed automatically.
        }
    }
}
                
            

Key DataReader Properties and Methods