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)
    foreach (var item in enumerable)
        var colValues = columnSelectors.Select(selector => selector(item)).ToArray();
    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);
    new {Names = namesTvp},
    commandType: CommandType.StoredProcedure);


string[] names = GenerateNames(generatedNamesCount);
User[] users = names.Select(x => new User {Name = x, Email = x + ""}).ToArray();
var usersTvp = ToTvp(users, x => x.Name, x => x.Email);
    new {Users = usersTvp},
    commandType: CommandType.StoredProcedure);

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s