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
and am trying to call this with Dapper.TVP.
我试图用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);
}
My issue is how do I pass both sets of parameters to the procedure in the call to Dapper Execute()
?
我的问题是如何在调用Dapper Execute()时将两组参数传递给过程?
I have tried:
我努力了:
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);
and
和
connection.Execute("Update_Records", new Object[] { spParams, recordsParam }, commandType: CommandType.StoredProcedure);
Both call the procedure, but pass an empty table parameter ( SELECT COUNT(*) FROM @records
returns 0 )
两者都调用该过程,但传递一个空表参数(SELECT COUNT(*)FROM @records返回0)
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.
我似乎无法找到Dapper.TVP的任何实际文档或来源,所以整个事情非常令人困惑,而.Execute()的第二个参数只是一个动态,所以再次没有告诉我我能做什么无法传递给它。
Any ideas?
有任何想法吗?
2 个解决方案
#1
14
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
);
#2
6
Based on an answer from Mark Gravell here: Does Dapper support SQL 2008 Table-Valued Parameters?
基于Mark Gravell的回答:Dapper是否支持SQL 2008表值参数?
I changed my code to no longer use Dapper.TVP and instead just use a DataTable
so the code is now:
我改变了我的代码,不再使用Dapper.TVP而只是使用DataTable,所以代码现在是:
var recordsTable = new DataTable();
recordsTable.Columns.Add("NewValue", typeof(Decimal));
foreach (var netRevenue in records)
{
var row = recordsTable.NewRow();
row[0] = netRevenue.NewValue;
recordsTable.Rows.Add(row);
}
recordsTable.EndLoadData();
var spParams = new DynamicParameters(new
{
currentYear = filter.currentYear,
country = filter.country,
records = recordsTable.AsTableValuedParameter("Record_Table_Type")
});
using (var connection = ConnectionFactory.GetConnection())
{
connection.Execute("Update_Records", spParams, commandType: CommandType.StoredProcedure);
}
And this works.
这很有效。
#1
14
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
);
#2
6
Based on an answer from Mark Gravell here: Does Dapper support SQL 2008 Table-Valued Parameters?
基于Mark Gravell的回答:Dapper是否支持SQL 2008表值参数?
I changed my code to no longer use Dapper.TVP and instead just use a DataTable
so the code is now:
我改变了我的代码,不再使用Dapper.TVP而只是使用DataTable,所以代码现在是:
var recordsTable = new DataTable();
recordsTable.Columns.Add("NewValue", typeof(Decimal));
foreach (var netRevenue in records)
{
var row = recordsTable.NewRow();
row[0] = netRevenue.NewValue;
recordsTable.Rows.Add(row);
}
recordsTable.EndLoadData();
var spParams = new DynamicParameters(new
{
currentYear = filter.currentYear,
country = filter.country,
records = recordsTable.AsTableValuedParameter("Record_Table_Type")
});
using (var connection = ConnectionFactory.GetConnection())
{
connection.Execute("Update_Records", spParams, commandType: CommandType.StoredProcedure);
}
And this works.
这很有效。