Dapper Table Valued Parameter OleDB returns Unspecified Error

Question

I have read your answer regarding on the use of TVP in Dapper, I also have implemented your solution but when I execute the code, Dapper returns to me an "Unspecified Error" message. I have reviewed all of the components involved:

1. TVP in SQL Server
2. Datatable Structure in code behind is same as TVP
3. Executing Stored Proc do not have an error

Here is the sample code for Dapper, I hope it help in analyzing my problem;

return _oledbconn.Query<int>(@"exec tvpdapper_sample ?", new { 
otstatus = _dtTVP.AsTableValuedParameter() 
}).SingleOrDefault();

Answer 1

You don't indicate what the backend is here. If the backend is SQL Server, frankly: use SqlConnection. If the backend is something else, it may or may not even work. ADO.NET providers in general are not required or expected to support table-valued-parameters.

Note that at the moment your code isn't calling a stored procedure. At least, not directly. Rather: it is running a text command that calls a stored procedure. This is important because the custom data type is implicit for stored procedures, but must be explicit for text. And with OLEDB: there's no way to make it explicit! So you might find that this works, if your backend and provider both support TVPs:

return _oledbconn.Query<int>(@"tvpdapper_sample", new { 
    otstatus = _dtTVP.AsTableValuedParameter() 
}, commandType: CommandType.StoredProcedure).SingleOrDefault();