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?

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.

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.