.net Dapper 是否支持 SQL 2008 表值参数？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6232978/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Does Dapper support SQL 2008 Table-Valued Parameters?
提问by Carlos Mendes
Does anyone know if is possible to pass table-valued parameterdata to a stored procedure with Dapper?
有谁知道是否可以使用 Dapper将表值参数数据传递给存储过程？
回答by Marc Gravell
There is now (n Dapper 1.26 and higher) direct support for table-valued parameters baked into dapper. In the case of stored procedures, since the data type is built into the sproc API, all you need to do is supply a DataTable:
现在（n Dapper 1.26 及更高版本）直接支持嵌入到 dapper 中的表值参数。对于存储过程，由于数据类型内置在 sproc API 中，您需要做的就是提供一个DataTable：
var data = connection.Query<SomeType>(..., new {
id=123, name="abc", values = someTable
}, ...);
For direct command-text you have two other options:
对于直接命令文本，您还有另外两个选项：
use a helper method to tell it the custom data type:
var data = connection.Query<SomeType>(..., new { id=123, name="abc", values = someTable.AsTableValuedParameter("mytype") }, ...);tell the data-table itself what custom data type to use:
someTable.SetTypeName("mytype"); var data = connection.Query<SomeType>(..., new { id=123, name="abc", values = someTable }, ...);
使用辅助方法告诉它自定义数据类型：
var data = connection.Query<SomeType>(..., new { id=123, name="abc", values = someTable.AsTableValuedParameter("mytype") }, ...);告诉数据表本身使用什么自定义数据类型：
someTable.SetTypeName("mytype"); var data = connection.Query<SomeType>(..., new { id=123, name="abc", values = someTable }, ...);
Any of these should work fine.
其中任何一个都应该可以正常工作。
回答by Sam Saffron
Yes, we support them but you will need to code your own helpers.
是的，我们支持它们，但您需要编写自己的助手代码。
For example:
例如：
class IntDynamicParam : Dapper.SqlMapper.IDynamicParameters
{
IEnumerable<int> numbers;
public IntDynamicParam(IEnumerable<int> numbers)
{
this.numbers = numbers;
}
public void AddParameters(IDbCommand command)
{
var sqlCommand = (SqlCommand)command;
sqlCommand.CommandType = CommandType.StoredProcedure;
List<Microsoft.SqlServer.Server.SqlDataRecord> number_list = new List<Microsoft.SqlServer.Server.SqlDataRecord>();
// Create an SqlMetaData object that describes our table type.
Microsoft.SqlServer.Server.SqlMetaData[] tvp_definition = { new Microsoft.SqlServer.Server.SqlMetaData("n", SqlDbType.Int) };
foreach (int n in numbers)
{
// Create a new record, using the metadata array above.
Microsoft.SqlServer.Server.SqlDataRecord rec = new Microsoft.SqlServer.Server.SqlDataRecord(tvp_definition);
rec.SetInt32(0, n); // Set the value.
number_list.Add(rec); // Add it to the list.
}
// Add the table parameter.
var p = sqlCommand.Parameters.Add("@ints", SqlDbType.Structured);
p.Direction = ParameterDirection.Input;
p.TypeName = "int_list_type";
p.Value = number_list;
}
}
// SQL Server specific test to demonstrate TVP
public void TestTVP()
{
try
{
connection.Execute("CREATE TYPE int_list_type AS TABLE (n int NOT NULL PRIMARY KEY)");
connection.Execute("CREATE PROC get_ints @ints int_list_type READONLY AS select * from @ints");
var nums = connection.Query<int>("get_ints", new IntDynamicParam(new int[] { 1, 2, 3 })).ToList();
nums[0].IsEqualTo(1);
nums[1].IsEqualTo(2);
nums[2].IsEqualTo(3);
nums.Count.IsEqualTo(3);
connection.Execute("DROP PROC get_ints");
connection.Execute("DROP TYPE int_list_type");
}
}
Make sure you properly test performance for table valued params. When I tested this for passing int lists it was significantly slower than passing in multiple params.
确保正确测试表值参数的性能。当我测试这个传递 int 列表时，它比传递多个参数要慢得多。
I am totally not against having some SQL Server specific helpers for dapper in the contrib project, however the core dapper avoids adding vendor specific tricks where possible.
我完全不反对在 contrib 项目中为 dapper 提供一些 SQL Server 特定的帮助程序，但是核心 dapper 避免在可能的情况下添加供应商特定的技巧。
回答by Darek
I know this ticket is OLD, very old, but wanted to let you know that I have published Dapper.Microsoft.Sql package, which supports generic TVPs.
我知道这张票很旧，很旧，但想让你知道我已经发布了支持通用 TVP 的 Dapper.Microsoft.Sql 包。
https://www.nuget.org/packages/Dapper.Microsoft.Sql/
https://www.nuget.org/packages/Dapper.Microsoft.Sql/
Sample use:
样品用途：
List<char> nums = this.connection.Query<char>(
"get_ints",
new TableValuedParameter<char>(
"@ints", "int_list_Type", new[] { 'A', 'B', 'C' })).ToList();
It is based on the original classes from Dapper test project.
它基于 Dapper 测试项目的原始类。
Enjoy!
享受！
回答by Marc Gravell
todayit isn't. We actually investigated table-valed-parameters for our cheeky "in" implementation (where col in @values), but were very unimpressed by performance. However in the context of a SPROC it makes sense.
今天不是。我们实际上为我们厚颜无耻的“in”实现 ( where col in @values)研究了表值参数，但对性能非常不满意。然而，在 SPROC 的上下文中，这是有道理的。
Your best bet is to log this as an issue on the project siteso we can track/prioritise it. It sounds like something will be doable, though, probably similar to the DbString or DynamicParameters options.
最好的办法是将此作为问题记录在项目站点上，以便我们可以对其进行跟踪/优先处理。不过，听起来有些事情是可行的，可能类似于 DbString 或 DynamicParameters 选项。
But today? No.
但今天？不。
