Passing Table-Valued Parameters From .NET To SQL Server (ADO.NET, Dapper, and EF Core)

Sending a batch of records from .NET to SQL Server shouldn’t require looping individual INSERT statements or wrestling with ad-hoc SQL strings. While data access abstractions often hide database engine capabilities, SQL Server provides a dedicated mechanism for passing tabular data in a single round-trip: Table-Valued Parameters (TVPs).

In this blog post, we’ll explore what user-defined table types and TVPs are, when to reach for them over alternatives like SqlBulkCopy, and how to use them from .NET applications using modern ADO.NET (Microsoft.Data.SqlClient), Dapper, and Entity Framework Core. (If you’re configuring your database setup from scratch, you might also find my guide on validating connection strings on .NET startup useful.)

What Is A User-Defined Table Type

SQL Server is a relational database, and in many instances, as a developer, we will be dealing with tables, columns, and rows. The table structure allows us to express the idea of “many” when outputting results. SQL Server enables us to create tables as containers for information, and these are known as user-defined table types. Developers commonly use these types as input parameters to stored procedures and user-defined functions.

If you want to see if any of your current databases have any user-defined table types, you will need to go to the following structure in your database.

Programmability > Types > User-Defined Table Types

Creating a User-Defined Table Type

Creating a user-defined table type is as simple as creating a regular table.

CREATE TYPE FunTableType
   AS TABLE
      ( ActivityName VARCHAR(50)
      , Rating INT );

Here I defined a type of FunTableType, which we will use in a simple stored procedure that echoes our rows back.

/* Create a procedure to receive data for the table-valued parameter. */
CREATE PROCEDURE dbo.sp_EchoFun
   @TVP FunTableType READONLY
      AS
      SET NOCOUNT ON
      select * from @TVP

Finally, let’s call our stored procedure.

/* Declare a variable that references the type. */
DECLARE @activities AS FunTableType;
/* Add data to the table variable. */
INSERT INTO @activities (ActivityName, Rating) Values ('swimming', 5)

/* Pass the table variable data to a stored procedure. */
EXEC sp_EchoFun @activities;

We are creating our user-defined table in memory, inserting values into it, then calling our stored procedure. Putting it all together, you can see the results.

When Is This Useful

So when would you want to use a user-defined table? In my personal experience, the following scenarios provide the most value.

1. Input parameters into stored procedures that need to take in logical concepts as rows.
2. Inserting multiple rows more efficiently without resorting to SqlBulkCopy.

There is considerable overhead to using this feature, so use it judiciously. I explain some caveats in the next section.

Table-Valued Caveats

Before you start using user-defined table types, you should consider their strengths and weaknesses.

1. User-defined tables cannot be used to define columns in other database tables.
2. User-defined tables require permission to access and use them in stored procedures.
3. As the name implies, the data needs to be tabular.
4. Managing user-defined tables is like any other schema-based object.
5. Depending on your implementation, building the parameter in your codebase can be cumbersome.

With SqlCommand and SqlParameter

If you are not a fan of higher-level frameworks or libraries, you can pass user-defined table types using ADO.NET and modern Microsoft.Data.SqlClient.

using System.Data;
using Microsoft.Data.SqlClient;

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

var table = new DataTable();
table.Columns.Add("ActivityName", typeof(string));
table.Columns.Add("Rating", typeof(int));

var row = table.NewRow();
row["ActivityName"] = "swimming";
row["Rating"] = 1;
table.Rows.Add(row);

await using var command = connection.CreateCommand();
command.CommandText = "dbo.sp_EchoFun";
command.CommandType = CommandType.StoredProcedure;

var parameter = new SqlParameter("@TVP", SqlDbType.Structured)
{
    TypeName = "dbo.FunTableType",
    Value = table
};

command.Parameters.Add(parameter);

await using var reader = await command.ExecuteReaderAsync();

var result = new DataTable();
result.Load(reader);

There are a couple of notable parts of this implementation:

1. We use the DataTable type to construct our table structure in memory.
2. We add our rows to the data table.
3. The command is of type CommandType.StoredProcedure. While not strictly required if you use EXEC, when used, make sure parameter names match your stored procedure definition.
4. The parameter explicitly sets SqlDbType.Structured. Setting this tells the SQL Server driver to handle the parameter as a structured table type.
5. The parameter TypeName specifies the schema and name of the user-defined table type in the database (dbo.FunTableType).

For simplicity, I just reloaded the result set back into a DataTable. You can see the results here.

With Dapper

You can make your code more accessible and reduce boilerplate by using a Micro-ORM like Dapper. Here is an example of passing table-valued parameters with Dapper.

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

public class Fun
{
    public string ActivityName { get; set; }
    public int Rating { get; set; }
}

async static Task Main(string[] args)
{
    await using var connection = new SqlConnection(connectionString);
    await connection.OpenAsync();

    var table = new DataTable();
    table.Columns.Add("ActivityName", typeof(string));
    table.Columns.Add("Rating", typeof(int));

    var row = table.NewRow();
    row["ActivityName"] = "swimming";
    row["Rating"] = 1;
    table.Rows.Add(row);

    var result = await connection.QueryAsync<Fun>("EXEC sp_EchoFun @activities", new
    {
        activities = table.AsTableValuedParameter("dbo.FunTableType")
    });
}

Let’s look at the result.

