MSDN Documentation

Using DataReader in ADO.NET

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

What is DataReader?

DataReader is an interface that represents a data reader. The specific implementation depends on the data provider you are using (e.g., SqlDataReader for SQL Server, OdbcDataReader for ODBC). It allows you to iterate through the results of a query one record at a time.

Key Features:

• Forward-Only: You can only move forward through the records. You cannot go back to previous records or jump to a specific record.
• Read-Only: You can read data from the fields, but you cannot modify it directly through the DataReader.
• High Performance: It's very efficient because it doesn't require buffering the entire result set, making it ideal for large datasets.
• Resource Efficient: It consumes minimal resources as it only holds one row in memory at a time.

How to Use DataReader:

The basic workflow involves executing a command that returns data, obtaining a DataReader object, reading the data row by row, and then closing the reader.

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

        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 name or ordinal index
                        Console.WriteLine($"ID: {reader["ProductID"]}, Name: {reader["ProductName"]}, Price: {reader["UnitPrice"]}");
                        // Or by index: Console.WriteLine($"ID: {reader[0]}, Name: {reader[1]}, Price: {reader[2]}");
                    }
                }
                else
                {
                    Console.WriteLine("No rows found.");
                }
                reader.Close(); // Always close the reader
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error: {ex.Message}");
            }
        }
    }
}
                

Key Methods and Properties:

• ExecuteReader(): Executes the command and returns a DataReader object.
• Read(): Advances the DataReader to the next record. Returns true if there are more rows, false otherwise.
• HasRows: Gets a value indicating whether the data reader contains one or more rows.
• Close(): Closes the DataReader object. This is crucial for releasing resources.
• FieldCount: Gets the number of columns in the current row.
• GetOrdinal(string name): Returns the zero-based column ordinal of the specified column name.
• GetValue(int ordinal): Gets the value of the specified column.
• Get(int ordinal): e.g., GetInt32(), GetString(), GetDecimal() retrieves the value of the specified column as the specified type.
• IsDBNull(int ordinal): Checks if the value in the specified column is DBNull.Value.

When to Use DataReader:

• When you need to process a large number of rows and memory usage is a concern.
• When you only need to read data sequentially and don't require random access or data manipulation capabilities.
• For improving the performance of data retrieval operations.

Alternatives:

For scenarios where you need to work with the data offline, bind data to UI controls, or perform complex data manipulation, consider using DataAdapter and DataTable or DataSet objects.