C# – How to use table-valued parameters (TVP) with ADO.NET, Dapper, and EF Core

Table-valued parameters (TVP) allow you to send multiple rows of data as a parameter into SQL queries.

TVPs are flexible. They can be used in both raw SQL queries and with stored procs, and can be used in all types of queries, from inserts to selects.

In this article, I’ll show how to use TVPs by doing bulk insertions. They can be used for way more than this, but this is one of the most common scenarios. I’ll be showing examples using ADO.NET, Dapper, and EF Core. At the end, I’ll show a performance comparison between doing a bulk insertion with TVP vs SqlBulkCopy.

Note: In all examples, I’m using the DataTable approach instead of the DataReader streaming approach.

1 – Create the TVP type in the database

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

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

This can have any number of columns.

2 – Populate a DataTable

In order to insert a TVP from the code, you first need to create and populate a DataTable, like this:

DataTable tvpPeople = new DataTable(); tvpPeople.Columns.Add(nameof(Person.Id), typeof(string)); tvpPeople.Columns.Add(nameof(Person.Name), typeof(string)); tvpPeople.Columns.Add(nameof(Person.BirthDate), typeof(DateTime)); tvpPeople.Columns.Add(nameof(Person.FavoriteMovie), typeof(string)); tvpPeople.Columns.Add(nameof(Person.FavoriteNumber), typeof(int)); foreach (var person in people) { var row = tvpPeople.NewRow(); row[nameof(Person.Id)] = person.Id; row[nameof(Person.Name)] = person.Name; row[nameof(Person.BirthDate)] = person.BirthDate ?? (object)DBNull.Value; row[nameof(Person.FavoriteMovie)] = person.FavoriteMovie; row[nameof(Person.FavoriteNumber)] = person.FavoriteNumber ?? (object)DBNull.Value; tvpPeople.Rows.Add(row); }
Code language: C# (cs)

Note: The column order in the DataTable must match the column order in the TVP definition.

3 – Use the TVP in a query

You can use TVPs with a stored proc or raw SQL query. I’ll show examples of both below.

Use the TVP with a raw SQL query

The key to passing in a table-valued parameter is using a SqlParameter with SqlDbType.Structured and setting the TypeName property to the TVP type you created in the database in step 1 (dbo.TVP_People).

using(var connection = new SqlConnection(GetConnectionString())) { connection.Open(); using (var command = new SqlCommand("INSERT INTO People (Id, Name, BirthDate, FavoriteMovie, FavoriteNumber) SELECT Id, Name, BirthDate, FavoriteMovie, FavoriteNumber FROM @TVP_People", connection)) { var param = command.Parameters.AddWithValue("@TVP_People", tvpPeople); param.SqlDbType = SqlDbType.Structured; param.TypeName = "dbo.TVP_People"; command.ExecuteNonQuery(); } }
Code language: C# (cs)

Use the TVP with a stored proc

Add the TVP as a readonly parameter in the stored proc definition. Then you can select from it, like this:

CREATE PROCEDURE InsertPeople @TVP_People dbo.TVP_People READONLY AS BEGIN INSERT INTO People (Id, Name, BirthDate, FavoriteMovie, FavoriteNumber) SELECT Id, Name, BirthDate, FavoriteMovie, FavoriteNumber FROM @TVP_People END
Code language: SQL (Structured Query Language) (sql)

In the code, add the TVP as a SqlParameter with SqlDbType.Structured and set the TypeName to the TVP type you created in step 1 (dbo.TVP_People).

using(var connection = new SqlConnection(GetConnectionString())) { connection.Open(); using (var command = new SqlCommand("dbo.InsertPeople", connection)) { command.CommandType = CommandType.StoredProcedure; var param = command.Parameters.AddWithValue("@TVP_People", tvpPeople); param.SqlDbType = SqlDbType.Structured; param.TypeName = "dbo.TVP_People"; command.ExecuteNonQuery(); } }
Code language: C# (cs)

Examples of using a TVP with Dapper and EF Core

