Dapper .NET: SQL Server Specific Features
Table-Valued parameters, Spatial and HiearchyID data types are all supported
15 Jan 2018
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.
Table-Valued Parameters
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:
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
Bad news first. The SqlGeometry and SqlGeography data types are not yet supported by .NET Core. So, even via Dapper, you can use them only in the “legacy” .NET version, also known as .NET Framework.
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 Microsoft.SqlServer.Types package.
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 Microsoft.SqlServer.Types
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 Microsoft.SqlServer.Types version 10.0.0.0 anyway, even if you have installed the latest version, the 14.0.0.0, on your machine. The latest version can be obtained via SQL Server Feature Pack:
Microsoft® SQL Server® 2016 Feature Pack
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:
Samples
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.
Conclusion
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.
What’s Next
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.