Published / updated29 Aug 2026

Readers and Object Mapping

Before you start

You need: parameters (Article 03).

Time: about 45 minutes, plus the practice.

Learning objective

Map any result set to C# objects without null-reference or cast exceptions, and know what a micro-ORM will do for you later.

Topics

• How SqlDataReader streams rows
• GetOrdinal versus hard-coded indexes
• Typed getters and IsDBNull
• Nullable columns
• Enums and GUIDs
• Writing a reusable mapper
• Multiple result sets
• Why this is the code Dapper removes

How a reader works

using (SqlDataReader reader = command.ExecuteReader())
{
    while (reader.Read())
    {
        // one row at a time
    }
}

A reader is forward-only and connection-bound. Rows stream from the server as you read them; there is no going back, no row count in advance, and the connection is unusable for anything else until the reader is closed.

That design is why it is fast — nothing is buffered — and why leaking one exhausts the pool.

GetOrdinal

// Fragile: breaks silently when the SELECT list changes order
student.Name = reader.GetString(3);

// Robust
student.Name = reader.GetString(reader.GetOrdinal("Name"));

Hard-coded indexes are the most common source of a mapping bug that appears months later, when someone adds a column to the SELECT list and every index after it shifts by one. The failure is usually an InvalidCastException — but when two adjacent columns share a type, it is worse: the data is simply wrong, with no error at all.

GetOrdinal does a name lookup per call, so hoist it out of the loop when mapping many rows:

int idOrdinal         = reader.GetOrdinal("Id");
int publicIdOrdinal   = reader.GetOrdinal("PublicId");
int nameOrdinal       = reader.GetOrdinal("Name");
int rollNumberOrdinal = reader.GetOrdinal("RollNumber");
int addressOrdinal    = reader.GetOrdinal("Address");

while (reader.Read())
{
    Student student = new Student();
    student.Id = reader.GetInt32(idOrdinal);
    student.PublicId = reader.GetGuid(publicIdOrdinal);
    student.Name = reader.GetString(nameOrdinal);
    student.RollNumber = reader.GetString(rollNumberOrdinal);

    students.Add(student);
}

On 100,000 rows this is a measurable difference. On 50 it does not matter — but the habit costs nothing.

GetOrdinal throws IndexOutOfRangeException for a name not in the result set, which is a clear failure at the first row rather than a silent wrong answer.

Typed getters

Getter	Returns
GetInt32 / GetInt16 / GetInt64	Integers
GetDecimal	decimal
GetDouble / GetFloat	Floating point
GetString	string
GetBoolean	bool
GetDateTime	DateTime
GetGuid	Guid
GetByte	byte
GetValue	object

Typed getters read the value directly with no boxing. GetValue returns object and then needs a cast, which is slower and loses compile-time checking.

The getter must match the column's SQL type exactly. A TINYINT column read with GetInt32 throws InvalidCastException — the provider does not widen for you:

SQL type	Getter
INT	GetInt32
TINYINT	GetByte
SMALLINT	GetInt16
BIGINT	GetInt64
BIT	GetBoolean
DECIMAL / NUMERIC	GetDecimal
NVARCHAR / VARCHAR	GetString
DATE / DATETIME2	GetDateTime
UNIQUEIDENTIFIER	GetGuid

The Status TINYINT column caught out most people — GetInt32 on it fails at run time with a message that names neither the column nor the expected type.

IsDBNull

// Throws SqlNullValueException when Address is NULL
student.Address = reader.GetString(addressOrdinal);

// Correct
if (reader.IsDBNull(addressOrdinal))
{
    student.Address = null;
}
else
{
    student.Address = reader.GetString(addressOrdinal);
}

Every nullable column needs this check. Code that omits it works for as long as the column happens to be populated, then fails on the first row where it is not — often in production, often for one specific record.

A helper keeps the mapper readable:

private static string? GetNullableString(SqlDataReader reader, int ordinal)
{
    if (reader.IsDBNull(ordinal))
    {
        return null;
    }

    return reader.GetString(ordinal);
}

private static decimal? GetNullableDecimal(SqlDataReader reader, int ordinal)
{
    if (reader.IsDBNull(ordinal))
    {
        return null;
    }

    return reader.GetDecimal(ordinal);
}

reader.GetFieldValue<T>(ordinal) is the generic form, and GetFieldValue<int?> handles nulls — but it boxes, so for hot paths the explicit check is better.

The absent-marks case

ExamResult result = new ExamResult();
result.IsAbsent = reader.GetBoolean(isAbsentOrdinal);
result.MarksObtained = GetNullableDecimal(reader, marksOrdinal);

Mapping a NULL to 0 here is the bug that prints an absent student as having failed. decimal? preserves the distinction; decimal cannot.

Enums and GUIDs

// Status is TINYINT in SQL, StudentStatus in C#
student.Status = (StudentStatus)reader.GetByte(statusOrdinal);

