# 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<T>.

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

## After this lesson you can

- Map each row a SqlDataReader returns to a model object in a List<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`.)

## 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