Parameters and Mapping to Models

After this lesson you can

• Pass parameters to Dapper as an anonymous object, a model, or a list for IN
• Map columns to model properties by name, with aliases for columns that don't match

Key takeaways

• Each @Name in the SQL is filled from the property called Name on the object you pass: an anonymous object such as new { CategoryId = 1 }, or a model.
• Dapper sends values as real parameters, never pasted into the SQL, so it's as safe from SQL injection as SqlParameter. A null property is sent as NULL.
• For a list, write IN @Ids without parentheses, and Dapper expands it into one parameter per item.
• Columns fill the properties with the same name, upper or lower case alike; alias a column with AS when its name doesn't match.
• Make a property nullable, such as int?, when its column can be NULL. An int property is left at 0, and a missing value looks like a real zero.

Dapper matches names twice: parameters by their @ names, and columns by their property names. Get the names right, and everything else is automatic.

Parameters: an object whose names match

Every @Name in the SQL is filled from the property called Name on the object you pass. An anonymous object is the quickest way to make one:

using Dapper;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
using SqlConnection connection = new SqlConnection(connectionString);

List<Product> products = connection.Query<Product>("""
    SELECT ProductId, ProductName, UnitPrice
    FROM Products
    WHERE CategoryId = @CategoryId AND UnitPrice < @MaxPrice
    ORDER BY UnitPrice, ProductName;
    """, new { CategoryId = 1, MaxPrice = 15m }).ToList();

foreach (Product product in products)
{
    Console.WriteLine(quot;{product.ProductName}: {product.UnitPrice:0.00}");
}

public class Product
{
    public int ProductId { get; set; }
    public string ProductName { get; set; } = "";
    public decimal? UnitPrice { get; set; }
}

Guaraná Fantástica: 4.50
Rhönbräu Klosterbier: 7.75
Laughing Lumberjack Lager: 14.00
Sasquatch Ale: 14.00

new { CategoryId = 1, MaxPrice = 15m } creates an object of a type with no name, with the two properties. They're sent as real parameters, never pasted into the SQL, so Dapper code is as safe from SQL injection as SqlParameter was.

A model works too: when its properties have the right names, pass the whole object. That's how a DAO's Add usually looks:

public int Add(Product product)
{
    string sql = """
        INSERT INTO Products (ProductName, CategoryId, UnitPrice)
        VALUES (@ProductName, @CategoryId, @UnitPrice);
        SELECT CAST(SCOPE_IDENTITY() AS int);
        """;

    using SqlConnection connection = new SqlConnection(connectionString);
    return connection.QuerySingle<int>(sql, product);
}

null properties are sent as SQL's NULL: no more DBNull.Value.

Lists become IN lists

Give a parameter a list, and write IN @Name without parentheses. Dapper expands it into one parameter per item:

using Dapper;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
using SqlConnection connection = new SqlConnection(connectionString);

List<int> ids = new List<int> { 1, 2, 77 };
List<string> names = connection.Query<string>(
    "SELECT ProductName FROM Products WHERE ProductId IN @Ids ORDER BY ProductName;", new { Ids = ids }).ToList();

Console.WriteLine(string.Join(", ", names));

Chai, Chang, Original Frankfurter grüne Soße

Query<string> reads a single column straight into strings: no model needed.

Mapping: columns to properties, by name

Dapper fills each property from the column with the same name (upper or lower case doesn't matter). A column with no matching property is skipped, and a property with no matching column keeps its default. So when names differ, alias the column in the SQL:

using Dapper;
using Microsoft.Data.SqlClient;

string connectionString = "Server=localhost;Database=Northwind;Integrated Security=true;TrustServerCertificate=true;";
using SqlConnection connection = new SqlConnection(connectionString);

List<ProductSummary> summaries = connection.Query<ProductSummary>("""
    SELECT TOP (3) p.ProductName AS Name
        , c.CategoryName AS Category
        , s.CompanyName AS Supplier
    FROM Products AS p
    INNER JOIN Categories AS c ON p.CategoryId = c.CategoryId
    INNER JOIN Suppliers AS s ON p.SupplierId = s.SupplierId
    ORDER BY p.ProductId;
    """).ToList();

foreach (ProductSummary summary in summaries)
{
    Console.WriteLine(quot;{summary.Name} ({summary.Category}) from {summary.Supplier}");
}

public class ProductSummary
{
    public string Name { get; set; } = "";
    public string Category { get; set; } = "";
    public string Supplier { get; set; } = "";
}

Chai (Beverages) from Exotic Liquids
Chang (Beverages) from Exotic Liquids
Aniseed Syrup (Condiments) from Exotic Liquids

A model doesn't have to match a table: ProductSummary matches this query, with columns from three tables.

Types and NULL

Each property's type must fit its column's type:

If a column can be NULL, make the property nullable. Dapper doesn't complain when it can't store a NULL in an int property: it leaves the property at 0, and a missing value quietly looks like a real zero.

Check yourself

2 questions to try on the lesson page.

So what

Names do all the work: @ names match parameter properties, column names match model properties. Alias what doesn't match, make nullable what can be NULL, and Dapper fills your models for you.

Key terms

• Parameterized Query
• Anonymous Type
• Column Alias
• Nullable Value Type

Cheat sheets

• Parameters and Mapping to Models