Question
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
Popular Answer
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();