Published / updated29 Aug 2026

Dapper Parameters and Stored Procedures

Before you start

You need: Dapper basics (Article 07).

Time: about 45 minutes, plus the practice.

Learning objective

Pass any parameter shape Dapper supports, call stored procedures with output and return values, and control the SQL type when it matters.

Topics

• Anonymous object parameters
• Entity objects as parameters
• DynamicParameters
• Controlling type, size, precision and direction
• Stored procedures
• Output and return values
• IN lists
• Optional filters
• Type handlers

Anonymous parameters

List<Student> students = connection.Query<Student>(
    @"SELECT s.Id, s.Name, s.RollNumber
      FROM   dbo.Student AS s
      WHERE  s.SchoolId = @SchoolId AND s.ClassName = @ClassName",
    new { SchoolId = schoolId, ClassName = className }).ToList();

Property names match parameter names, case-insensitively. Order is irrelevant, and extra properties that the SQL does not reference are ignored — so passing a whole entity is safe.

These become real SqlParameter objects. Nothing is concatenated, so nothing is injectable.

// The entity itself works as the parameter source
connection.Execute(
    @"INSERT INTO dbo.Student (PublicId, SchoolId, Name, RollNumber, ClassName, Section,
                               DateOfBirth, ParentName, ParentPhone, Address, Status)
      VALUES (@PublicId, @SchoolId, @Name, @RollNumber, @ClassName, @Section,
              @DateOfBirth, @ParentName, @ParentPhone, @Address, @Status);",
    student);

What Dapper infers

Dapper picks the DbType from the .NET type, and for strings it defaults to NVARCHAR sized at 4000 (or MAX beyond that).

That default is fine most of the time and wrong in one important case: a VARCHAR column compared against an NVARCHAR parameter. SQL Server converts the column rather than the parameter, so the index cannot be used and the query scans. The fix is covered under DynamicParameters below.

DynamicParameters

When the parameter set is built conditionally, or the type must be stated explicitly.

DynamicParameters parameters = new DynamicParameters();
parameters.Add("@SchoolId", schoolId, DbType.Int32);
parameters.Add("@RollNumber", rollNumber, DbType.AnsiString, size: 20);
parameters.Add("@Amount", amount, DbType.Decimal, precision: 18, scale: 2);

List<Student> students = connection.Query<Student>(sql, parameters).ToList();

DbType	SQL Server type
DbType.String	NVARCHAR
DbType.AnsiString	VARCHAR
DbType.StringFixedLength	NCHAR
DbType.AnsiStringFixedLength	CHAR
DbType.Int32	INT
DbType.Byte	TINYINT
DbType.Decimal	DECIMAL
DbType.Date	DATE
DbType.DateTime2	DATETIME2
DbType.Guid	UNIQUEIDENTIFIER

DbType.AnsiString is the one to remember. For a VARCHAR column, it prevents the implicit conversion that defeats the index. On a large table this is the difference between a seek and a scan, with no other change to the code.

Set precision and scale for money. Without them, 8000.50 can arrive rounded.

Building parameters conditionally

public List<Student> Search(int schoolId, string? term, string? className, int page, int pageSize)
{
    DynamicParameters parameters = new DynamicParameters();
    parameters.Add("@SchoolId", schoolId, DbType.Int32);
    parameters.Add("@Skip", (page - 1) * pageSize, DbType.Int32);
    parameters.Add("@PageSize", pageSize, DbType.Int32);

    StringBuilder where = new StringBuilder(
        "WHERE s.SchoolId = @SchoolId AND s.Status <> 1");

    if (!string.IsNullOrWhiteSpace(term))
    {
        where.Append(" AND (s.Name LIKE @Search OR s.RollNumber LIKE @Search)");
        parameters.Add("@Search", "%" + term + "%", DbType.String, size: 102);
    }

    if (!string.IsNullOrWhiteSpace(className))
    {
        where.Append(" AND s.ClassName = @ClassName");
        parameters.Add("@ClassName", className, DbType.String, size: 10);
    }

    string sql = $@"
        SELECT   s.Id, s.PublicId, s.SchoolId, s.Name, s.RollNumber, s.ClassName,
                 s.Section, s.DateOfBirth, s.ParentName, s.ParentPhone, s.Address, s.Status
        FROM     dbo.Student AS s
        {where}
        ORDER BY s.ClassName, s.Section, s.Name, s.Id
        OFFSET   @Skip ROWS FETCH NEXT @PageSize ROWS ONLY;";

    using (IDbConnection connection = new SqlConnection(_connectionString))
    {
        return connection.Query<Student>(sql, parameters).ToList();
    }
}

