title: Call stored procedure from dapper which accept list of user defined table type
body: <p>I have a stored procedure <code>InsertCars</code> which accepts list of user defined table type <code>CarType</code>.</p>
  
  <pre><code>CREATE TYPE dbo.CarType
  AS TABLE
  (
      CARID int null,
      CARNAME varchar(800) not null,
  );
  
  CREATE PROCEDURE dbo.InsertCars
      @Cars AS CarType READONLY
  AS
  -- RETURN COUNT OF INSERTED ROWS
  END
  </code></pre>
  
  <p>I need call this stored procedure from Dapper. I googled it and found some solutions.</p>
  
  <pre><code> var param = new DynamicParameters(new{CARID= 66, CARNAME= "Volvo"});
  
   var result = con.Query("InsertCars", param, commandType: CommandType.StoredProcedure);
  </code></pre>
  
  <p>But I get an error:</p>
  
  <blockquote>
    <p>Procedure or function InsertCars has too many arguments specified</p>
  </blockquote>
  
  <p>Also stored procedure <code>InsertCars</code> returns the count of inserted rows; I need get this value.</p>
  
  <p>Where is the root of problem?</p>
  
  <p>My problem is also that I have cars in generic list <code>List&lt;Car&gt; Cars</code> and I want pass this list to store procedure. It exist elegant way how to do it ?</p>
  
  <pre><code>public class Car
  {
      public CarId { get; set; }
      public CarName { get; set; }
  }
  </code></pre>
  
  <p>Thank you for help</p>
  
  <p><strong>EDITED</strong></p>
  
  <p>I found solutions</p>
  
  <p><a href="https://stackoverflow.com/questions/6232978/does-dapper-support-sql-2008-table-valued-parameters">Does Dapper support SQL 2008 Table-Valued Parameters?</a></p>
  
  <p>or </p>
  
  <p><a href="https://stackoverflow.com/questions/6913610/does-dapper-support-sql-2008-table-valued-parameters-2">Does Dapper support SQL 2008 Table-Valued Parameters 2?</a></p>
  
  <p>So I try make own stupid helper class</p>
  
  <pre><code>class CarDynamicParam : Dapper.SqlMapper.IDynamicParameters
  {
      private Car car;
  
      public CarDynamicParam(Car car)
      {
          this.car = car;
      }
  
      public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
      {
          var sqlCommand = (SqlCommand)command;
  
          sqlCommand.CommandType = CommandType.StoredProcedure;
  
          var carList = new List&lt;Microsoft.SqlServer.Server.SqlDataRecord&gt;();
  
          Microsoft.SqlServer.Server.SqlMetaData[] tvpDefinition =
                                                                  {
  
                                                                      new Microsoft.SqlServer.Server.SqlMetaData("CARID", SqlDbType.Int),
                                                                      new Microsoft.SqlServer.Server.SqlMetaData("CARNAME", SqlDbType.NVarChar, 100),
                                                                  };
  
          var rec = new Microsoft.SqlServer.Server.SqlDataRecord(tvpDefinition);
          rec.SetInt32(0, car.CarId);
          rec.SetString(1, car.CarName);
  
          carList.Add(rec);
  
          var p = sqlCommand.Parameters.Add("Cars", SqlDbType.Structured);
          p.Direction = ParameterDirection.Input;
          p.TypeName = "CarType";
          p.Value = carList;
      }
  }
  </code></pre>
  
  <p>Use </p>
  
  <pre><code>var result = con.Query("InsertCars", new CarDynamicParam(car), commandType: CommandType.StoredProcedure);
  </code></pre>
  
  <p>I get exception</p>
  
  <blockquote>
    <p>When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id.</p>
  </blockquote>
  
  <p>StackTrace:</p>
  
  <pre><code>   at Dapper.SqlMapper.GetDynamicDeserializer(IDataRecord reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in c:\Dev\Dapper\Dapper\SqlMapper.cs:line 1308
     at Dapper.SqlMapper.GetDeserializer(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in c:\Dev\Dapper\Dapper\SqlMapper.cs:line 1141
     at Dapper.SqlMapper.&lt;QueryInternal&gt;d__d`1.MoveNext() in c:\Dev\Dapper\Dapper\SqlMapper.cs:line 819
     at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
     at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
     at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in c:\Dev\Dapper\Dapper\SqlMapper.cs:line 770
     at Dapper.SqlMapper.Query(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType) in c:\Dev\Dapper\Dapper\SqlMapper.cs:line 715
  </code></pre>
  
  <p>What is wrong?</p>
  
  <p><strong>FIXED:</strong></p>
  
  <p>Call <code>con.Execute</code> instead <code>con.Query</code></p>