TVP Helper for Dapper ORM

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);