Using NTS with Dapper

ConsoleApp.csproj

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Dapper" Version="2.0.4" />
    <PackageReference Include="NetTopologySuite.IO.SqlServerBytes" Version="2.0.0" />
    <PackageReference Include="System.Data.SqlClient" Version="4.7.0-preview9.19421.4" />
  </ItemGroup>

</Project>

Program.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Threading.Tasks;
using Dapper;
using NetTopologySuite.Geometries;
using NetTopologySuite.IO;

class City
{
    public int CityID { get; set; }
    public string CityName { get; set; }
    public Point Location { get; set; }
}

class GeometryHandler<T> : SqlMapper.TypeHandler<T>
    where T : Geometry
{
    readonly bool _geography;
    readonly SqlServerBytesWriter _writer;
    readonly SqlServerBytesReader _reader;

    public GeometryHandler(bool geography = false)
    {
        _geography = geography;
        _writer = new SqlServerBytesWriter { IsGeography = geography };
        _reader = new SqlServerBytesReader { IsGeography = geography };
    }

    public override T Parse(object value)
        => (T)_reader.Read((byte[])value);

    public override void SetValue(IDbDataParameter parameter, T value)
    {
        parameter.Value = _writer.Write(value);

        ((SqlParameter)parameter).SqlDbType = SqlDbType.Udt;
        ((SqlParameter)parameter).UdtTypeName = _geography ? "geography" : "geometry";
    }
}

class Program
{
    static async Task Main()
    {
        SqlMapper.AddTypeHandler(new GeometryHandler<Point>(geography: true));

        var connection = new SqlConnection(
            @"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=WideWorldImporters");

        await connection.OpenAsync();

        var nearestCity = await connection.QueryFirstAsync<City>(
            @"
                SELECT CityID, CityName, Location.Serialize() AS Location
                FROM Application.Cities
                ORDER BY Location.STDistance(@CurrentLocation)
            ",
            new
            {
                CurrentLocation = new Point(-122.128822, 47.643703) { SRID = 4326 }
            });

        Console.WriteLine($"{nearestCity.CityName} ({nearestCity.Location.Y}, {nearestCity.Location.X})");
    }
}

Hi, I'm using your code and it works perfectly. but only when I write in the database, not when I try to read. maybe it's the new version of the components?

System.Data.DataException: 'Error parsing column 14 (Location = 2 - Byte)'

FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.Types, Version = 10.0.0.0, Culture = neutral, PublicKeyToken = 89845dcd8080cc91'. The system cannot find the file specified.

note: the interesting thing is that it tries to parse the value of a previous column and not the correct column.

@drma-dev Try using Microsoft.Data.SqlClient instead. I’m not sure if the UDT work ever made it into System.Data.SqlClient.

Ah, also note the call to .Serialize() in the T-SQL. This passes it to the client as a byte[]. This is required because Dapper only ever calls DbDataReader.GetValue() and doesn’t let you call GetBytes() to avoid trying to deserialize the SqlGeography instance on the client.

In fact, I was already using it:

Microsoft.Data.SqlClient
Microsoft.EntityFrameworkCore.SqlServer.NetTopologySuite

I tried to read only that column and gave this: Error parsing column 0 (location = < null >)

it may be a problem really here: (T) _reader.Read ((byte []) value)

but I'm not able to debug this handler

SqlMapper.AddTypeHandler(new GeometryHandler(geography: true));

using that line of code or not, the error remains. as if it doesn't influence anything

when i install everything that is required the following error appears:

InvalidCastException: Unable to cast object of type 'Microsoft.SqlServer.Types.SqlGeography' to type 'Microsoft.Data.SqlClient.Server.IBinarySerialize'.

does that mean anything to you?

I also tested it with this code (to make sure it wasn't a problem with the database) and gave the same error:

return await repRead.GetCustom("SELECT @p as location", new { p = new Point(47, 52) { SRID = 4326 } });

You need to use SELECT @p.Serialize() ...

it works. thanks

tnx man you are best

Works great, thanks! But did notice a slight performance hit having to call Serialize() - still much better than calling ToString() as we had before and working with the WKT! 👍

You saved my nerves =) Thank you!

Thanks @bricelam, @AndyMeps! This really made my day. I've been banging my head against this problem for a day. I had tried all sorts of variations and solutions - it would have never occurred to me to fiddle with the SQL statement and use Serialize()