Click here to Skip to main content
15,867,453 members
Articles / Database Development / SQL Server

Passing Table Valued Parameters with Dapper

Rate me:
Please Sign up or sign in to vote.
4.90/5 (9 votes)
31 Oct 2014CPOL3 min read 120.1K   14   16
Up until version 1.26 of Dapper, it was not possible to utilize TVP.

Introduction

Dapper is a high performing micro-ORM, supporting SQL Server, MySQL, Sqlite, SqlCE, Firebird, etc., making interactions with any database quite easy to implement in a .NET solution. The introduction of version 1.26, with support for table-valued parameters, further simplifies parameterizing SQL queries.

Background

There have been many attempts at passing sets of values as a single parameter to stored procedure. Many pages of the World-Wide-Web have been filled with creative approaches to improve usability and performance, based on exchanging data as comma-delimited strings. In the end, they all fall short of passing the set as set, and letting the database use it as a Table Valued Parameter. The creators of Dapper have recognized it and allowed to pass such parameter as a DataTable.

Database Setup

Let's first create a custom type on the database itself.

SQL
CREATE TYPE [dbo].[AUDITRECORD] AS TABLE(
    [AUDIT_PK] [bigint] NULL,
    [MESSAGE] [nvarchar](500) NULL,
    [SUCCESS] [bit] NULL
)
GO

This code creates a user-defined-type in SQL server, allowing a single parameter or variable to contain multiple values in rows and columns just like a table does, hence the name Table-Valued-Parameter. A stored procedure using such parameter would be created like this:

SQL
CREATE PROCEDURE [dbo].[TestOne] @TVP dbo.AUDITRECORD READONLY
AS

DataTable Approach

First, we must define a new DataTable, followed by definition of columns, and finally the action to add rows to the newly created table. Important thing to note is that the order of the columns added must be identical to the columns defined in the database TVP.

Out of the box, Dapper provides an extension, AsTableValuedParameter, which internally converts a DataTable to a TVP of desired database side type.

C#
var dt = new DataTable();
dt.Columns.Add("AUDIT_PK", typeof (long));
dt.Columns.Add("MESSAGE", typeof (string));
dt.Columns.Add("SUCCESS", typeof (bool));
dt.Rows.Add(1, "EHLO", null);

using (var conn = new SqlConnection("Data Source=.;Initial Catalog=Scratch;Integrated Security=true"))
{
    conn.Open();
    conn.Execute("TestOne", new {TVP = dt.AsTableValuedParameter("dbo.AUDITRECORD")},
        commandType: CommandType.StoredProcedure);
}

There is actually no conversion per se performed. Here is what is occurring during the stored procedure execution:

SQL
declare @p1 dbo.AUDITRECORD
insert into @p1 values(1,N'EHLO',NULL)

exec TestOne @TVP=@p1

Quite clever. Because Dapper can be used with many database clients, instead of having to figure out how to exchange a TVP with every database, Dapper injects extra SQL code to allow for the exchange to occur, without having to define custom types on the client.

Extension

Since lazyness is a mother of invention, let's try to simplify the process. Here is what I'd actually like to pass to Dapper as TVP:

C#
var l = new List<AUDITRECORD>
{
    new AUDITRECORD {AUDIT_PK = 2, MESSAGE = "HELO", SUCCESS = true},
    new AUDITRECORD {AUDIT_PK = 3, MESSAGE = "EHLO", SUCCESS = false}
};
using (var conn = new SqlConnection
("Data Source=.;Initial Catalog=Scratch;Integrated Security=true"))
{
    conn.Open();
    conn.Execute("TestOne",
        new {TVP = l.AsTableValuedParameter("dbo.AUDITRECORD", 
        new[] {"AUDIT_PK", "MESSAGE", "SUCCESS"})},
        commandType: CommandType.StoredProcedure);
}

That is much simpler, but unfortunately not supported. Dapper only accepts a DataTable and not an IEnumerable<T>. To be able to pass a list of values, we must create an extension first:

C#
public static class Extensions
{
    /// <summary>
    /// This extension converts an enumerable set to a Dapper TVP
    /// </summary>
    /// <typeparam name="T">type of enumerbale</typeparam>
    /// <param name="enumerable">list of values</param>
    /// <param name="typeName">database type name</param>
    /// <param name="orderedColumnNames">if more than one column in a TVP, 
    /// columns order must mtach order of columns in TVP</param>
    /// <returns>a custom query parameter</returns>
    public static SqlMapper.ICustomQueryParameter AsTableValuedParameter<T>
    	(this IEnumerable<T> enumerable,
        string typeName, IEnumerable<string> orderedColumnNames = null)
    {
        var dataTable = new DataTable();
        if (typeof(T).IsValueType || typeof(T).FullName.Equals("System.String"))
        {
            dataTable.Columns.Add(orderedColumnNames==null? 
            	"NONAME":orderedColumnNames.First(), typeof(T));
            foreach (T obj in enumerable)
            {
                dataTable.Rows.Add(obj);
            }                
        }
        else
        {
            PropertyInfo[] properties = typeof(T).GetProperties
            	(BindingFlags.Public | BindingFlags.Instance);
            PropertyInfo[] readableProperties = properties.Where
            	(w => w.CanRead).ToArray();
            if (readableProperties.Length > 1 && orderedColumnNames == null)
                throw new ArgumentException("Ordered list of column names 
                must be provided when TVP contains more than one column");
            var columnNames = (orderedColumnNames ?? 
            	readableProperties.Select(s => s.Name)).ToArray();
            foreach (string name in columnNames)
            {
                dataTable.Columns.Add(name, readableProperties.Single
                	(s => s.Name.Equals(name)).PropertyType);
            }

            foreach (T obj in enumerable)
            {
                dataTable.Rows.Add(
                    columnNames.Select(s => readableProperties.Single
                    	(s2 => s2.Name.Equals(s)).GetValue(obj))
                        .ToArray());
            }
        }
        return dataTable.AsTableValuedParameter(typeName);
    }
}

Per C# rules, all extension methods must be public and static, and placed inside a public and static class.

If the type of T is ValueType, we can simply skip Reflection and add a NONAME column. This will come in handy later and is also a reason for the orderedColumnNames parameter to be optional.

Otherwise, using Reflection, we obtain a list of public getters available for a type. Then we check if we have more than one, because if we do, we also need to have an ordered, not sorted, list of column names, in the exact order as the TVP definition in the database.

Then we proceed with the boiler-plate code, creating a data table, adding column definitions with the property type, and finally adding all entities as rows to the newly created data table. When executed, we will get this:

C#
declare @p1 dbo.AUDITRECORD
insert into @p1 values(2,N'HELO',1)
insert into @p1 values(3,N'EHLO',0)

exec TestOne @TVP=@p1

If your set-based-parameters are always a list of long numbers, you can pass them to the database as list as long as you have a common TVP defined.

SQL
CREATE TYPE TVPBIGINT AS TABLE(
  [KEY] BIGINT NULL
  )

Then in your stored procedures, you can use it like this:

SQL
CREATE PROCEDURE [dbo].[TestTwo] 
  @customerKeys dbo.TVPBIGINT READONLY,
  @productKeys dbo.TVPBIGINT READONLY
AS
SET NOCOUNT ON
SELECT * FROM SomeTable WHERE 
  CUST_KEY IN (SELECT [KEY] FROM @customerKeys) 
  OR PROD_KEY IN (SELECT [KEY] FROM @productKeys) 
  -- do something with the data 
SET NOCOUNT OFF
GO

Your C# code will pass the parameters like this:

C#
using (var conn = new SqlConnection
	("Data Source=.;Initial Catalog=Scratch;Integrated Security=true"))
{
    conn.Open();
    conn.Execute("TestTwo",
        new
        {
            customerKeys = custKeyList.AsTableValuedParameter("dbo.TVPBIGINT"),
            productKeys = prodKeyList.AsTableValuedParameter("dbo.TVPBIGINT")
        },
        commandType: CommandType.StoredProcedure);
}

As long as both, custKeyList and prodKeyList are enumerables of long, the stored procedures will be executed quickly and efficiently:

SQL
declare @p1 dbo.TVPBIGINT
insert into @p1 values(N'1')
insert into @p1 values(N'3')
insert into @p1 values(N'5')

declare @p2 dbo.TVPBIGINT
insert into @p2 values(N'31')
insert into @p2 values(N'75')
insert into @p2 values(N'712')

exec TestTwo @customerKeys=@p1,@productKeys=@p2

Summary

Dapper is a great development time saver when it comes to interactions with a database, with miniscule impact on performance. When coupled with simple extensions, it provides for time and performance savings in our day-to-day data processing.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Architect BI Software, Inc.
United States United States
A seasoned IT Professional. Programming and data processing artist. Contributor to StackOverflow.

Comments and Discussions

 
QuestionPassing a TABLE parameter with Oracle to a Stored Procedure Pin
Member 1465094810-Nov-19 13:18
Member 1465094810-Nov-19 13:18 
QuestionTable Valued Parameter with string query Pin
ltuttini7-Aug-15 11:05
ltuttini7-Aug-15 11:05 
AnswerRe: Table Valued Parameter with string query Pin
ltuttini10-Aug-15 4:20
ltuttini10-Aug-15 4:20 
AnswerRe: Table Valued Parameter with string query Pin
Darek Danielewski18-Aug-15 22:24
Darek Danielewski18-Aug-15 22:24 
GeneralRe: Table Valued Parameter with string query Pin
ltuttini19-Aug-15 7:17
ltuttini19-Aug-15 7:17 
GeneralRe: Table Valued Parameter with string query Pin
Darek Danielewski19-Aug-15 8:15
Darek Danielewski19-Aug-15 8:15 
GeneralRe: Table Valued Parameter with string query Pin
ltuttini21-Aug-15 3:43
ltuttini21-Aug-15 3:43 
GeneralRe: Table Valued Parameter with string query Pin
Darek Danielewski21-Aug-15 7:02
Darek Danielewski21-Aug-15 7:02 
GeneralRe: Table Valued Parameter with string query Pin
ltuttini28-Aug-15 3:16
ltuttini28-Aug-15 3:16 
GeneralRe: Table Valued Parameter with string query Pin
Darek Danielewski28-Aug-15 4:47
Darek Danielewski28-Aug-15 4:47 
QuestionSource Code Pin
John C Rayan11-Jun-15 1:04
professionalJohn C Rayan11-Jun-15 1:04 
AnswerRe: Source Code Pin
Darek Danielewski13-Jun-15 5:52
Darek Danielewski13-Jun-15 5:52 
Please use the link on the top right corner of each code sample to copy it, as I don't have a specific solution to share. The samples have been taken out from a POC, which cannot be shared.
GeneralRe: Source Code Pin
John C Rayan13-Jun-15 10:37
professionalJohn C Rayan13-Jun-15 10:37 
Questionnice Pin
Sacha Barber1-Nov-14 20:28
Sacha Barber1-Nov-14 20:28 
AnswerRe: nice Pin
Darek Danielewski2-Nov-14 7:09
Darek Danielewski2-Nov-14 7:09 
GeneralRe: nice Pin
Sacha Barber3-Nov-14 11:17
Sacha Barber3-Nov-14 11:17 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.