Only fixed SQL fragments are appended — never a caller's value. Every value goes through parameters.Add. That distinction is what keeps a dynamically built WHERE clause safe.

The alternative, avoiding string building entirely:

WHERE s.SchoolId = @SchoolId
  AND s.Status <> 1
  AND (@Search IS NULL OR s.Name LIKE @Search OR s.RollNumber LIKE @Search)
  AND (@ClassName IS NULL OR s.ClassName = @ClassName)

Simpler, and one cached plan instead of four. The cost is that a single plan serves every filter combination, which can be poor when the data is skewed — add OPTION (RECOMPILE) if it becomes a problem. Prefer this form until you measure a reason not to.

Stored procedures

List<Student> students = connection.Query<Student>(
    "dbo.usp_GetStudentsByClass",
    new { SchoolId = schoolId, ClassName = className, Section = (string?)null },
    commandType: CommandType.StoredProcedure).ToList();

commandType: CommandType.StoredProcedure is the whole difference. Omit it and Dapper sends the name as a literal statement, producing "Incorrect syntax near 'usp_GetStudentsByClass'".

Dapper passes only the parameters the anonymous object declares. A procedure parameter with a default can be omitted; one without a default causes "Procedure or function expects parameter '@X', which was not supplied."

Output and return values

public StudentCreationResult Create(Student student)
{
    DynamicParameters parameters = new DynamicParameters();
    parameters.Add("@SchoolId", student.SchoolId, DbType.Int32);
    parameters.Add("@Name", student.Name, DbType.String, size: 100);
    parameters.Add("@RollNumber", student.RollNumber, DbType.AnsiString, size: 20);
    parameters.Add("@ClassName", student.ClassName, DbType.String, size: 10);
    parameters.Add("@Section", student.Section, DbType.String, size: 1);
    parameters.Add("@DateOfBirth", student.DateOfBirth, DbType.Date);
    parameters.Add("@ParentName", student.ParentName, DbType.String, size: 100);
    parameters.Add("@ParentPhone", student.ParentPhone, DbType.AnsiString, size: 15);
    parameters.Add("@Address", student.Address, DbType.String, size: 250);

    parameters.Add("@NewId", dbType: DbType.Int32, direction: ParameterDirection.Output);
    parameters.Add("@NewPublicId", dbType: DbType.Guid, direction: ParameterDirection.Output);
    parameters.Add("@Return", dbType: DbType.Int32, direction: ParameterDirection.ReturnValue);

    using (IDbConnection connection = new SqlConnection(_connectionString))
    {
        connection.Execute("dbo.usp_CreateStudent", parameters,
                           commandType: CommandType.StoredProcedure);

        return new StudentCreationResult
        {
            Id = parameters.Get<int>("@NewId"),
            PublicId = parameters.Get<Guid>("@NewPublicId"),
            StatusCode = parameters.Get<int>("@Return")
        };
    }
}

Read them back with parameters.Get<T>(name), after the command completes. Reading before execution returns the default.

An output parameter the procedure never assigns comes back as DBNull, and Get<int> on it throws. Use Get<int?> when the procedure has paths that skip the assignment.

ParameterDirection.ReturnValue captures the procedure's RETURN value — a status code only, since it can hold nothing but an int.

IN lists

Dapper expands a collection automatically, which is its nicest trick.

int[] studentIds = { 12, 18, 31, 44 };

List<Student> students = connection.Query<Student>(
    @"SELECT s.Id, s.Name, s.RollNumber
      FROM   dbo.Student AS s
      WHERE  s.SchoolId = @SchoolId AND s.Id IN @Ids",
    new { SchoolId = schoolId, Ids = studentIds }).ToList();

No parentheses around @Ids. Dapper rewrites it to IN (@Ids1, @Ids2, @Ids3, @Ids4) and adds one parameter per element. Writing IN (@Ids) produces a syntax error, and it is the first thing to check when this fails.

Two limits:

• An empty collection becomes IN (SELECT 1 WHERE 1 = 0) — matches nothing, which is usually correct, but check it is what you want.
• Each distinct length is a different plan. A list of 3 and a list of 4 cache separately. For large or highly variable lists, use a table-valued parameter instead.

DataTable idTable = new DataTable();
idTable.Columns.Add("Id", typeof(int));

foreach (int id in studentIds)
{
    idTable.Rows.Add(id);
}

List<Student> students = connection.Query<Student>(
    "dbo.usp_GetStudentsByIds",
    new { SchoolId = schoolId, Ids = idTable.AsTableValuedParameter("dbo.IntList") },
    commandType: CommandType.StoredProcedure).ToList();

Nulls

// null → DBNull.Value automatically
connection.Execute(sql, new { Address = (string?)null });

