Dapper - Parameter TVP

Description

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.

TVP lets you pass a table to allow you to perform “IN” clause, massive insert, and a lot of more.

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

Step 1

Let first, create the table.

  1. connection.Execute(@"
  2.     CREATE TABLE [Customer]
  3.     (
  4.         [CustomerID] [INT] IDENTITY(1,1) NOT NULL,
  5.         [Code] [VARCHAR](20) NULL,
  6.         [Name] [VARCHAR](20) NULL,
  8.         CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED 
  9.         (
  10.             [CustomerID] ASC
  11.         )
  12.     )
  13. ");

Step 2

Then create the TVP type.

  1. connection.Execute(@"
  2.     CREATE TYPE TVP_Customer AS TABLE
  3.     (
  4.         [Code] [VARCHAR](20) NULL,
  5.         [Name] [VARCHAR](20) NULL
  6.     )
  7. ");

Step 3

And finally, create the stored procedure that will take the TVP type as a parameter.

  1. connection.Execute(@"
  2.     CREATE PROCEDURE Customer_Seed
  3.         @Customers TVP_Customer READONLY
  4.     AS
  5.     BEGIN
  6.         INSERT INTO Customer (Code, Name)
  7.         SELECT Code, Name
  8.         FROM @Customers
  9.     END
  10. ");

Step 4

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

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

  1. var dt = new DataTable();
  2. dt.Columns.Add("Code");
  3. dt.Columns.Add("Name");
  5. for(int i = 0; i < 5; i++) {
  6.     dt.Rows.Add("Code_" + i, "Name_" + i);
  7. }
  9. connection.Execute("Customer_Seed", new { Customers = dt.AsTableValuedParameter("TVP_Customer") }, commandType: CommandType.StoredProcedure);

Try it: .NET Core | .NET Framework