Feature request: Support for Table Value Parameters

[GoogleCodeExporter]

The feature has been brought up several times before:
http://stackoverflow.com/questions/6232978/does-dapper-support-sql-2008-table-va
lued-parameters
http://stackoverflow.com/questions/6913610/does-dapper-support-sql-2008-table-va
lued-parameters-2

Why TVP vs a large IN statement?

While a TVP is typically slower than IN(...) for inline SQL, IN(...) is not 
possible when dealing with stored procedures. Additionally, you can JOIN 
against a TVP, which is much faster than using a very large IN statement in 
SQL, especially when dealing with a large number of values. 

The bottom line: A TVP is the only way to pass an indeterminate number of 
values to a stored procedure.

Proposed solution: 

In the CreateParamInfoGenerator() method, check if the property type is a 
DataTable. If it is, treat it as a TVP. While this would be an exception from 
trying to keep Dapper vender-agnostic, it is a small break (only about 10 lines 
of code, and no reference to SqlClient) that adds support to a nice feature. 
The cool thing is, all that really gets done is giving the DataTable to the 
parameter. By not setting a type, it will infer the SqlDbType as 
SqlDbType.Structured and the TypeName without needing it to be explicitly given.

Please see the attached file for an example implementation. Added lines are 
commented with //TVP support

It enables you to pass a parameter to Dapper like:

new { exampleIntParam = 1, 
      exampleTvpParam = MyDataTable }

The problem with the IDynamicParameters solution:

The suggest solution has always been to implement an IDynamicParameters. The 
issue is that breaks you out of the normal method of providing parameters, and 
you must define a new dynamic parameter for each variant.

For example: say you want to provide a TVP, and also a simple int parameter. 
Later, you want a TVP and two int parameters. Each one requires a different 
IDynamicParameters (or to dramatically extend its functionality.

Thank you.

Original issue reported on code.google.com by smdra...@gmail.com on 28 Sep 2012 at 1:35

Attachments:

• SqlMapperWithTVPSupport.cs

[GoogleCodeExporter]

Belatedly going through prior issues, it appears another solution has already 
been approved, but has not made it to prime time yet (Issue 69). What can I do 
to make either one happen? :)

Original comment by smdra...@gmail.com on 28 Sep 2012 at 1:52

[GoogleCodeExporter]

Please consider bumping this up in priority.
Thanks!

Original comment by craft.st...@gmail.com on 28 Feb 2014 at 2:58

[GoogleCodeExporter]

Working through TVPs now; I haven't currently added as first-class, as it can 
only work reliably for stored procedures; I have added a 
.AsTableValuedParameter extension method instead, which allows use in all 
scenarios (by making it possible to specify a type name)

Original comment by marc.gravell on 2 Jun 2014 at 1:19

• Changed state: Fixed