Using Dapper with SQL Spatial Types as a parameter

Question

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.

What is the error that you are getting? – Neil Knight Jul 14 '11 at 08:52 — Neil Knight, Jul 14 '11 at 08:52

score 10 · Accepted Answer · edited May 07 '14 at 20:10

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.

NOTE: The current version of Dapper (as of me writing this comment) has a slightly different Interface method signature, now => `void AddParameters(IDbCommand command, SqlMapper.Identity identity)` — Pure.Krome, Jul 09 '13 at 04:06

Brendan · Answer 2 · 2014-07-11T04:44:14.207

5

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.

edited Jul 11 '14 at 04:44

answered Feb 15 '13 at 16:28

Brendan

3,415
24
26

Marc Gravell · Answer 3 · 2014-08-28T09:09:12.517

3

Dapper.EntityFramework 1.26 has support for DbGeography
Dapper 1.32 has inbuilt support for SqlGeography
Dapper 1.33 has inbuilt support for SqlGeometry
Dapper.EntityFramework 1.33 has inbuilt support for DbGeometry
Dapper 1.34 has inbuilt support for SqlHierarchyId

So with the latest libraries; it should simply work.

edited Aug 28 '14 at 09:09

answered Aug 28 '14 at 08:55

Marc Gravell

1,026,079
266
2,566
2,900

Using Dapper with SQL Spatial Types as a parameter

3 Answers3

Linked