Things to note in this example:

1. We still need to create a DataTable.
2. We call Dapper’s extension method AsTableValuedParameter to wrap our data table into a TVP parameter.
3. We still need to tell Dapper what the database type name is.
4. Results are serialized directly into our strongly-typed C# objects.

Converting Collections to DataTables

Manually configuring DataTable columns and rows every time you want to send a batch of objects gets tedious fast. We can write a reusable extension method that converts any IEnumerable<T> into a DataTable suitable for Table-Valued Parameters:

using System;
using System.Collections.Generic;
using System.Data;
using System.Reflection;

public static class TableValuedParameterExtensions
{
    public static DataTable ToDataTable<T>(this IEnumerable<T> items, string typeName)
    {
        var table = new DataTable(typeName);
        var properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

        foreach (var prop in properties)
        {
            var propType = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
            table.Columns.Add(prop.Name, propType);
        }

        foreach (var item in items)
        {
            var row = table.NewRow();
            foreach (var prop in properties)
            {
                row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;
            }
            table.Rows.Add(row);
        }

        return table;
    }
}

With this helper in place, passing an arbitrary list of domain models to Dapper is clean and readable:

var activities = new List<Fun>
{
    new() { ActivityName = "swimming", Rating = 5 },
    new() { ActivityName = "running", Rating = 4 },
    new() { ActivityName = "cycling", Rating = 5 }
};

var result = await connection.QueryAsync<Fun>(
    "EXEC sp_EchoFun @activities",
    new
    {
        activities = activities.ToDataTable("dbo.FunTableType")
            .AsTableValuedParameter("dbo.FunTableType")
    }
);

High-Performance Tip: For high-throughput scenarios or huge batches, allocating a DataTable creates memory pressure and triggers garbage collection. You can stream records without DataTable allocations by implementing IEnumerable<SqlDataRecord> or using libraries like FastMember (ObjectReader.Create(items)), which exposes an IDataReader directly over in-memory collections.

With Entity Framework Core

Entity Framework Core is the go-to ORM for many .NET applications. While EF Core does not natively translate in-memory collections into SQL Server TVPs within LINQ queries, you can easily execute stored procedures or raw SQL statements with TVPs using Database.ExecuteSqlRawAsync or FromSqlRaw.

Here is how you pass a TVP using EF Core:

using System.Data;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;

public async Task ExecuteStoredProcedureWithTvpAsync(
    MyDbContext context,
    IEnumerable<Fun> activities)
{
    var parameter = new SqlParameter("@TVP", SqlDbType.Structured)
    {
        TypeName = "dbo.FunTableType",
        Value = activities.ToDataTable("dbo.FunTableType")
    };

    await context.Database.ExecuteSqlRawAsync(
        "EXEC dbo.sp_EchoFun @TVP",
        parameter
    );
}

If your procedure returns tabular data that matches an entity or unmapped keyless type configured in your DbContext, you can query it with SqlQueryRaw:

var results = await context.Database
    .SqlQueryRaw<Fun>("EXEC dbo.sp_EchoFun @TVP", parameter)
    .ToListAsync();

(If you are coordinating complex procedural logic in SQL Server from EF Core, you might also be interested in my post on using SQL Server cursors with Entity Framework Core.)

When to Choose TVPs vs. Alternatives

Developers frequently wonder whether they should use Table-Valued Parameters, SqlBulkCopy, JSON strings, or multiple INSERT statements. The right choice depends on batch size, schema rigidity, and performance requirements:

Frequently Asked Questions (FAQ)

Can I modify a Table-Valued Parameter inside a stored procedure?

No. TVPs in SQL Server are strictly READONLY. You cannot perform UPDATE, DELETE, or INSERT directly on a TVP parameter inside stored procedures or user-defined functions. If you need to mutate incoming data, insert the TVP rows into a local #temp table or @table_variable first.

Do Table-Valued Parameters cause plan regression?

SQL Server evaluates TVPs similarly to table variables: the query optimizer estimates a fixed cardinality (often 1 row, or 100 rows in modern cardinality estimators) because SQL Server does not maintain column distribution statistics on table variables. For large or variable batch sizes where query plans depend on row volume, add OPTION (RECOMPILE) to the statement inside your stored procedure.

Does Entity Framework Core support TVPs in LINQ?

EF Core does not support querying TVPs directly via LINQ expressions. However, as demonstrated above, you can execute stored procedures or raw SQL queries using Database.ExecuteSqlRawAsync and SqlQueryRaw by passing an explicit SqlParameter with SqlDbType.Structured.

Should I use System.Data.SqlClient or Microsoft.Data.SqlClient?

Always use Microsoft.Data.SqlClient. The legacy System.Data.SqlClient package has been deprecated and does not receive new feature updates or performance optimizations for modern .NET versions (.NET 6, 8, 9+).

Conclusion

Table-valued parameters and user-defined table types are a powerful combination. As a .NET developer, they allow you to write clean, set-based database operations while avoiding unnecessary network round-trips. Whether you are using raw ADO.NET, Dapper, or Entity Framework Core, TVPs give you a reliable bridge between in-memory C# collections and relational database engines.

Some referenced material:

• Microsoft Learn: Use Table-Valued Parameters (Database Engine)
• Microsoft Learn: Table-Valued Parameters in ADO.NET
• Dapper Micro-ORM on GitHub