c# - Call stored procedure from dapper which accept list of user defined table type -

i have stored procedure insertcars accepts list of user defined table type cartype.

create type dbo.cartype table (     carid int null,     carname varchar(800) not null, );  create procedure dbo.insertcars     @cars cartype readonly -- return count of inserted rows end 

i need call stored procedure dapper. googled , found solutions.

 var param = new dynamicparameters(new{carid= 66, carname= "volvo"});   var result = con.query("insertcars", param, commandtype: commandtype.storedprocedure); 

but error:

procedure or function insertcars has many arguments specified

also stored procedure insertcars returns count of inserted rows; need value.

where root of problem?

my problem have cars in generic list list<car> cars , want pass list store procedure. exist elegant way how ?

public class car {     public carid { get; set; }     public carname { get; set; } } 

thank help

edited

i found solutions

does dapper support sql 2008 table-valued parameters?

or

does dapper support sql 2008 table-valued parameters 2?

so try make own stupid helper class

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<microsoft.sqlserver.server.sqldatarecord>();          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;     } } 

use

var result = con.query("insertcars", new cardynamicparam(car), commandtype: commandtype.storedprocedure); 

i exception

when using multi-mapping apis ensure set spliton param if have keys other id.

stacktrace:

   @ dapper.sqlmapper.getdynamicdeserializer(idatarecord reader, int32 startbound, int32 length, boolean returnnulliffirstmissing) in c:\dev\dapper\dapper\sqlmapper.cs:line 1308    @ dapper.sqlmapper.getdeserializer(type type, idatareader reader, int32 startbound, int32 length, boolean returnnulliffirstmissing) in c:\dev\dapper\dapper\sqlmapper.cs:line 1141    @ dapper.sqlmapper.<queryinternal>d__d`1.movenext() in c:\dev\dapper\dapper\sqlmapper.cs:line 819    @ system.collections.generic.list`1..ctor(ienumerable`1 collection)    @ system.linq.enumerable.tolist[tsource](ienumerable`1 source)    @ 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    @ 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 

what wrong?

fixed:

call con.execute instead con.query

my problem have cars in generic list list cars , want pass list store procedure. exist elegant way how ?

you need convert generic list car datatable , pass storedprocedure. point note order of fields must same defined in user defined table type in database. otherwise data not save properly. , must have same number of columns well.

i use method convert list datatable. can call yourlist.todatatable()

public static datatable todatatable<t>(this list<t> ilist)     {         datatable datatable = new datatable();         propertydescriptorcollection propertydescriptorcollection =             typedescriptor.getproperties(typeof(t));         (int = 0; < propertydescriptorcollection.count; i++)         {             propertydescriptor propertydescriptor = propertydescriptorcollection[i];             type type = propertydescriptor.propertytype;              if (type.isgenerictype && type.getgenerictypedefinition() == typeof(nullable<>))                 type = nullable.getunderlyingtype(type);               datatable.columns.add(propertydescriptor.name, type);         }         object[] values = new object[propertydescriptorcollection.count];         foreach (t ilistitem in ilist)         {             (int = 0; < values.length; i++)             {                 values[i] = propertydescriptorcollection[i].getvalue(ilistitem);             }             datatable.rows.add(values);         }         return datatable;     }