Dapper: Data table generator for table-valued parameters

[cpx86]

When using .AsTableValuedParameter(), is there any reason why it does not generate IL code for creating a DataTable from the object passed in? As it is currently, you have to create the DataTable in user code differently for every table type that you want to use.

I'm thinking that in the same way POCOs are being mapped to the table schema for constructing the DbCommand, they could be mapped to the table type for constructing the DataTable. With an extension method like the existing ones, the API would be:

// Given a table type KeyValueTableType with columns Key and Value
var keyValueTableParameter = new [] { 
    new KeyValueTableType { Key = "abc", Value = "qwe" },
    new KeyValueTableType { Key = "xyz", Value = "asd" }
}.AsTableValuedParameter();
// would have signature ICustomQueryParameter AsTableValuedParameter<T>(IEnumerable<T> rows)

The extension method above would (in addition to it's current implementation) be generating a dynamic method that essentially does:

var table = new DataTable();
table.Columns.Add("Key", typeof(string));
table.Columns.Add("Value", typeof(string));
foreach (var row in rows)
{
    table.Rows.Add(new[] { row.Key, row.Value });
}

Is this something that you think makes sense? Or is it too specific of a use-case, and should go into contrib?

[mgravell]

I think that's a very valid question. Presumably all it would need is a new
extension method to to the work. It isn't a trivial amount of work, but it
isn't huge either. The main interest question might be around where the
custom object name comes from, and how to identify this scenario
_precisely_ - especially if it is implicit rather than explicit (a
List-of-Foo where Foo has [UdtTypeName("foo")], for example, could
potentially be implicit). It deserves some thought, but: yeah, that sounds
possible.
On 10 Nov 2015 9:59 p.m., "Christian Palmstierna" [email protected]
wrote:

When using .AsTableValuedParameter(), is there any reason why it does not
generate IL code for creating a DataTable from the object passed in? As it
is currently, you have to create the DataTable in user code differently for
every table type that you want to use.

I'm thinking that in the same way POCOs are being mapped to the table
schema for constructing the DbCommand, they could be mapped to the table
type for constructing the DataTable. With an extension method like the
existing ones, the API would be:

// Given a table type KeyValueTableType with columns Key and Value
var keyValueTableParameter = new [] {
new KeyValueTableType { Key = "abc", Value = "qwe" },
new KeyValueTableType { Key = "xyz", Value = "asd" }
}.AsTableValuedParameter(); // would have signature AsTableValuedParameter(IEnumerable rows)

Is this something that you think makes sense? Or is it too specific of a
use-case, and should go into contrib?

—
Reply to this email directly or view it on GitHub
https://github.com/StackExchange/dapper-dot-net/issues/389.

[cpx86]

Presumably all it would need is a new extension method to to the work. It isn't a trivial amount of work, but it isn't huge either.
That's my impression as well. I'm very new to the source code, but it looks like a lot of the IL generation code could be almost directly copied from SqlMapper.CreateParamInfoGenerator() since it does essentially the same thing. The only real difference is that it maps objects to an IDbCommand, whereas this extension method would map to a DataTable.

The main interest question might be around where the custom object name comes from, and how to identify this scenario *precisely* - especially if it is implicit rather than explicit (a List-of-Foo where Foo has [UdtTypeName("foo")], for example, could potentially be implicit). It deserves some thought, but: yeah, that sounds possible.
There's also the scenario where you use an anonymous type, in which case the table type name would have to be specified as a parameter to the extension method. When you do have a named type, I can see benefits with being able to have the name be implicit from the class name, overriding it with an attribute, as well as overriding it for a specific query.

[RoCore]

TVP Generation is an easy step... create columns/rows and give tabel a Table Valued Parameter Type name. MS SQL will map automatically name to type. I'm currently in Use of TVP and it works fine to me.

[cpx86]

@RomanKernSW Sure, it's possible to do the column-property mapping manually, but the whole purpose of Dapper is to not have to write that code yourself.