Dapper and Passing Table Value Parameter(TVP)

Dapper and TVP (Table Value Parameter)

• Introduction

In one sentence, dapper is a Micro ORM. I find it very helpful if my application uses stored procedures  to connect to the database and do various operations than  using Entity Framework for the database operations. The beauty is, they could coexist in the same application and Dapper can facilitate database operations for stored procedures and raw SQL while Entity Framework can do database operations through POCO or Models. So they together satisfy all your needs in terms of database operations from your applications. Dapper has a bunch of extension classes which extends the .NET database facilitation classes.

Dapper is very simple and in fact, it has only two methods related to database activity. Execute and Query. As the name says, do execute when you need to do some modifications in the database and do the Query when you want to fetch some records from the database. When you fetch using the Query method, you could specify which type of object you are expecting by adding the type in the Generic <T> place holder. 

• TVP (Table Value Parameter)

We might need to pass a table value parameter in some cases to the stored procedure using dapper. One of the typical situation is when you want to have a list of objects persisted in database through stored procedure. To pass the TVP through dapper to a stored procedure, you need to install an extension for dapper which is called Dapper.TVP. You could easily download and install that using your Nuget package manager. Once you install it, implement the code like the sample code I have given below.

using Dapper;

using Dapper.tvp;

conn.Execute("<Procedure Name>", new {TVP = datatable.AsTableValuedParameter("<SQL Type>")}, commandType: CommandType.StoredProcedure);