SQL Server Specific Features
Table-Valued parameters, Spatial and HiearchyID data types are all supported
If you are a SQL Server or Azure SQL user, you’ll be happy to learn that Dapper support some very specific (and very nice) SQL Server Features:
- Table-Valued Parameters
- Spatial Data Types
- HiearchyID Data Type
support to these features is really simple and straightforward as you’ll see.
TVP are surely one of the nicest feature of SQL Server that aims to solve the problem of passing an entire table as input parameter for a Stored Procedure or a Function. They offer great performance and flexibility. If you’re not using them, take a look here:
Table-valued parameters provide an easy way to marshal multiple rows of data from a client application to SQL Server…
Using them with Dapper is embarrassingly simple. Once you have created an
IEnumerable or a
DataTable that is compatible with the defined TVP you can use the
AsTableValuedParameter extension to, guess what?, pass it to your Stored Procedure or Function that expect a TVP as input.
So, say you have defined the following TVP on SQL Server:
in your .NET application all you have to do is to create a DataTable that is compatible with the table type schema and fill it with data:
and once this is done you can just pass it as a parameter using the
AsTableValuedParameter extension method to inform Dapper that such DataTable must be mapped to the TVP:
This just work smoothly in .NET Framework. In .NET Core, the mentioned extension method has not been added yet, as of Dapper version 1.50.4…so the way to solve the problem is to extend Dapper and create a custom query parameter. I’ve learned this technique by studying Dapper code itself. No guarantee it will work in future, but it works now, and that’s enough. I’m sure that in future version Dapper will support the nice extension method also in .NET Core natively.
Here’s a sample code for the custom parameter:
Once this class is in place, the query invocation will just need a small change:
Done, the TVP is working as expected also in .NET Core, as the SQL Server Profiler confirms:
Spatial Data Types
Beside this, the good news is that Dapper is completely transparent to the fact that this is not a native type (which is exactly what we could expect from a micro ORM). After having added the package
Microsoft.SqlServer.Types via NuGet, all is needed to do is to use the Spatial Data Types:
HiearchyID Data Type
Same as before,
HierachyID is supported only in .NET Framework and not in .NET Core, and it is provided by the
Same as before, all you need to do is to add the NuGet package and you’re good to go. Using it is straightforward, since nothing special needs to be done in order to use it as a parameter or as the result of a query:
A note on
Reality is that you may have some troubles having the types provided by
Microsoft.SqlServer.Types to work correctly, mainly because .NET will try to load the
10.0.0.0 anyway, even if you have installed the latest version, the
22.214.171.124, on your machine. The latest version can be obtained via SQL Server Feature Pack:
Microsoft® SQL Server® 2016 Feature Pack
The Microsoft SQL Server 2016 Feature Pack is a collection of stand-alone packages which provide additional value for…
and then download the “SQLSysClrTypes.msi” file:
Once this has been installed, you just have to make sure your application correctly look for it, using the Assembly Binding feature. This means that you have to put the following code in your app.config file:
As usual samples are available on GitHub:
I’ve changed the samples to support both .NET Core 2.0 and .NET Framework 4.5.2, so that you can also play with the features not yet supported by .NET Core 2.0. Look in the readme to see how to execute the samples against one or another framework.
Support to native SQL Server feature is native and works just right out of the box. TVP support is a great thing, since performance wise TVP can offer great benefits. In the .NET Core bits of Dapper is not yet there, but it was easy to extend Dapper to support it.
Extending Dapper is the topic of the next articles: how to customize Dapper behavior to make sure it can handle all possible scenarios, even the most exotic ones.