student.PublicId = reader.GetGuid(publicIdOrdinal);

A cast from a number to an enum does not validate. (StudentStatus)9 compiles, runs, and produces an enum value that matches no member — and then falls through every switch to default.

Validate when the data source is not fully trusted:

byte rawStatus = reader.GetByte(statusOrdinal);

if (!Enum.IsDefined(typeof(StudentStatus), rawStatus))
{
    throw new InvalidOperationException(
        $"Student {student.Id} has an unrecognised status value {rawStatus}.");
}

student.Status = (StudentStatus)rawStatus;

The CHECK constraint from the SQL Server track is the better defence — it stops the bad value being stored at all. This check is the second line.

A reusable mapper

private static Student MapStudent(SqlDataReader reader)
{
    Student student = new Student();

    student.Id = reader.GetInt32(reader.GetOrdinal("Id"));
    student.PublicId = reader.GetGuid(reader.GetOrdinal("PublicId"));
    student.SchoolId = reader.GetInt32(reader.GetOrdinal("SchoolId"));
    student.Name = reader.GetString(reader.GetOrdinal("Name"));
    student.RollNumber = reader.GetString(reader.GetOrdinal("RollNumber"));
    student.ClassName = reader.GetString(reader.GetOrdinal("ClassName"));
    student.Section = reader.GetString(reader.GetOrdinal("Section"));
    student.DateOfBirth = reader.GetDateTime(reader.GetOrdinal("DateOfBirth"));
    student.ParentName = reader.GetString(reader.GetOrdinal("ParentName"));
    student.ParentPhone = reader.GetString(reader.GetOrdinal("ParentPhone"));
    student.Address = GetNullableString(reader, reader.GetOrdinal("Address"));
    student.Status = (StudentStatus)reader.GetByte(reader.GetOrdinal("Status"));

    return student;
}

One mapper per entity, used by every query returning that entity. That is the only way to keep mapping consistent — mapping inline in each method guarantees that one of them eventually forgets an IsDBNull check.

The mapper depends on the SELECT list. Every query feeding MapStudent must return all twelve columns. A query selecting only three throws IndexOutOfRangeException from GetOrdinal.

Two ways to handle that: always select the full column set for an entity, or write narrower mappers for narrower queries. Do not use SELECT * to make the mapper safe — it reintroduces the column-exposure problem and still breaks if a column is renamed.

Multiple result sets

public PagedResult<Student> SearchStudents(int schoolId, string? term, int page, int pageSize)
{
    const string sql = @"
        SELECT COUNT(*)
        FROM   dbo.Student AS s
        WHERE  s.SchoolId = @SchoolId AND s.Status <> 1
          AND  (@Term IS NULL OR s.Name LIKE @Like OR s.RollNumber LIKE @Like);

        SELECT   s.Id, s.PublicId, s.SchoolId, s.Name, s.RollNumber, s.ClassName,
                 s.Section, s.DateOfBirth, s.ParentName, s.ParentPhone, s.Address, s.Status
        FROM     dbo.Student AS s
        WHERE    s.SchoolId = @SchoolId AND s.Status <> 1
          AND    (@Term IS NULL OR s.Name LIKE @Like OR s.RollNumber LIKE @Like)
        ORDER BY s.ClassName, s.Section, s.Name, s.Id
        OFFSET   @Skip ROWS FETCH NEXT @PageSize ROWS ONLY;";

    PagedResult<Student> result = new PagedResult<Student>();
    result.Page = page;
    result.PageSize = pageSize;

    using (SqlConnection connection = new SqlConnection(_connectionString))
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        command.Parameters.Add("@SchoolId", SqlDbType.Int).Value = schoolId;
        command.Parameters.Add("@Term", SqlDbType.NVarChar, 100).Value =
            (object?)term ?? DBNull.Value;
        command.Parameters.Add("@Like", SqlDbType.NVarChar, 102).Value = "%" + term + "%";
        command.Parameters.Add("@Skip", SqlDbType.Int).Value = (page - 1) * pageSize;
        command.Parameters.Add("@PageSize", SqlDbType.Int).Value = pageSize;

        connection.Open();

        using (SqlDataReader reader = command.ExecuteReader())
        {
            reader.Read();
            result.TotalCount = reader.GetInt32(0);

            reader.NextResult();

            while (reader.Read())
            {
                result.Items.Add(MapStudent(reader));
            }
        }
    }

    return result;
}

One round trip for both the count and the page. NextResult() moves to the second set; forgetting it leaves the reader on the count and the row loop finds nothing.

Note the deterministic ORDER BY ending in s.Id — without a unique tiebreaker, rows can repeat across pages.

Streaming versus materialising

