Does Dapper support SQL 2008 Table-Valued Parameters?

.net dapper

Question

Does anyone know if is possible to pass table-valued parameter data to a stored procedure with Dapper?

Accepted Answer

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.


Expert Answer

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.




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why