One case needs care. A null in a WHERE comparison does not match:

-- Never matches, even for rows where Section IS NULL
WHERE s.Section = @Section

-- Handles both
WHERE (@Section IS NULL OR s.Section = @Section)

-- Or, to match rows whose value is genuinely NULL
WHERE (s.Section = @Section OR (s.Section IS NULL AND @Section IS NULL))

Dapper passes the null correctly; SQL's three-valued logic is what discards the row. This is a SQL behaviour, not a Dapper one, and it is worth recognising because the C# looks entirely reasonable.

Type handlers

For a type Dapper does not know:

public class DateOnlyTypeHandler : SqlMapper.TypeHandler<DateOnly>
{
    public override DateOnly Parse(object value)
    {
        return DateOnly.FromDateTime((DateTime)value);
    }

    public override void SetValue(IDbDataParameter parameter, DateOnly value)
    {
        parameter.DbType = DbType.Date;
        parameter.Value = value.ToDateTime(TimeOnly.MinValue);
    }
}

// Register once at startup
SqlMapper.AddTypeHandler(new DateOnlyTypeHandler());

Now DateOnly properties map in both directions. Register handlers once — in Program.cs or a static constructor — not per request.

Enums need no handler. Dapper converts a numeric column to an enum automatically, though it does not validate that the number is a defined member — (StudentStatus)9 maps without complaint. The CHECK constraint on the column is the real defence.

Errors you will hit

Message	Cause	Fix
Must declare the scalar variable	Parameter missing from the anonymous object	Add it
An IN clause matches nothing	Passed a joined string instead of a collection	Dapper expands a list automatically — pass the list
Output parameter is empty	Read before the query completed	Read DynamicParameters after execution
CommandType ignored	Not set for a stored procedure	Pass commandType: CommandType.StoredProcedure
A LIKE search returns everything	Wildcards embedded in user input	Escape them

Dapper expands a list into an IN clause for you. Pass new { Ids = idList } with WHERE Id IN @Ids — building the string yourself is both unnecessary and an injection risk.

Common mistakes

• Omitting commandType: CommandType.StoredProcedure
• Default NVARCHAR against a VARCHAR column, defeating the index
• No precision and scale on a money parameter
• Reading an output parameter before the command completes
• Get<int> on an output parameter the procedure did not assign
• Writing IN (@Ids) with parentheses
• An empty IN collection whose "matches nothing" behaviour was not intended
• A very large IN list, producing one plan per length
• Appending a caller's value into a dynamically built WHERE clause
• WHERE s.Section = @Section expecting it to match NULL
• Registering a type handler per request
• Assuming an enum conversion validates the value

Practice

The course exercise is call procedures with output parameters.

1. Convert a repository method to DynamicParameters with explicit DbType and size for every parameter.
2. Create a VARCHAR(20) indexed RollNumber column. Query it with the default anonymous parameter and capture the execution plan. Then use DbType.AnsiString and compare. Look for CONVERT_IMPLICIT on the column in the first plan.
3. Call usp_CreateStudent with two output parameters and a return value. Read all three.
4. Read an output parameter before calling Execute. Record what you get.
5. Add a procedure path that skips assigning an output parameter. Call Get<int> on it and record the exception, then fix it with Get<int?>.
6. Query with IN @Ids for a list of four. Then write IN (@Ids) and record the error.
7. Pass an empty array to the same query. Confirm it returns nothing and explain the generated SQL.
8. Build a conditional WHERE with DynamicParameters and three optional filters. Confirm every value is a parameter and nothing is concatenated.
9. Rewrite the same search with the (@X IS NULL OR col = @X) pattern. Compare readability and the number of cached plans.
10. Query WHERE s.Section = @Section with @Section null against data containing NULL sections. Confirm no rows, then fix it.
11. Write and register a DateOnly type handler and use it in a round trip.

Then run the AI drill — review generated SQL for parameterization. Ask an assistant for a Dapper search method with optional filters. Check specifically for: values concatenated into the SQL, IN (@Ids) with parentheses, missing commandType on a procedure call, and a default NVARCHAR against a VARCHAR column.

You can now

• Pass any parameter shape Dapper supports
• Use DynamicParameters for output and return values
• Call stored procedures with the right CommandType
• Let Dapper expand a list into an IN clause
• Read output parameters after execution

Review questions

1. Why does DbType.AnsiString matter for a VARCHAR column?
2. Why is IN @Ids written without parentheses?
3. When must you read an output parameter, and what happens if you read it earlier?
4. Why does WHERE s.Section = @Section fail to match rows where Section is NULL?

Next: Mapping, async and transactions