c# - 从 dapper 调用存储过程，它接受用户定义的表类型列表

Question

我有一个存储过程 InsertCars它接受用户定义的表类型列表 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

我需要从 Dapper 调用这个存储过程。我用谷歌搜索并找到了一些解决方案。

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

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

但是我得到一个错误:

Procedure or function InsertCars has too many arguments specified

还有存储过程 InsertCars返回插入行的计数；我需要得到这个值。

问题的根源在哪里？

我的问题还在于我在通用列表中有汽车 List<Car> Cars我想将此列表传递给存储过程。它存在优雅的方式如何做到这一点？

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

谢谢你的帮助

已编辑

我找到了解决方案

Does Dapper support SQL 2008 Table-Valued Parameters?

或

Does Dapper support SQL 2008 Table-Valued Parameters 2?

所以我尝试制作自己的愚蠢助手类

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;
    }
}

使用

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

我得到异常

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

堆栈跟踪:

   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

怎么了？

已修复:

调用con.Execute相反 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 ?

您需要将通用列表 Car 转换为数据表，然后将其传递给存储过程。需要注意的一点是，您的字段顺序必须与数据库中用户定义的表类型中定义的顺序相同。否则数据将无法正确保存。而且它也必须有相同数量的列。

我使用此方法将 List 转换为 DataTable。你可以像 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;
    }