How to pass Table Valued Parameters with Dapper Dynamic Parameters?
c# dapper datatable sql-server stored-procedures
Question
I was trying to create a generic method, which can read the parameters name and value from a class at Runtime and create parameter collection for Dapper query execution. Realized that till the point all parameters are Input type it works well, but if I have to add anOutput / ReturnValue
type parameters, thus I must deal with them.DynamicParameters
otherwise I am unable to get the value ofOutput / ReturnValue
parameters
These are the criteria for SP:
PersonList - TableValued - Input
TestOutput - Int - Output
The following line of code does not work for me:
var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("PersonList", <DataTable PersonList>);
dynamicParameters.Add("TestOutput", 0, Dbtype.Int32, ParameterDirection.Output);
Except for:
System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@PersonList"): Data type 0x62 (sql_variant) has an invalid type for type- specific metadata.
As far as I can see, the problem is that because I am not utilizing the sqldbtype, there is no valid DbType available for adding a TVP to the dynamic parameters. in the DbType format.
Any advice or workarounds to fix the problem?
Popular Answer
Make a User Defined Table Type First in the Database.
CREATE TYPE udtt_PersonList AS TABLE
(
...
)
GO
in the code
var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("@PersonList", PersonList.AsTableValuedParameter("[dbo].[udtt_PersonList]"));
dynamicParameters.Add("TestOutput", 0, Dbtype.Int32, ParameterDirection.Output);