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

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

我有一个存储过程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

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

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

 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

过程或函数InsertCars指定了太多参数

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

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

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 ?

我的问题是我在通用列表List Cars中有汽车，我希望将此列表传递给存储过程。它优雅的方式存在怎么办？

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

Thank you for help

谢谢你的帮助

EDITED

EDITED

I found solutions

我找到了解决方案

Does Dapper support SQL 2008 Table-Valued Parameters?

Dapper是否支持SQL 2008表值参数？

要么

Does Dapper support SQL 2008 Table-Valued Parameters 2?

Dapper是否支持SQL 2008表值参数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.

使用多映射API时，如果您具有Id以外的密钥，请确保设置splitOn参数。

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

调用con.Execute而不是con.Query

3 个解决方案

#1

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

我的问题是我在通用列表List Cars中有汽车，我希望将此列表传递给存储过程。它优雅的方式存在怎么办？

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.

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

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

我使用此方法将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;
    }

#2

I know this is a little old, but I thought I would post on this anyway since I sought out to make this a little easier. I hope I have done so with a NuGet package I create that will allow for code like:

我知道这有点旧了，但我想我会发布这个，因为我试图让这更容易一点。我希望我已经使用我创建的NuGet包完成了这样的代码，例如：

public class CarType
{
  public int CARID { get; set; }
  public string CARNAME{ get; set; }
}

var cars = new List<CarType>{new CarType { CARID = 1, CARNAME = "Volvo"}};

var parameters = new DynamicParameters();
parameters.AddTable("@Cars", "CarType", cars)

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

NuGet package: https://www.nuget.org/packages/Dapper.ParameterExtensions/0.2.0 Still in its early stages so may not work with everything!

NuGet包：https：//www.nuget.org/packages/Dapper.ParameterExtensions/0.2.0仍处于早期阶段，因此可能无法解决所有问题！

Please read the README and feel free to contribute on GitHub: https://github.com/RasicN/Dapper-Parameters

请阅读自述文件并随时在GitHub上做出贡献：https：//github.com/RasicN/Dapper-Parameters

#3

The other solution would be to call it like this

另一种解决方案就是这样称呼它

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

var result = con.Query<dynamic>("InsertCars", param);

Remove : new CarDynamicParam(car), commandType: CommandType.StoredProcedure

删除：新的CarDynamicParam（car），commandType：CommandType.StoredProcedure

Use the parameter of table type directly, it will work.

直接使用表类型的参数，它将起作用。

If you can use Datatable(.net core does not support it), then its very easy.

如果你可以使用Datatable（.net核心不支持它），那么它非常容易。

Create DataTable -> Add required columns to match with your table type -> Add required rows. Finally just call it using dapper like this.

创建DataTable - >添加所需的列以匹配您的表类型 - >添加所需的行。最后用这样的小巧玲子来调用它。

var result = con.Query<dynamic>("InsertCars", new{paramFromStoredProcedure=yourDataTableInstance}, commandType: CommandType.StoredProcedure);

#1