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
5条答案
按热度按时间gt0wga4j1#
First create a User Defined Table type in Database
In your code
jv2fixgn2#
As I can understand that this requirement is not supported out of the box and I may need to code the specific helper. I have resolved it using a custom base abstract class
TypeMap
, which can be extended by all kinds of providers, to implement the API, which are not out of he box possible using the Dapper, I am pasting my implementation related to SQL-Server, similar can be done for other ADO.Net compliant providers:Supporting classes and API, to make the above code work:
Common APIs
brccelvz3#
Inside the add function of parameter object pass argument DbType.Object
eg: DynamicParameters parameterObject = new DynamicParameters(); parameterObject.Add("@table", dtTable, DbType.Object);
qc6wkl3g4#
I found Rohit Shetty's answer helpful, but still had trouble as it did not provide a complete example. Here is an example with more sample code.
In SQL, define your User Defined Table Type:
Then in C# you would do something like this:
You can optionally provide the name of the User Defined Table Type (e.g.
[dbo].[IntListTableType]
) in theAsTableValuedParameter
method, but I found it worked fine without it in my scenario, so I'm not certain when it would be required.ejk8hzay5#