Dapper Tutorial Dapper - Table-Valued Parameter


A Table-Valued Parameters (TVP) is a SQL Server feature that lets you pass an entire table as an input parameter for a Stored Procedure or Function. One of the best features of Dapper is its support for table-valued parameters.

  • Table-valued parameters are a great way to improve performance when you're working with large amounts of data.
  • With table-valued parameters, you can send multiple rows of data to the database in one shot.
  • This can significantly reduce the amount of time that it takes to execute a query.

To use table-valued parameters with Dapper, you need to create a custom type that maps to the data that you want to insert.

TVP lets you pass a table to allow you to perform an "IN" clause, a massive insert, and a lot more.

Here is an example that will seed customers to our Database:

Step 1

Now let's create our first table using Dapper and we will call it Customer. This table will have three columns: CustomerID, Name, and Code.

    CREATE TABLE [Customer]
        [CustomerID] [INT] IDENTITY(1,1) NOT NULL,
        [Code] [VARCHAR](20) NULL,
        [Name] [VARCHAR](20) NULL,

            [CustomerID] ASC

Step 2

To create a TVP, you first need to create a user-defined type. A user-defined type (UDT) is a data type that you can define and use in your database.

After you have created the UDT, you can create a TVP that uses the UDT as its base type. To do this, you use the CREATE TYPE statement as shown in the following example.

        [Code] [VARCHAR](20) NULL,
        [Name] [VARCHAR](20) NULL

Step 3

And finally, create the stored procedure that will take the TVP type as a parameter. To use a TVP in a stored procedure, you need to declare a parameter of the UDT type, as the following example shows:

    CREATE PROCEDURE Customer_Seed
        @Customers TVP_Customer READONLY
        INSERT INTO Customer (Code, Name)
        SELECT Code, Name
        FROM @Customers

Step 4

To use a TVP parameter, we must first need to create a DataTable with the same definition and populate it.

Now you can execute the stored procedure and pass a TVP as a parameter. The following example creates a TVP that contains five rows of data and passes it to the Customer_Seed stored procedure.

var dt = new DataTable();

for(int i = 0; i < 5; i++) {
    dt.Rows.Add("Code_" + i, "Name_" + i);
connection.Execute("Customer_Seed", new { Customers = dt.AsTableValuedParameter("TVP_Customer") }, commandType: CommandType.StoredProcedure);

In your parameter list, use the AsTableValuedParameter with the TVP type name in the parameter to execute the Stored Procedure.

Try it: .NET Core | .NET Framework