MSDN Documentation

DataReader Concepts

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 reading large amounts of data quickly, as it doesn't load the entire dataset into memory at once. This makes it ideal for scenarios where you need to process records one by one.

Key Features and Benefits:

• Forward-Only: You can only move forward through the rows and columns.
• Read-Only: You cannot modify the data through the DataReader.
• High Performance: Minimal memory overhead due to streaming data.
• Efficient for Large Datasets: Processes data row by row, reducing memory consumption.
• Resource Management: Automatically closes when all data has been read or explicitly closed.

Commonly Used Methods:

• Read(): Advances the DataReader to the next record. Returns true if there is another record, false otherwise.
• Close(): Closes the DataReader object.
• GetBoolean(ordinal), GetChar(ordinal), GetString(ordinal), GetInt32(ordinal), GetDateTime(ordinal), etc.: Retrieves the column value of the specified type at the specified column ordinal (index).
• GetValue(ordinal): Retrieves the value of the specified column as a generic Object.
• GetName(ordinal): Retrieves the name of the specified column.
• FieldCount: Gets the number of columns in the current row.
• IsDBNull(ordinal): Checks if the specified column contains a null value.

Example Usage (C#):

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;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            string sql = "SELECT CustomerID, CompanyName, ContactName FROM Customers";

            using (SqlCommand command = new SqlCommand(sql, connection))
            {
                // Execute the command and get a DataReader
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    // Check if there are any rows
                    if (reader.HasRows)
                    {
                        // Loop through each row
                        while (reader.Read())
                        {
                            // Access data by column ordinal
                            int customerId = reader.GetInt32(0);
                            string companyName = reader.GetString(1);
                            string contactName = reader.IsDBNull(2) ? "N/A" : reader.GetString(2);

                            Console.WriteLine($"ID: {customerId}, Company: {companyName}, Contact: {contactName}");

                            // Alternatively, access data by column name (less efficient)
                            // int customerIdByName = reader.GetInt32(reader.GetOrdinal("CustomerID"));
                            // string companyNameByName = reader.GetString(reader.GetOrdinal("CompanyName"));
                        }
                    }
                    else
                    {
                        Console.WriteLine("No rows found.");
                    }
                } // DataReader is automatically closed here
            }
        } // Connection is automatically closed here
    }
}

DataReader vs. DataTable

While both are used to retrieve data, they serve different purposes:

• DataReader: Best for retrieving large result sets where you process data sequentially. It's lightweight and efficient.
• DataTable: Suitable for smaller datasets that need to be manipulated, filtered, sorted, or bound to UI controls. It loads the entire dataset into memory.