I’ve used this TVP (Table Valued Parameter) helper for Dapper for quite some time now and I thought I’d share it here in case someone also thinks it is super cool.
It’s really just a wrapper around the existing AsTableValuedParameter method that comes with Dapper. The wrapper helps you quickly build a DataTable from any type of collection.
With this small helper method:
public SqlMapper.ICustomQueryParameter ToTvp<T>(
IEnumerable<T> enumerable,
params Func<T, object>[] columnSelectors)
{
if (columnSelectors.Length == 0)
{
Func<T, object> getSelf = x => x;
columnSelectors = new[] { getSelf };
}
var result = new DataTable();
foreach (var selector in columnSelectors)
{
result.Columns.Add();
}
foreach (var item in enumerable)
{
var colValues = columnSelectors.Select(selector => selector(item)).ToArray();
result.Rows.Add(colValues);
}
return result.AsTableValuedParameter();
}
You can now create TVP parameters super easy and feed them to Dapper like this:
string[] names = GenerateNames(generatedNamesCount);
var namesTvp = ToTvp(names);
conn.Execute(
"[dbo].[User_InsertFromSimpleTvp]",
new {Names = namesTvp},
commandType: CommandType.StoredProcedure);
string[] names = GenerateNames(generatedNamesCount);
User[] users = names.Select(x => new User {Name = x, Email = x + "@1800.com"}).ToArray();
var usersTvp = ToTvp(users, x => x.Name, x => x.Email);
conn.Execute(
"[dbo].[User_InsertFromComplexTvp]",
new {Users = usersTvp},
commandType: CommandType.StoredProcedure);
Pedro Liska 
Doesn’t seem finished. All columns are added without type and end up as strings. But otherwise a nice approach as it avoids reflection.
You are right Bernhard; the DataTable columns are not typed so they end up as strings. I do want to point out, mostly to future readers of this post, that dapper does convert those strings to the appropriate SQL data type when executing the SQL stored procedure.
We’ve been using this helper in all our production codebases for about 3 years now. Most of the time the limitation you are pointing out is not an issue.
We noticed this limitation about 6 months ago. If I remember right, the datatype that was giving us problems was a nullable type (int?). After looking at the issue for a while we realized that we didn’t even want to use a nullable type in that situation (int was fine). So we abandoned doing the fix.
The fix would have consisted of making a new overload of the ToTvp method where the params of Func would be params of a custom object that had the Func and the Type as properties.
Since it would have been a new overload, the original ToTvp would remain intact. We like it that way since the usage of the original ToTvp is super convenient and is the one we would use most of the time.
Happy coding!
For use with Dapper, creating a datatable from a list of objects (of the same type), this is fast:
public static SqlMapper.ICustomQueryParameter AsTvp
(this IEnumerable enumerable, string typeName, params string[] orderedColumnNames)
{
var table = new DataTable();
using (var reader = ObjectReader.Create(enumerable, orderedColumnNames))
{
table.Load(reader);
}
return table.AsTableValuedParameter(typeName);
}
This uses the Fast-Member nuget package (create by the author of Dapper) and is immensely faster than reflection and preserves original types. This is what I ended using.