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.
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:
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.
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:
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:
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:
public static class Extensions
{
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:
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.
CREATE TYPE TVPBIGINT AS TABLE(
[KEY] BIGINT NULL
)
Then in your stored procedures, you can use it like this:
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)
SET NOCOUNT OFF
GO
Your C# code will pass the parameters like this:
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:
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.
