Does anyone know if is possible to pass table-valued parameter data to a stored procedure with Dapper?
There is now (n Dapper 1.26 and higher) direct support for table-valued parameters baked into dapper. In the case of stored procedures, since the data type is built into the sproc API, all you need to do is supply a DataTable
:
var data = connection.Query<SomeType>(..., new {
id=123, name="abc", values = someTable
}, ...);
For direct command-text you have two other options:
use a helper method to tell it the custom data type:
var data = connection.Query<SomeType>(..., new {
id=123, name="abc", values = someTable.AsTableValuedParameter("mytype")
}, ...);
tell the data-table itself what custom data type to use:
someTable.SetTypeName("mytype");
var data = connection.Query<SomeType>(..., new {
id=123, name="abc", values = someTable
}, ...);
Any of these should work fine.
today it isn't. We actually investigated table-valed-parameters for our cheeky "in" implementation (where col in @values
), but were very unimpressed by performance. However in the context of a SPROC it makes sense.
Your best bet is to log this as an issue on the project site so we can track/prioritise it. It sounds like something will be doable, though, probably similar to the DbString or DynamicParameters options.
But today? No.