Does Dapper support SQL 2008 Table-Valued Parameters?

Question

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

Selfrestraint

Alternatively you can use bulkinsert to a temp table https://mcmap.net/q/41812/-correct-method-of-deleting-over-2100-rows-by-id-with-dapper – Seat

Answer 1

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.

Catarinacatarrh

A case where I am not able to add a TVP using Dapper, if I need a Non Input parameter addition along with, using Dynamic Parameters, I am not able to add TVP, please check my question @ #33088129 – Unconditional

Bah, no IEnumerable conversion? – Wickerwork

With ExecuteReader, I get, "The member events of type System.Data.DataTable cannot be used as a parameter value". – Thither

Answer 2

Yes, we support them but you will need to code your own helpers.

For example:

class IntDynamicParam : Dapper.SqlMapper.IDynamicParameters
{
    IEnumerable<int> numbers;
    public IntDynamicParam(IEnumerable<int> numbers)
    {
        this.numbers = numbers;
    }

    public void AddParameters(IDbCommand command)
    {
        var sqlCommand = (SqlCommand)command;
        sqlCommand.CommandType = CommandType.StoredProcedure;

        List<Microsoft.SqlServer.Server.SqlDataRecord> number_list = new List<Microsoft.SqlServer.Server.SqlDataRecord>();

        // Create an SqlMetaData object that describes our table type.
        Microsoft.SqlServer.Server.SqlMetaData[] tvp_definition = { new Microsoft.SqlServer.Server.SqlMetaData("n", SqlDbType.Int) };

        foreach (int n in numbers)
        {
            // Create a new record, using the metadata array above.
            Microsoft.SqlServer.Server.SqlDataRecord rec = new Microsoft.SqlServer.Server.SqlDataRecord(tvp_definition);
            rec.SetInt32(0, n);    // Set the value.
            number_list.Add(rec);      // Add it to the list.
        }

        // Add the table parameter.
        var p = sqlCommand.Parameters.Add("@ints", SqlDbType.Structured);
        p.Direction = ParameterDirection.Input;
        p.TypeName = "int_list_type";
        p.Value = number_list;

    }
}

// SQL Server specific test to demonstrate TVP 
public void TestTVP()
{
    try
    {
        connection.Execute("CREATE TYPE int_list_type AS TABLE (n int NOT NULL PRIMARY KEY)");
        connection.Execute("CREATE PROC get_ints @ints int_list_type READONLY AS select * from @ints");

        var nums = connection.Query<int>("get_ints", new IntDynamicParam(new int[] { 1, 2, 3 })).ToList();
        nums[0].IsEqualTo(1);
        nums[1].IsEqualTo(2);
        nums[2].IsEqualTo(3);
        nums.Count.IsEqualTo(3);
        connection.Execute("DROP PROC get_ints");
        connection.Execute("DROP TYPE int_list_type");

    }
}

Make sure you properly test performance for table valued params. When I tested this for passing int lists it was significantly slower than passing in multiple params.

I am totally not against having some SQL Server specific helpers for dapper in the contrib project, however the core dapper avoids adding vendor specific tricks where possible.

Brittaniebrittany

In fact using TVP is slower than "where col in @values". How can I use the List Support Feature (Dapper allow you to pass in IEnumerable<int> and will automatically parameterize your query) to pass a list of ints to a StoredProcedure? – Selfrestraint

This is one of those crazy edge cases where batches are faster than sps the technique dapper uses for list support is not compatible with stored procs – Brittaniebrittany

Can you update this? I can't quite figure out if it's OK to be ignoring the identity parameter in newer versions of Dapper. – Through

Would this code work as is or will it need IEnumerable<int> collection to be converted to a DataTable for be used as a TVP – Unconditional

Answer 3

I know this ticket is OLD, very old, but wanted to let you know that I have published Dapper.Microsoft.Sql package, which supports generic TVPs.

https://www.nuget.org/packages/Dapper.Microsoft.Sql/

Sample use:

List<char> nums = this.connection.Query<char>(
  "get_ints", 
  new TableValuedParameter<char>(
    "@ints", "int_list_Type", new[] { 'A', 'B', 'C' })).ToList();

It is based on the original classes from Dapper test project.

Enjoy!

Resort

I am trying to understand how to have a TVP as a parameter along with other typical-typed parameters. How is that done? – Rosemari

I have not implemented that part yet. – Resort

Answer 4

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.

Catarinacatarrh

correction, we sort of support it ... you just need to code it yourself :) – Brittaniebrittany

if that is SP, what is data type of @values? – Plowman