Introduction

Yes, Dapper can work with SQL Server table-valued parameters, even though the feature is really provided by SqlClient and SQL Server rather than by a heavy Dapper abstraction. In practice you pass a structured parameter through Dapper, usually with DynamicParameters and AsTableValuedParameter.

This is useful when you need to send many rows to a stored procedure without building a giant IN list or issuing one command per row. Table-valued parameters, usually shortened to TVPs, are efficient, typed, and much easier to maintain than comma-separated strings.

How TVPs Fit Into Dapper

SQL Server requires a user-defined table type before you can send a TVP. That type defines the column layout the procedure expects.

1CREATE TYPE dbo.IntIdList AS TABLE
2(
3    Id INT NOT NULL PRIMARY KEY
4);
5GO
6
7CREATE PROCEDURE dbo.GetUsersByIds
8    @Ids dbo.IntIdList READONLY
9AS
10BEGIN
11    SET NOCOUNT ON;
12
13    SELECT u.Id, u.DisplayName
14    FROM dbo.Users AS u
15    INNER JOIN @Ids AS i ON i.Id = u.Id;
16END;
17GO

On the C# side, Dapper can pass a DataTable or an IEnumerable<SqlDataRecord> as a TVP. The DataTable approach is usually simplest.

1using System;
2using System.Data;
3using System.Data.SqlClient;
4using Dapper;
5
6public static class Demo
7{
8    public static void Main()
9    {
10        var table = new DataTable();
11        table.Columns.Add("Id", typeof(int));
12        table.Rows.Add(1);
13        table.Rows.Add(5);
14        table.Rows.Add(8);
15
16        using var connection = new SqlConnection(
17            "Server=localhost;Database=AppDb;Trusted_Connection=True;TrustServerCertificate=True;");
18
19        var parameters = new DynamicParameters();
20        parameters.Add("@Ids", table.AsTableValuedParameter("dbo.IntIdList"));
21
22        var users = connection.Query(
23            "dbo.GetUsersByIds",
24            parameters,
25            commandType: CommandType.StoredProcedure);
26
27        foreach (var user in users)
28        {
29            Console.WriteLine(user);
30        }
31    }
32}

That is the important point: Dapper does support the scenario. You just need to provide the parameter in the form SQL Server expects.

Why TVPs Are Better Than String Lists

Without TVPs, developers often pass a string like 1,5,8 and split it in SQL. That has several drawbacks:

• You lose strong typing.
• Validation becomes more complicated.
• Query plans are harder to reason about.
• Large lists become awkward and error-prone.

A TVP keeps the input relational. SQL Server can join against it naturally, and the procedure signature clearly documents the expected shape.

For very high-throughput code, IEnumerable<SqlDataRecord> can avoid some DataTable overhead, but most application code is fine with DataTable unless profiling proves otherwise.

Important Dapper Details

Dapper is intentionally thin. It does not try to hide every ADO.NET concept. That is why TVP support can feel easy to miss if you expect a dedicated “TVP API” with lots of wrappers.

The actual responsibilities break down like this:

• SQL Server defines the table type and procedure contract.
• 'SqlClient understands structured parameters.'
• Dapper passes those parameters through with minimal ceremony.

That design is consistent with the rest of Dapper. The library is excellent when you already know the SQL you want and simply need convenient parameter binding and row mapping.

Common Pitfalls

The most common failure is forgetting that TVPs are READONLY. If you try to modify the TVP variable inside the stored procedure, SQL Server rejects it. Treat it as an input set, not a temp table.

Another common issue is a type mismatch between the SQL table type and the DataTable schema. Column names, order, and types should line up with the user-defined table type. If SQL expects INT and the DataTable contains strings, the call will fail.

Developers also sometimes pass the wrong type name to AsTableValuedParameter. Use the full SQL type name, such as dbo.IntIdList, not the procedure name or an arbitrary label.

Finally, TVPs are a SQL Server feature. If you switch database engines, do not assume the same pattern exists with the same API shape.

Summary

• Dapper supports SQL Server table-valued parameters through ADO.NET integration.
• Define a user-defined table type in SQL Server first.
• Use DynamicParameters plus AsTableValuedParameter in C#.
• TVPs are cleaner and safer than comma-separated string lists.
• Most problems come from schema mismatches or the wrong SQL type name.