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

Question

I have a stored procedure InsertCars which accepts list of user defined table type CarType.

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

I need call this stored procedure from Dapper. I googled it and found some solutions.

 var param = new DynamicParameters(new{CARID= 66, CARNAME= "Volvo"});

 var result = con.Query("InsertCars", param, commandType: CommandType.StoredProcedure);

But I get an error:

Procedure or function InsertCars has too many arguments specified

Also stored procedure InsertCars returns the count of inserted rows; I need get this value.

Where is the root of problem?

My problem is also that I have cars in generic list List<Car> Cars and I want pass this list to store procedure. It exist elegant way how to do it ?

public class Car
{
    public CarId { get; set; }
    public CarName { get; set; }
}

Thank you for help

EDITED

I found solutions

Does Dapper support SQL 2008 Table-Valued Parameters?

or

Does Dapper support SQL 2008 Table-Valued Parameters 2?

So I 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 get exception

When using the multi-mapping APIs ensure you set the splitOn param if you have keys other than Id.

StackTrace:

   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.<QueryInternal>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

What is wrong?

FIXED:

Call con.Execute instead con.Query

Answer 1

My problem is also that I have cars in generic list List Cars and I want pass this list to stored procedure. It exist elegant way how to do it ?

You need to convert your generic list Car to a datatable and then pass it to storedprocedure. A point to note is that the order of your fields must be same as defined in the user defined table type in database. Otherwise data will not save properly. And it must have same number of columns as well.

I use this method to convert List to DataTable. You can call it like yourList.ToDataTable()

public static DataTable ToDataTable<T>(this List<T> iList)
    {
        DataTable dataTable = new DataTable();
        PropertyDescriptorCollection propertyDescriptorCollection =
            TypeDescriptor.GetProperties(typeof(T));
        for (int i = 0; i < 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)
        {
            for (int i = 0; i < values.Length; i++)
            {
                values[i] = propertyDescriptorCollection[i].GetValue(iListItem);
            }
            dataTable.Rows.Add(values);
        }
        return dataTable;
    }