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?
Dapper does not support DB Provider specific data types. In your case its GEOGRAPHY. Dapper has no DB specific implementation details, it works across all .net ado providers including sqlite, sqlce, firebird, oracle, MySQL and SQL Server In order to handle this param with Dapper, you would have to write your own handling for it.
And column 5 is a SqlGeography polygon. Be careful to use the right version of SqlGeography depending on which ms database version you are using. I had to install SqlGeography Version 10.5 for Sql 2012. Then it worked fine with Dapper.
SQL Server supports a set of methods for the geography spatial data type. This includes methods on geography that are defined by the Open Geospatial Consortium (OGC) standard and a set of Microsoft extensions to that standard.
It's a good example of Dapper's extensibility. Does Dapper support my database provider? Probably yes since Dapper provides extensions to the IDbConnection interface. It's your job to write the SQL compatible with your database provider.
Support for SqlGeography
has been added in the next release, again via the . 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 Dapper.EntityFramework
packageMicrosoft.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; }
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With