7

I have a SQL Server 2012 database with a table that contains a geography column and I want to use Dapper in a .Net application working with that database, but as far as I can tell and see in the Dapper code, "only" Entity Framework's DBGeography type is supported, the underlying SQLGeography data type has no other mentioning in the repository.

Can Dapper handle these column types 'magically' nevertheless or would I have to explicitly write a Dapper.SqlMapper.TypeHandler for these?

Jörg Battermann
  • 4,044
  • 5
  • 42
  • 79
  • What type(s) are you using currently to map these? Are you using `SqlGeography` directly? or...? – Marc Gravell Aug 28 '14 at 06:47
  • related; http://stackoverflow.com/questions/6690565/using-dapper-with-sql-spatial-types-as-a-parameter/25544690#25544690 and http://stackoverflow.com/questions/12090549/mapping-sqlgeography-with-dapper?lq=1 – Marc Gravell Aug 28 '14 at 08:56

1 Answers1

9

Support for SqlGeography has been added in the next release, again via the Dapper.EntityFramework package. I haven't built/deployed yet, as I am in two minds as to whether that is the most appropriate assembly for it to live in... but I also don't want to take a dependency on Microsoft.SqlServer.Types in the core library. There may be a way of doing it without that, though.

Update: this has now moved up a level to the core library, so you shouldn't need any EF references or Dapper.EntityFramework; it should just work; this has been pushed as Dapper 1.32.

Example:

public void SqlGeography_SO25538154()
{
    Dapper.SqlMapper.ResetTypeHandlers(); // to show it doesn't depend on any
    connection.Execute("create table #SqlGeo (id int, geo geography)");

    var obj = new HazSqlGeo
    {
        Id = 1,
        Geo = SqlGeography.STLineFromText(
            new SqlChars(new SqlString(
                "LINESTRING(-122.360 47.656, -122.343 47.656 )")), 4326)
    };
    connection.Execute("insert #SqlGeo(id, geo) values (@Id, @Geo)", obj);
    var row = connection.Query<HazSqlGeo>(
        "select * from #SqlGeo where id=1").SingleOrDefault();
    row.IsNotNull();
    row.Id.IsEqualTo(1);
    row.Geo.IsNotNull();
}

class HazSqlGeo
{
    public int Id { get; set; }
    public SqlGeography Geo { get; set; }
}
Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Awesome, that's exactly what I was looking/hoping for - thanks Marc! – Jörg Battermann Aug 28 '14 at 08:28
  • @JörgB. let me know if you get any problems, etc – Marc Gravell Aug 28 '14 at 08:29
  • Will do - going to give this a thorough try later today. – Jörg Battermann Aug 28 '14 at 08:30
  • 1
    @JörgB. note: 1.34 supercedes this and includes geometry and hierarchyid support – Marc Gravell Aug 28 '14 at 09:09
  • When using 1.34 inserting works perfectly fine (I can see it in SSMS), however I cannot retrieve the entry - I get a System.Data.DataException (see https://gist.github.com/jbattermann/07308bda78f12645e9e2). Sample app / data looks like this: https://gist.github.com/jbattermann/ab4a436c669714e4561b. I've referenced Dapper (1.34) & and the Microsoft.SqlServer.Types NuGet package but that exception complains about a 10.0.0 & 11.0.0 assembly mismatch. Sql Server in this case is SQL Express 2014. Is that dapper related or something else going on? – Jörg Battermann Aug 28 '14 at 20:52
  • @Jorg have you tried an assembly binding redirect from 10 to 11? You can look inthe app.config of the dapper unit test project for an example. – Marc Gravell Aug 28 '14 at 20:56
  • Binding redirect.. oh my, forgot about that one - but it worked! \o/ – Jörg Battermann Aug 28 '14 at 21:05
  • I had the 10.0.0 and 11.0.0 issue as well, with the nuget package. The reason is that Dapper seems to use the Microsoft.SqlServer.Types.dll from the MS Sql Server SDK, while the NuGet package adds a 2nd copy to your system. Simply removing the nuget package and adding a reference to C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Types.dll instead fixed this for me. – Legolas Feb 18 '16 at 12:22
  • I'm "using Dapper" but I don't see a SqlGeography type. What namespace is this in? I pulled Dapper down from nuget. I'm running the latest stable version: 1.42.0 – mac10688 Apr 07 '16 at 21:52
  • I think I just found it in Microsoft.SqlServer.Types – mac10688 Apr 07 '16 at 22:03
  • @MarcGravell is there special handling for dot net core 5.0? I am unable to resolve SqlGeography type using the Dapper package. – Aveer28 May 19 '21 at 13:52
  • @loopy78 not currently; if you have a knowledge of what is required for this on .NET 5 etc: a PR would be welcome – Marc Gravell May 19 '21 at 14:10
  • @MarcGravell the following implementation provides a workaround https://gist.github.com/bricelam/7eca234674c3ca4150872f899af37611 – Aveer28 May 19 '21 at 17:38