In steps 1-3 above, I showed how to use TVPs with ADO.NET. You can also use TVPs with Dapper and EF Core. I’ll show examples of this below. I’ll be using the TVP, DataTable, and stored proc shown earlier in the article.

TVP with Dapper

If you don’t already have the Dapper nuget package installed, get it with the following command (View > Other Windows > Package Manager Console):

Install-Package Dapper
Code language: PowerShell (powershell)

To use a TVP with Dapper, you have to call AsTableValuedParameter(“TVP type name”) on your DataTable and pass it in like this:

using Dapper; using (var connection = new SqlConnection(GetConnectionString())) { connection.Execute("dbo.InsertPeople", new { TVP_People = tvpPeople.AsTableValuedParameter("dbo.TVP_People") }, commandType: CommandType.StoredProcedure); }
Code language: C# (cs)

This is a bit simpler than using ADO.NET, and it has similar performance.

TVP with EF Core

If you don’t already have the EF Core SQL Server package, get it with the following command (View > Other Windows > Package Manager Console):

Install-Package Microsoft.EntityFrameworkCore.SqlServer
Code language: PowerShell (powershell)

Here’s how to pass in a TVP when using EF Core:

using Microsoft.EntityFrameworkCore; var optionsBuilder = new DbContextOptionsBuilder<DbContext>(); optionsBuilder.UseSqlServer(GetConnectionString()); using (var dbContext = new DbContext(optionsBuilder.Options)) { var tvpParameter = new Microsoft.Data.SqlClient.SqlParameter("@TVP_People", SqlDbType.Structured) { Value = tvpPeople, TypeName = "dbo.TVP_People" }; dbContext.Database.ExecuteSqlInterpolated($"dbo.InsertPeople {tvpParameter}"); }
Code language: C# (cs)

Note: This is manually creating a DbContext object.

Notice this is similar to what you have to do when using ADO.NET. You have to pass in a SqlParameter using SqlDbType.Structured and you have to specify the TVP type name.

Use Microsoft.Data.SqlClient.SqlParameter

If you try to pass a System.Data.SqlClient.SqlParameter to ExecuteSqlInterpolated(), you’ll get the following confusing error:

System.InvalidCastException: The SqlParameterCollection only accepts non-null SqlParameter type objects, not SqlParameter objects.

This is only confusing because it’s not showing the full type names. This exception really means it’s expecting you to use Microsoft.Data.SqlClient.SqlParameter instead of System.Data.SqlClient.SqlParameter.

Performance comparison between TVP bulk insertion and SqlBulkCopy when using a DataTable

TVP is an alternative to using SqlBulkCopy for bulk insertion. SqlBulkCopy performs a BULK INSERT, and is much faster than doing multiple inserts. But what performs better – TVP bulk insertion or SqlBulkCopy?

Microsoft claims that TVP bulk insertion is faster when inserting fewer than 1000 records.

To compare the performance of the two approaches, I inserted 10, 500, 1000, and 10,000 records into an empty table. I executed each operation 10 times and took the average execution time in milliseconds using a Stopwatch. To make the approaches comparable, I used a DataTable when inserting.

For the TVP bulk insertion approach, I used ADO.NET with a stored proc, since it’s faster than using Dapper and EF Core.

Results

Here are the performance comparison results:

10 records500 records1000 records10,000 records
TVP bulk insertion with ADO.NET8 ms17 ms44 ms958 ms
SqlBulkCopy7.5 ms18 ms20 ms122 ms
Multiple individual inserts8.5 ms168 ms305 ms3100 ms

Note: I added results for multiple individual inserts for reference.

TVP bulk insertion and SqlBulkCopy perform about the same when inserting 500 or fewer records. After that, SqlBulkCopy outperforms the TVP approach. At 1000 records, it’s 2x faster. At 10,000 records, it’s ~8x faster.

SqlBulkCopy is specialized for bulk insertions, whereas TVP is a general purpose option. If you need to do simple, well-perform bulk insertions, use SqlBulkCopy.

Leave a Comment