-
-
Save bricelam/7eca234674c3ca4150872f899af37611 to your computer and use it in GitHub Desktop.
<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> |
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})"); | |
} | |
} |
@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!
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?
note: the interesting thing is that it tries to parse the value of a previous column and not the correct column.