I've got a system which basically has to do a query like this:
SELECT * FROM MyTable WHERE @parameter.STIntersects(MyGeometryColumn)
This is quite simple to do when using vanilla SQL parameters, you just have to create your parameter in a non-typical way (where the builder variable is a SqlGeometryBuilder which I use to create a rectangle):
command.Parameters.Add(new SqlParameter
{
UdtTypeName = "geometry",
Value = builder.ConstructedGeometry,
ParameterName = "@paremeter"
});
Now, When I try to do this using dapper, I get an error that it can't figure out how to use this as a parameter. Anyone who has got this working, or any pointers on how to enable this? I do have a workaround, but that involves using the string representation and converting that to a geometry type in my SQL query. I really don't want that.
To answer the comment, the error I'm getting is 'The member Parameter of type Microsoft.SqlServer.Types.SqlGeometry cannot be used as a parameter value'. In other words, dapper doesn't know how to deal with a SqlGeometry object as a parameter.
SQL Server supports two spatial data types: the geometry data type and the geography data type. The geometry type represents data in a Euclidean (flat) coordinate system. The geography type represents data in a round-earth coordinate system.
Dapper is an example of Micro ORM, in fact, it is called the King of Micro ORM because of its speed and ease of work. First, it creates an IDbConnection object and allows us to write queries to perform CRUD operations on the database.
The key to implementing weird and wonderful DB specific params all boils down to SqlMapper.IDynamicParameters
This simple interface has a single endpoint:
public interface IDynamicParameters
{
void AddParameters(IDbCommand command);
}
Dapper already has a DB generic implementation of this interface called: DynamicParameters
which allows you to handle output and return values.
To emulate this spatial stuff I would try something like:
public class SpatialParam : SqlMapper.IDynamicParameters
{
string name;
object val;
public SpatialParam(string name, object val)
{
this.name = name;
this.val = val;
}
public void AddParameters(IDbCommand command, SqlMapper.Identity identity)
{
var sqlCommand = (SqlCommand)command;
sqlCommand.Parameters.Add(new SqlParameter
{
UdtTypeName = "geometry",
Value = val,
ParameterName = name
});
}
}
Usage:
cnn.Query("SELECT * FROM MyTable WHERE @parameter.STIntersects(MyGeometryColumn)",
new SpatialParam("@parameter", builder.ConstructedGeometry));
This simple implementation of the interface handles only a single param, but it can easily be extended to handle multiple params, either by passing in from the constructor or adding a helper AddParameter method.
If you don't mind modifying Dapper at its core then you can use what I've done... https://gist.github.com/brendanmckenzie/4961483
I modified Dapper to accept Microsoft.SqlServer.Types.SqlGeography
parameters.
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