Parameters and Mapping to Models

Pass parameters as an anonymous object or a model, with property names that match the @ names; lists become IN lists. Columns map to properties by name, so alias any column that doesn't match.

Journeyman · C# · T-SQL · Updated

By

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:

Column Property
int NOT NULL int
int NULL int?
money or decimal decimal or decimal?
smallint short or short?
nvarchar NULL string?
bit bool
date, datetime, datetime2 DateTime

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.

Do it: match a column to its property, the way Dapper does

Dapper fills each property from the column with the same name, upper or lower case alike, and skips a column that no property matches. Write that rule yourself. Then you can tell, before you run a query, which columns need an alias.

The method gets two values:

  • column: a column's name, as the query gives it, a string ("category").
  • propertyNames: the model's property names, a List<string>. ProductSummary's are "Name", "Category" and "Supplier".

For example, three columns tried against ProductSummary:

List<string> propertyNames = new List<string> { "Name", "Category", "Supplier" };
Call Returns Why
MatchProperty("Category", propertyNames) "Category" The same name
MatchProperty("category", propertyNames) "Category" The same name in lower case: case doesn't matter
MatchProperty("CategoryName", propertyNames) null No property has that name, so Dapper skips the column. It needs AS Category.

So MatchProperty("category", propertyNames) returns "Category", the property's name as the model writes it.

Your job: write the method string? MatchProperty(string column, List<string> propertyNames).

  • Return the name from propertyNames that is the same as column, ignoring upper and lower case.
  • Return it as the list writes it ("Category", not "category").
  • When no name matches, return null.
  • When the list is empty, return null.

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

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

Cheat sheets