How to pass Table Valued Parameters with Dapper Dynamic Parameters?

Question

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 an Output / ReturnValue type parameters, then I need to work with DynamicParameters, else I cannot fetch the value of Output / ReturnValue parameters

SP has following parameters:

PersonList - TableValued - Input
TestOutput - Int - Output

I am not able to make following piece of code work:

var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("PersonList", <DataTable PersonList>);
dynamicParameters.Add("TestOutput", 0, Dbtype.Int32, ParameterDirection.Output);

Exception is:

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.

Issue as I can understand is there's no valid DbType available for adding a TVP to the Dynamic Parameters, since I am not using the SqlDbType, so there's no replacement for SqlDbType.Structured in the DbType.

Any pointer or workaround to resolve the issue

Mrinal Kamboj

View more on Stack Overflow

Rohit Shetty · Accepted Answer · 2/15/2019 4:33:55 PM

First create a User Defined Table type in Database

CREATE TYPE udtt_PersonList AS TABLE 
(
    ...
)
GO

In your code

var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("@PersonList", PersonList.AsTableValuedParameter("[dbo].[udtt_PersonList]"));
dynamicParameters.Add("TestOutput", 0, Dbtype.Int32, ParameterDirection.Output);

How to pass Table Valued Parameters with Dapper Dynamic Parameters?

Question

Fastest Dapper Plus Extensions

Popular Answer

Performance

Expression Evaluator