小巧玲珑是否支持SQL 2008表值参数2? [英] Does Dapper support SQL 2008 Table-Valued Parameters 2?
问题描述
我知道,短小精悍可以支持TVF,但你怎么用TVF发送额外的参数一起(不将其添加到IntDynamicParam类)?请参阅从Tests.cs下面的例子中,我已经修改,添加额外的参数:
connection.Execute(CREATE TYPE int_list_type如表(正诠释NOT NULL PRIMARY KEY));
connection.Execute(CREATE PROC get_ints @x INT,@ints int_list_type READONLY AS SELECT * FROM @ints);
我尝试以下,但得到的错误(从对象类型SqlMapper.Tests + IntDynamicParam到一个不存在任何映射已知的托管提供原生型):
VAR p =新DynamicParameters();
p.Add(×,4);
p.Add(整数,新IntDynamicParam(新INT [] {1,2,3}));
VAR NUMS = connection.Query< INT>(get_ints,P).ToList();
感谢您的答复山姆,但问题有一点不同的。我想知道如何在另一个变量传递的元组一起。请参见下面的修改SP:
CREATE TYPE int_tuple_list_type如表(正诠释NOT NULL PRIMARY KEY,N2 INT)
CREATE PROC get_int_tuples
@someVar VARCHAR(10),
@ints int_tuple_list_type READONLY
AS SELECT * FROM @ints
有大约 IDynamicParameters
所有你需要担心对正在实施 AddParameters
在准备运行开放的IDbCommand。
假设你的整数的元组,你可以实现以下内容:
CREATE TYPE int_tuple_list_type
如表(正诠释NOT NULL PRIMARY KEY,N2 INT)
创建PROC get_int_tuples @ints
int_tuple_list_type READONLY AS SELECT * FROM @ints
跟:
类TupleIntDynamicParam:Dapper.SqlMapper.IDynamicParameters
{
IEnumerable的< INT>元组;
公共IntDynamicParam(IEnumerable的<元组LT; INT,INT>>的元组)
{
this.tuples =元组;
}
公共无效AddParameters(IDbCommand的命令)
{
VAR的SqlCommand =(的SqlCommand)命令;
sqlCommand.CommandType = CommandType.StoredProcedure;
名单,LT; Microsoft.SqlServer.Server.SqlDataRecord> number_list =
新的List< Microsoft.SqlServer.Server.SqlDataRecord>();
//创建描述我们的表型的SqlMetaData对象。
Microsoft.SqlServer.Server.SqlMetaData [] tvp_definition = {
新Microsoft.SqlServer.Server.SqlMetaData(n的,SqlDbType.Int)
新Microsoft.SqlServer.Server.SqlMetaData (N2,SqlDbType.Int)};
的foreach(INT n的元组)
{
//创建一个新的记录,使用上面的元数据阵列。
Microsoft.SqlServer.Server.SqlDataRecord REC =
新Microsoft.SqlServer.Server.SqlDataRecord(tvp_definition);
rec.SetInt32(0,n.Item1);
rec.SetInt32(1,n.Item2);
number_list.Add(REC); //将它添加到列表中。
}
//添加表参数。
变种P = sqlCommand.Parameters.Add(整型,SqlDbType.Structured);
p.Direction = ParameterDirection.Input;
p.TypeName =int_tuple_list_type;
p.Value = number_list;
}
}
然后,你可以通过在元组
VAR NUMS = connection.Query(get_int_tuples,
新TupleIntDynamicParam(新的记录< INT,INT> []
{
Tuple.Create(1,2),Tuple.Create(2,3)
}))了ToList();
I know that dapper can support TVF, but how do you send extra parameters along with TVF (without adding it to the IntDynamicParam class)? See the below example from Tests.cs, i have modified to add the extra parameter:
connection.Execute("CREATE TYPE int_list_type AS TABLE (n int NOT NULL PRIMARY KEY)");
connection.Execute("CREATE PROC get_ints @x int, @ints int_list_type READONLY AS select * from @ints");
I tried the following but got errors (No mapping exists from object type SqlMapper.Tests+IntDynamicParam to a known managed provider native type.):
var p = new DynamicParameters();
p.Add("x", 4);
p.Add("ints",new IntDynamicParam(new int[] { 1, 2, 3 }));
var nums = connection.Query<int>("get_ints", p).ToList();
Thank you for the reply Sam, but the question was a little different. I want to know how to pass in another variable along with the tuple. See the modified SP below:
CREATE TYPE int_tuple_list_type AS TABLE (n int NOT NULL PRIMARY KEY, n2 int)
CREATE PROC get_int_tuples
@someVar varchar(10),
@ints int_tuple_list_type READONLY
AS select * from @ints
There is very little magic about IDynamicParameters
all you need to worry about is implementing AddParameters
on the ready to run open IDbCommand.
Say you wanted a tuple of ints, you could implement the following:
CREATE TYPE int_tuple_list_type
AS TABLE (n int NOT NULL PRIMARY KEY, n2 int)
CREATE PROC get_int_tuples @ints
int_tuple_list_type READONLY AS select * from @ints
Followed by:
class TupleIntDynamicParam : Dapper.SqlMapper.IDynamicParameters
{
IEnumerable<int> tuples;
public IntDynamicParam(IEnumerable<Tuple<int,int>> tuples)
{
this.tuples= tuples;
}
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),
new Microsoft.SqlServer.Server.SqlMetaData("n2", SqlDbType.Int) };
foreach (int n in tuples)
{
// 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.Item1);
rec.SetInt32(1, n.Item2);
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_tuple_list_type";
p.Value = number_list;
}
}
Then you can pass in tuples with:
var nums = connection.Query("get_int_tuples",
new TupleIntDynamicParam (new Tuple<int,int>[]
{
Tuple.Create(1,2), Tuple.Create(2,3)
})).ToList();
这篇关于小巧玲珑是否支持SQL 2008表值参数2?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!