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})"); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
You saved my nerves =) Thank you!