// Dangerous: the reader is still open when the caller iterates
public IEnumerable<Student> GetStudents(int schoolId)
{
    using (SqlConnection connection = new SqlConnection(_connectionString))
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        connection.Open();

        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                yield return MapStudent(reader);   // lazy
            }
        }
    }
}

yield return makes the method lazy, so the connection stays open for as long as the caller takes to enumerate — including while it renders a page or calls a web service. A caller that abandons the enumeration part-way leaves the connection open until garbage collection.

Return a materialised list unless you have a specific reason to stream:

public List<Student> GetStudents(int schoolId)
{
    List<Student> students = new List<Student>();

    // ... read fully inside the using blocks ...

    return students;
}

Streaming is right for genuinely large exports. For a page of results it is a liability.

What Dapper removes

Compare the mapper above with its Dapper equivalent:

public List<Student> GetStudentsByClass(int schoolId, string className)
{
    const string sql = @"
        SELECT   s.Id, s.PublicId, s.SchoolId, s.Name, s.RollNumber, s.ClassName,
                 s.Section, s.DateOfBirth, s.ParentName, s.ParentPhone, s.Address, s.Status
        FROM     dbo.Student AS s
        WHERE    s.SchoolId = @SchoolId AND s.ClassName = @ClassName AND s.Status <> 1
        ORDER BY s.Section, s.Name;";

    using (SqlConnection connection = new SqlConnection(_connectionString))
    {
        return connection.Query<Student>(sql, new { SchoolId = schoolId, ClassName = className })
                         .ToList();
    }
}

Dapper does exactly what MapStudent does — GetOrdinal by name, typed getters, IsDBNull checks — using generated IL, so it is roughly as fast as hand-written code. What it removes is the opportunity to forget a null check.

Everything you have written in this article still happens. Knowing it is why you can debug Dapper when a column does not map, rather than guessing.

Errors you will hit

Message	Cause	Fix
System.IndexOutOfRangeException: Name	Column not in the result set, or renamed	Check the SELECT list
Data is Null. This method or property cannot be called on Null values	Read a NULL into a non-nullable type	IsDBNull first
Specified cast is not valid	Column type does not match the property	Check the SQL type
Invalid attempt to read when no data is present	Read before Read() returned true	Call Read() first
A property is silently null	Column alias does not match the property name	Alias the column to match

DBNull.Value is not null. A database NULL arrives as DBNull.Value, and comparing it to null is false — which is why IsDBNull exists.

Common mistakes

• Hard-coded ordinals, silently wrong after a SELECT list change
• GetOrdinal inside the row loop on a large result set
• GetString on a nullable column with no IsDBNull check
• Mapping a NULL decimal to 0, so absent students appear to have failed
• GetInt32 on a TINYINT column
• Casting a raw number to an enum without validating it
• Mapping inline in each method rather than one mapper per entity
• A mapper used by a query that does not select all its columns
• SELECT * to keep a mapper happy
• Forgetting NextResult() between result sets
• Returning a lazy IEnumerable<T> that outlives the connection
• Non-deterministic ORDER BY with OFFSET paging

Practice

The course exercise is map reader data to objects.

1. Write MapStudent covering all twelve columns, with IsDBNull on every nullable one.
2. Change the SELECT list order and confirm the mapper still works. Then rewrite one line with a hard-coded ordinal and confirm it breaks.
3. Swap two adjacent columns of the same type in the SELECT list, with hard-coded ordinals. Confirm no exception is thrown and the data is wrong. This is the failure mode worth seeing once.
4. Read a NULL address with GetString. Record the exception, then fix it.
5. Read the Status TINYINT column with GetInt32. Record the exception, then use GetByte.
6. Insert a Status value of 9 directly in SSMS, then map it. Confirm the invalid enum. Add Enum.IsDefined validation, then add the CHECK constraint that prevents it entirely.
7. Map an absent exam result into decimal? and confirm it is null, not 0.
8. Write the paged search with two result sets. Remove NextResult() and record what happens.
9. Hoist GetOrdinal out of the loop, then time both versions over 100,000 rows.
10. Write a method returning IEnumerable<Student> with yield return. Call it, take the first item, and abandon the rest. Then check for an open connection.

Then run the course debugging exercise — investigate a null mapping. Given a Student whose Address comes back as null when the database has a value, work through the three causes in order: the column is not in the SELECT list, the mapper reads the wrong ordinal, or IsDBNull is being called on the wrong ordinal.

You can now

• Map a result set to objects without null or cast errors
• Test IsDBNull before reading a nullable column
• Say why DBNull.Value is not null
• Alias columns so they match property names
• Keep the reader open only as long as needed

Review questions

1. Why are hard-coded ordinals more dangerous than they look?
2. What exception does GetString throw on a NULL column, and how do you prevent it?
3. Why does GetInt32 fail on a TINYINT column?
4. Why is returning a lazy IEnumerable<T> from a repository method a problem?

Next: ADO.NET CRUD and procedures