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?

1
8
3/18/2019 3:30:31 AM

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);
4
2/15/2019 4:33:55 PM


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow