SQL Server Specific Features

Table-Valued parameters, Spatial and HiearchyID data types are all supported

Davide Mauri
Dapper .NET
5 min readJan 15, 2018

--

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, and, as of December 2019, with Dapper 2.0, also in .NET Core. If for any reason you need to use an old version of Dapper, read on. Otherwise you may want to go to the next section.

December 2019 Update: Dapper 2.0 now works perfectly also with TVP and .NET Core. No workaround needed. Thanks Devs! :)

With Dapper before version 2.0, 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. December 2019 Update: But wait! With the latest version of .NET Core, this has changed and you can use the .NET Framework types along with .NET Core. You’ll get a warning when compiling, but everything will work well.

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:

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 older Dapper version is not there, but it was easy to extend Dapper to support it. And now with Dapper 2.0 the problem as been fixed at the root. Just great!

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.

--

--

Davide Mauri
Dapper .NET

Data Geek, Storyteller, Developer at heart, now infiltrated in Azure SQL product group to make sure developers voice is heard loud and clear. Heavy Metal fan.