So you want to pass a list of something to a SQL stored procedure/Query using Dapper? And you want to receive it as a Table Valued Parameter? Sounds great, you might feel a little bummed that this isn’t supported out of the box in Dapper, meaning that you can’t simply put a IEnumerable as a param in the dynamic param object that a Dapper query accepts. But fear not, it’s pretty easy to do yourself, depending on how thorough you want to be.
I’m simply going to create a one-off solution that you can easily expand on yourself.
I will not pretend to be an expert on the inner workings of Dapper, but I do know that the
Query<t> method takes a parameter called param that is of type dynamic, meaning that we can put an inline object initilazier here, or an object of any type. The magic that we’re after happens if we create a new type that implements Dappers
But first things first, let us assume that you have a stored procedure that takes a TVP parameter of ids for filtering on. It might look like this:
You might try to call it like this, using the basic Dapper functionality that you’re used to:
But this will of course not work, and that’s why you’re reading this. The solution isn’t that hard but finding Dapper examples on the internet can be quite time consuming/frustrating. So lets write some code:
But as I said, this is a one-off implementation with no dynamic input. If you’re okay with having maybe one or two implementations of this style in your code then that’s okay, simply add parameters to the constructor as needed and add them to the command in the
AddParameters method. Or you could extend it to take a
Dynamic object in the constructor and iterate over its properties, creating parameters on the fly. I would consider the second option if you use TVP a lot in your stored procedures and that most of your other params are of a few normal types such as INT and NVARCHAR. This could easibly be a project in itself if you want to catch every single use case in a single implementation.