Last active
March 24, 2024 12:25
-
-
Save bricelam/7eca234674c3ca4150872f899af37611 to your computer and use it in GitHub Desktop.
Using NTS with Dapper
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<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> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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})"); | |
} | |
} |
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!
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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 } });