几乎没有什么魔力IDynamicParameters
您需要担心的只是实施AddParameters
就准备运行打开IDbCommand。
假设您想要一个整数元组,您可以实现以下内容:
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
其次是:
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;
}
}
然后你可以传入元组:
var nums = connection.Query("get_int_tuples",
new TupleIntDynamicParam (new Tuple<int,int>[]
{
Tuple.Create(1,2), Tuple.Create(2,3)
})).ToList();