SqlDataReader & Result Processing

Level: Beginner

ℹ️ Where This Fits

SqlDataReader is the fast, lightweight way to read SELECT results. Understanding how to access columns efficiently and process large result sets is essential for performance.

ℹ️ What You'll Learn

• SqlDataReader purpose: Read SELECT query results row by row (lightweight, fast, forward-only)
• Forward-only nature: Read rows in order, cannot go backward, must process sequentially
• Memory efficiency: Only one row in memory at a time (great for large datasets)
• Creating reader: var reader = await cmd.ExecuteReaderAsync()
• Reading rows: while (await reader.ReadAsync()) advances to next row
• Column access by name: (string)reader["ColumnName"] (clearest, slowest)
• Column access by index: (string)reader[0] (fastest, requires knowledge of column order)
• GetString/GetInt32/etc: reader.GetString(0) typed accessors (fast + typed)
• FieldCount: reader.FieldCount returns number of columns
• GetName: reader.GetName(0) returns column name by index
• Null handling: Check reader.IsDBNull(0) before casting
• School Management reading: Get all students, get exam results, get attendance records
• Multiple result sets: .NextResult() moves to next SELECT in query
• GetOrdinal optimization: Cache column indexes with reader.GetOrdinal("Name") for repeated reads
• Performance: DataReader faster than DataTable/DataSet for large reads
• Common mistakes: Not disposing reader (resource leak), not checking IsDBNull, inefficient column access

Basic Row Reading

using (var conn = new SqlConnection(connString))
{
    await conn.OpenAsync();
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = "SELECT Id, Name, ClassName, DateOfBirth FROM Students WHERE Status = @status";
        cmd.Parameters.AddWithValue("@status", "Active");

        using (var reader = await cmd.ExecuteReaderAsync())
        {
            var students = new List<Student>();
            
            while (await reader.ReadAsync())
            {
                students.Add(new Student
                {
                    Id = (int)reader["Id"],
                    Name = (string)reader["Name"],
                    ClassName = (string)reader["ClassName"],
                    DateOfBirth = (DateTime)reader["DateOfBirth"]
                });
            }
            
            return students;  // 5000 students read efficiently
        }
    }
}

Null Handling

while (await reader.ReadAsync())
{
    var id = (int)reader["Id"];
    var name = (string)reader["Name"];
    
    // Handle nullable phone (some students have no phone)
    string phone = null;
    if (!reader.IsDBNull(reader.GetOrdinal("Phone")))
    {
        phone = (string)reader["Phone"];
    }

    students.Add(new Student 
    { 
        Id = id, 
        Name = name, 
        Phone = phone 
    });
}

Typed Accessors (Fast)

while (await reader.ReadAsync())
{
    var id = reader.GetInt32(0);        // Column 0 as int
    var name = reader.GetString(1);     // Column 1 as string
    var dob = reader.GetDateTime(2);    // Column 2 as DateTime
    
    // Faster than casting (int)reader[0]
}

GetOrdinal Optimization

Cache column indexes when reading many rows:

using (var reader = await cmd.ExecuteReaderAsync())
{
    int idOrdinal = reader.GetOrdinal("Id");
    int nameOrdinal = reader.GetOrdinal("Name");
    int classOrdinal = reader.GetOrdinal("ClassName");
    
    while (await reader.ReadAsync())
    {
        var student = new Student
        {
            Id = reader.GetInt32(idOrdinal),
            Name = reader.GetString(nameOrdinal),
            ClassName = reader.GetString(classOrdinal)
        };
        students.Add(student);
    }
}

Multiple Result Sets

cmd.CommandText = @"
    SELECT Id, Name FROM Students WHERE ClassName = @className;
    SELECT Id, ExamName FROM Exams WHERE Status = 'Active';";

using (var reader = await cmd.ExecuteReaderAsync())
{
    var students = new List<Student>();
    
    // First result set - Students
    while (await reader.ReadAsync())
    {
        students.Add(new Student
        {
            Id = (int)reader["Id"],
            Name = (string)reader["Name"]
        });
    }
    
    // Move to second result set
    await reader.NextResultAsync();
    
    var exams = new List<Exam>();
    while (await reader.ReadAsync())
    {
        exams.Add(new Exam
        {
            Id = (int)reader["Id"],
            ExamName = (string)reader["ExamName"]
        });
    }
}

Performance Tips

✓ Use GetOrdinal for frequently read columns ✓ Use typed accessors (GetInt32, GetString) instead of casting ✓ Always dispose reader (use using) ✓ Process and discard rows (don't load entire result in memory) ✓ Use DataReader for large datasets (not DataTable)

💡 Memory Efficiency

DataReader processes one row at a time. Loading 100,000 students:

• DataTable: Loads all 100,000 in memory at once
• DataReader: One row at a time (constant memory)

For large datasets, DataReader is essential.

🤖Use AI to Learn Faster

Use ChatGPT, Claude, or Copilot to go deeper on SqlDataReader & Result Processing. Try these prompts:

• "What's the difference between DataReader and DataTable for reading results?"
• "How do you handle NULL values when reading from SqlDataReader?"
• "Why is GetOrdinal useful for performance optimization?"
• "How do you read multiple result sets from one query?"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article

-> 06. Transactions & Batch Operations