Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using Dapper with SQL Spatial Types as a parameter

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.

like image 834
Erik van Brakel Avatar asked Jul 14 '11 08:07

Erik van Brakel


People also ask

Is a spatial data type in SQL?

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.

What is the use of dapper in C#?

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.


2 Answers

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.

like image 140
Sam Saffron Avatar answered Oct 22 '22 05:10

Sam Saffron


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.

like image 43
Brendan Avatar answered Oct 22 '22 07:10

Brendan