结合使用Dapper.TVP TableValueParameter和其他参数

问题描述

我有一个使用表值参数以及其他参数的过程：

I have a procedure that takes in a table-valued parameter, along with others:

CREATE PROCEDURE [dbo].[Update_Records]
    @currentYear INT,
    @country INT,
    @records Record_Table_Type READONLY
AS

，并尝试使用Dapper.TVP调用它。

and am trying to call this with Dapper.TVP.

这是我到目前为止的代码：

Here is the code I have so far:

        var recordsParameter = new List<SqlDataRecord>();

        // This metadata matches 'Record_Table_Type' in the Database
        var recordsMetaData = new[]
        {
            new SqlMetaData("OriginalValue", SqlDbType.Decimal, 19, 4),
            new SqlMetaData("NewValue", SqlDbType.Decimal, 19, 4),
            new SqlMetaData("NewPercent", SqlDbType.Decimal, 7, 2),
        };

        foreach (var r in records)
        {
            var record = new SqlDataRecord(recordsMetaData);
            record.SetDecimal(0, r.OriginalValue);
            record.SetDecimal(1, r.NewValue);
            record.SetDecimal(2, r.NewPercent);
            recordsParameter.Add(record);
        }

        var spParams = new DynamicParameters(new
        {
            currentYear = filter.currentYear,
            country = filter.country,
        });

        var recordsParam = new TableValueParameter("@records", "Record_Table_Type", recordsParameter);

        using (var connection = ConnectionFactory.GetConnection())
        {
            connection.Execute("Update_Records", ???, commandType: CommandType.StoredProcedure);
        }

我的问题是如何将两组参数都传递给致电Dapper Execute（）？

My issue is how do I pass both sets of parameters to the procedure in the call to Dapper Execute()?

我尝试过：

var spParams = new DynamicParameters(new
{
    currentYear = filter.currentYear,
    country = filter.country,
    records = new TableValueParameter("@records", "Record_Table_Type", recordsParameter);
});

connection.Execute("Update_Records", spParams, commandType: CommandType.StoredProcedure);

和

connection.Execute("Update_Records", new Object[] { spParams, recordsParam }, commandType: CommandType.StoredProcedure);

都调用该过程，但传递一个空表参数（ SELECT COUNT（ *）FROM @records 返回0）

Both call the procedure, but pass an empty table parameter ( SELECT COUNT(*) FROM @records returns 0 )

我似乎找不到Dapper.TVP的任何实际文档或来源，因此整个事情非常混乱， .Execute（）的第二个参数只是一个 dynamic ，所以再次告诉我我可以和不能通过的东西。

I can't seem to find any actual documentation or source for Dapper.TVP, so the whole thing is very confusing, and the 2nd parameter to .Execute() is just a dynamic so that again doesn't tell me what I can and can't pass to it.

有什么想法吗？

推荐答案

我在移动设备上，可能会误解这个问题，但这应该仅仅是：

I am on mobile and may be misunderstanding the question, but this should be just:

DataTable records = ...
connection.Execute("Update_Records",
    new {
        currentYear = filter.currentYear,
        country = filter.country,
        records
    },
    commandType: CommandType.StoredProcedure
);

这篇关于结合使用Dapper.TVP TableValueParameter和其他参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！