I am using a Sql server database. I have some spatial data (geometry
data type). I need to read them in a C#
web project using dapper
.
In my project I imported Microsoft.Spatial
nuget package, that Support OData v4.
I think in this way my project should be SQL-Server-Independent.
First problem I have found is understand which data type I should use to map Sql geometry
data type. I am trying to use Microsfot.Spatial.Geometry
that is an abstract class. But I am not sure.
Then this is the query I am writing and the mapping I am doing with dapper:
string sql = @"SELECT ..., departureAddress.GeometryLocation AS DepartureCoordinates, arrivalAddress.GeometryLocation AS ArrivalCoordinates ...";
var infoResultset = await this._connection.QueryAsync<MyInfoClass, ..., MyInfoClass>(
sql,
(request, ...) =>
{
/* Nothing about spatial types */
return result;
}
);
When I run the project I obtain this error:
Dapper: Error parsing column 3 (DepartureCoordinates=POINT (12.496365500000024 41.9027835) - Object). Unable to cast object of type 'Microsoft.SqlServer.Types.SqlGeometry' to type 'Microsoft.Spatial.Geometry'.
I have also tryed to use Microsoft.Spatial.GeometryPoint
but I obtain same error (just the destination type change in the message).
Can anyone help me to solve the mapping? Thank you
I have solved by changing the query and creating a new type handler:
string sql = @"SELECT ..., departureAddress.GeometryLocation.STAsText() AS DepartureCoordinates, arrivalAddress.GeometryLocation.STAsText() AS ArrivalCoordinates ...";
And this is the type handler I have written:
public class GeometryPointTypeHandler : SqlMapper.TypeHandler<GeometryPoint>
{
// POINT(X Y)
// POINT(X Y Z M)
public override GeometryPoint Parse(object value)
{
if (value == null)
return null;
if (!Regex.IsMatch(value.ToString(), @"^(POINT \()(.+)(\))"))
throw new Exception("Value is not a Geometry Point");
//Get values inside the brackets
string geometryPoints = value.ToString().Split('(', ')')[1];
//Split values by empty space
string[] geometryValues = geometryPoints.Split(' ');
double x = this.ConvertToDouble(geometryValues[0]);
double y = this.ConvertToDouble(geometryValues[1]);
double? z = null;
if (geometryValues.Length >= 3)
z = this.ConvertToDouble(geometryValues[2]);
double? m = null;
if (geometryValues.Length >= 4)
m = this.ConvertToDouble(geometryValues[3]);
return GeometryPoint.Create(x, y, z, m);
}
public override void SetValue(IDbDataParameter parameter, GeometryPoint value)
{
throw new NotImplementedException();
}
private double ConvertToDouble(string value)
{
return double.Parse(value, CultureInfo.InvariantCulture);
}
}
I have not implemented SetValue
because I do not need it.
Finally I added the handler to dapper:
Dapper.SqlMapper.AddTypeHandler(new GeometryPointTypeHandler());