Dynamic Parameters with Table Valued Parameters

[mrinalkamboj]

I have posted the same question @

http://stackoverflow.com/questions/33087629/dapper-dynamic-parameters-with-table-valued-parameters

Didn't got a response, therefore tried and posted my own work around as a response. Still looking for an out of the box solution. Issue is fairly simple, if I have a Output Parameter, Return Value parameter along with a Input parameter which is Table type, now we need Dynamic parameters for adding parameters to execute query, but it seems not feasible to add Dynamic Parameters which is Table Valued. It leads to an error:

System.Data.SqlClient.SqlException: The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect.

Any suggestion to work around it?

[NickCraver]

Can you paste the exact code? From the SO question it doesn't look like you are using the .AsTableValuedParameter() extension method that handles (I think) exactly what you're trying to do. For example:

var dynamicParameters = new DynamicParameters();
dynamicParameters.Add("PersonList",  myDatatable.AsTableValuedParameter());

[mrinalkamboj]

Tested using extension method "AsTableValuedParameter()" and it works as expected, though I have following questions:

• When I am not using Dynamic Parameters all Table Values Parameters just need a DataTable to work, explicit extension method needn't be called, then why do we have a exception in this case for an Input parameter. My understanding on reviewing some examples was that "AsTableValuedParameter()" is a deprecated implementation, which may not be any more required
• I have reviewed the "AsTableValuedParameter()" , it seems to be coded purely for supporting "System.Data.SqlClient", will not work in case of any other database like Oracle, apparently the custom solution I have posted on Stack Overflow is much more generic, as we use an abstract class, which fills up with the type compatible derived class at run time and do the a similar job. Shall I plan a similar modification in this case, so that its not just SQL Server specific

[NickCraver]

On the first item, DataTable isn't in CoreCLR - so there's a lot of pain on that particular point at the moment. We're unsure if it'll make a return, but there's no lack of discussion around it. On the second - I have no strong feelings around TVPs...mostly from the fact we just don't use them for performance reasons.

We also haven't had a very robust test suite outside SQL server. Which we can do now and I'll try and flesh out as time allows - this would let us more heavily test any more compatible version. @mgravell any thoughts on more generified version here?

[yehudamakarov]

@NickCraver Thanks for the above explanation.

In regards to:

I have no strong feelings around TVPs...mostly from the fact we just don't use them for performance reasons.

What do people try to use instead to fill this need?