C# - Table-valued parameters (TVP) with Dapper

Table-valued parameters (TVP) allow you to send multiple rows of data as a parameter into SQL queries. This is useful for SELECTs and INSERTs. In this article, I’ll show how to create a TVP in SQL Server and then use it in queries with Dapper.

1 – Create the TVP in SQL Server

To be able to pass in table-valued parameters, you first have to create a table type in the database, like this:

CREATE TYPE TVP_People AS TABLE
(
	[Id] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[FavoriteNumber] [int] NOT NULL
)
Code language: SQL (Structured Query Language) (sql)

This can have any number of columns.

2 – Use TVP in a query with Dapper

Now that you have the TVP type in the database, you can use it in a query with Dapper by doing the following steps:

Create a DataTable with the same columns as the TVP.
Add rows to the DataTable.
Call Dapper method AsTableValuedParameter(“dbo.TVP_People”) to convert the DataTable to a TVP object.
Execute the query, passing in the TVP object as the parameter.

Here’s an example:

using System.Data;
using System.Data.SqlClient;
using Dapper;

//Create DataTable with same columns as TVP
var tvpPeople = new DataTable();
tvpPeople.Columns.Add("Id", typeof(int));
tvpPeople.Columns.Add("Name", typeof(string));
tvpPeople.Columns.Add("FavoriteNumber", typeof(int));

//Add data
tvpPeople.Rows.Add(1, "Jake", 7);
tvpPeople.Rows.Add(2, "Benny", 11);
tvpPeople.Rows.Add(3, "Bob", 2);

//Use the TVP as a parameter with Dapper
using (var con = new SqlConnection(connectionString))
{
    var tvp = tvpPeople.AsTableValuedParameter("dbo.TVP_People");
    
    con.Execute("INSERT INTO dbo.People SELECT * FROM @TVP_People", 
        param: new { TVP_People = tvp });
}
Code language: C# (cs)

This successfully inserts multiple rows from the TVP into the People table.

Next, I’ll rework this example to use a stored proc instead of a raw SQL query. At the end, I’ll explain errors to watch out for.

Use TVP with a stored procedure

In the example above, I showed how to use a TVP with a SQL query in the code. You can use TVPs with stored procs as well. First, create the stored proc with a parameter using the TVP type name (dbo.TVP_People) and READONLY:

CREATE PROCEDURE [dbo].[InsertPeople]
	@TVP_People dbo.TVP_People READONLY
AS
BEGIN
	INSERT INTO dbo.People SELECT * FROM @TVP_People
END
Code language: SQL (Structured Query Language) (sql)

Now create a DataTable with columns matching the TVP definition and fill it with data. Then execute the stored proc with Dapper, passing in the DataTable as a TVP parameter type:

using System.Data;
using System.Data.SqlClient;
using Dapper;

//Create DataTable with same columns as TVP
var tvpPeople = new DataTable();
tvpPeople.Columns.Add("Id", typeof(int));
tvpPeople.Columns.Add("Name", typeof(string));
tvpPeople.Columns.Add("FavoriteNumber", typeof(int));

//Add data
tvpPeople.Rows.Add(1, "Jake", 7);
tvpPeople.Rows.Add(2, "Benny", 11);
tvpPeople.Rows.Add(3, "Bob", 2);

//Use the TVP as a parameter with Dapper
using (var con = new SqlConnection(connectionString))
{
    var tvp = tvpPeople.AsTableValuedParameter("dbo.TVP_People");

    con.Execute("dbo.InsertPeople", 
        new { TVP_People  = tvp }, commandType: CommandType.StoredProcedure);
}
Code language: C# (cs)

This successfully passes the TVP to the stored proc, which inserts its rows into the People table.

Errors to watch out for

Here are three common errors to watch out for when using TVPs.

Columns in the wrong order

Columns from the DataTable are matched to the TVP by column order, not name. So make sure you add the columns to the DataTable in the same order that they are defined in the TVP. If the columns are in the wrong order, you’ll either 1) Insert data into the wrong columns or 2) Get the following conversion failure:

System.Data.SqlClient.SqlException: Conversion failed when converting the nvarchar value ‘Jake’ to data type int.

In the example above, the TVP has the following definition:

CREATE TYPE TVP_People AS TABLE
(
	[Id] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[FavoriteNumber] [int] NOT NULL
)
Code language: SQL (Structured Query Language) (sql)

The columns are defined in this order: int Id, varchar Name, int FavoriteNumber. So the columns have to be added to the DataTable in the same order:

var tvpPeople = new DataTable();
tvpPeople.Columns.Add("Id", typeof(int));
tvpPeople.Columns.Add("Name", typeof(string));
tvpPeople.Columns.Add("FavoriteNumber", typeof(int));
Code language: C# (cs)

Wrong number of columns

The DataTable must have the same number of columns as the TVP definition, otherwise you get the following exception:

SqlException: Trying to pass a table-valued parameter with 2 column(s) where the corresponding user-defined table type requires 3 column(s).

In the example above, the TVP has the following definition:

CREATE TYPE TVP_People AS TABLE
(
	[Id] [int] NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[FavoriteNumber] [int] NOT NULL
)
Code language: SQL (Structured Query Language) (sql)

It has three columns: Id, Name, FavoriteNumber. So the DataTable has to have the same number of columns:

var tvpPeople = new DataTable();
tvpPeople.Columns.Add("Id", typeof(int));
tvpPeople.Columns.Add("Name", typeof(string));
tvpPeople.Columns.Add("FavoriteNumber", typeof(int));
Code language: C# (cs)

Missing the TVP type name

If you call the Dapper method AsTableValuedParameter() with the wrong TVP type name, you get the following error:

System.ArgumentException: The table type parameter ‘TVP_People’ must have a valid type name.

In the above example, the TVP was created like this:

CREATE TYPE TVP_People AS TABLE
Code language: SQL (Structured Query Language) (sql)

Its type name is “dbo.TVP_People”, so that is what you have to pass into AsTableValuedParameter():

var tvp = tvpPeople.AsTableValuedParameter("dbo.TVP_People");
Code language: C# (cs)

C# – Table-valued parameters (TVP) with Dapper