Mapping Rows to Models

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.

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(quot;{categories.Count} categories");
foreach (Category category in categories)
{
    Console.WriteLine(quot;{category.CategoryId}. {category.CategoryName}");
}

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

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:

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

Cheat sheets

• Mapping Rows to Models