# Mapping Rows to Models

Read each row of a query into a new model object, handle NULL columns, and collect the objects in a List&lt;T>.

Journeyman · C# · T-SQL · Updated November 15, 2026

By Gregor Dzierzon

## After this lesson you can

- Map each row a SqlDataReader returns to a model object in a List&lt;T>

## Key takeaways

- For each row, create a new model with an object initializer, fill its properties from the columns, and add it to a `List<T>`.
- A `using (...) { }` block closes the connection at its closing brace, and the list still holds every row afterward.
- Check a nullable column with `IsDBNull` before you read it, or read it by name with `as string`, which turns a `NULL` into `null`.
- When several queries read the same table, write the mapping once, in a method such as `MapRowToCategory`.

Each row of `Categories` matches the `Category` model, so for each row the program creates one `Category`, fills its properties from the row's columns, and adds it to a list.

```csharp
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";

// A list to hold every category
List<Category> categories = new List<Category>();

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    string sql = "SELECT CategoryId, CategoryName, Description FROM Categories ORDER BY CategoryId;";
    using SqlCommand command = new SqlCommand(sql, connection);
    using SqlDataReader reader = command.ExecuteReader();

    // Create a Category for each row, and add it to the list
    while (reader.Read())
    {
        Category category = new Category
        {
            CategoryId = reader.GetInt32(0),
            CategoryName = reader.GetString(1),
            Description = reader.IsDBNull(2) ? null : reader.GetString(2)
        };
        categories.Add(category);
    }
}

// The connection is closed, but the data is still here
Console.WriteLine($"{categories.Count} categories");
foreach (Category category in categories)
{
    Console.WriteLine($"{category.CategoryId}. {category.CategoryName}");
}

public class Category
{
    public int CategoryId { get; set; }
    public string CategoryName { get; set; } = "";
    public string? Description { get; set; }
}
```

```text
8 categories
1. Beverages
2. Condiments
3. Confections
4. Dairy Products
5. Grains/Cereals
6. Meat/Poultry
7. Produce
8. Seafood
```

## What's new here

- **`using (...) { ... }` with a block.** This `using` closes the connection at the end of its curly braces, not at the end of the program. The connection is open only while the rows are read. After the block, `categories` still holds every category.
- **An object initializer**, as in *Auto-Properties and Read-Only Properties*: `new Category { CategoryId = ..., CategoryName = ... }` creates the object and sets its properties in one statement, one property per column.
- **`IsDBNull` for nullable columns.** `Description` allows `NULL`, so it's checked before it's read.

## A method that maps a row

When several queries read the same table, put the mapping in one method, so it's written once:

```csharp
Category MapRowToCategory(SqlDataReader reader)
{
    return new Category
    {
        CategoryId = (int)reader["CategoryId"],
        CategoryName = (string)reader["CategoryName"],
        Description = reader["Description"] as string
    };
}
```

This version reads each column by its name. `reader["Description"] as string` gives the text, or `null` when the column is `NULL`. (A `NULL` column holds the special value `DBNull.Value`, which isn't a `string`, so `as string` turns it into `null`.)

## Do it: map the shippers' rows to Shipper objects

The shipping page lists Northwind's shippers, and it wants them as `Shipper` objects it can keep after the connection closes. The rows come from this query:

```sql
SELECT ShipperId, CompanyName, Phone
FROM Shippers
ORDER BY ShipperId;
```

Its columns, by position, and the `Shipper` property each one fills:

- `0`, `ShipperId`: an `int`, for `ShipperId`.
- `1`, `CompanyName`: text, never `NULL`, for `CompanyName`.
- `2`, `Phone`: text that can be `NULL`, for `Phone`, a `string?`.

The `Shipper` model is ready, at the bottom of `Program.cs`. So is a stand-in for `SqlDataReader`, named `StandInReader`, because the practice box can't reach SQL Server: its rows are written in code, but its `Read`, `GetInt32`, `GetString` and `IsDBNull` work as `SqlDataReader`'s do. Leave both classes as they are. The tests make a reader like this:

```csharp
StandInReader reader = new StandInReader(
    new object[] { 1, "Speedy Express", "(503) 555-9831" },
    new object[] { 2, "United Package", "(503) 555-3199" },
    new object[] { 3, "Federal Shipping", DBNull.Value });
```

For those three rows, `ReadShippers(reader)` returns a list of three `Shipper` objects, in the same order:

| | ShipperId | CompanyName | Phone |
|---|---|---|---|
| 1st | 1 | `"Speedy Express"` | `"(503) 555-9831"` |
| 2nd | 2 | `"United Package"` | `"(503) 555-3199"` |
| 3rd | 3 | `"Federal Shipping"` | `null` (the column was `NULL`) |

**Your job:** write the method `List<Shipper> ReadShippers(StandInReader reader)`.

- Read the rows one at a time, in order, until there are no more.
- For each row, make a new `Shipper`, fill its three properties from the row's three columns, and add it to the list.
- When a row's `Phone` is `NULL`, set the shipper's `Phone` to `null`.
- When there are no rows, return an empty list.

Practiced live on the lesson page: write the C# right there and check it.

## Check yourself

1 question to try on the lesson page.

## So what

One loop, one object per row, one list: that's mapping. It's the bridge between the database's rows and the rest of your C# program.

## Key terms

- [DBNull](https://www.ask-codey.com/glossary/dbnull)

## Cheat sheets

- [Mapping Rows to Models](https://www.ask-codey.com/cheat-sheets/mapping-data-to-models-in-csharp-mapping-rows-to-models-cheat-sheet)

Source: https://www.ask-codey.com/lessons/mapping-data-to-models-in-csharp-mapping-rows-to-models
