结合使用Dapper和SQL Spatial Types作为参数

问题描述

我有一个基本上必须执行如下查询的系统:

I've got a system which basically has to do a query like this:

SELECT * FROM MyTable WHERE @parameter.STIntersects(MyGeometryColumn)

这在使用普通SQL参数时非常简单，您只需以非典型方式创建参数(其中，builder变量是我用来创建矩形的SqlGeometryBuilder):

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"
});

现在，当我尝试使用dapper进行此操作时，出现一个错误，提示它无法弄清楚如何将此参数用作参数.任何人都可以使用此功能，或者有关如何启用此功能的任何指示?我确实有一种解决方法，但是其中涉及使用字符串表示形式并将其转换为SQL查询中的几何类型.我真的不想要那个.

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.

要回答评论，我得到的错误是"Microsoft.SqlServer.Types.SqlGeometry类型的成员参数不能用作参数值".换句话说，dapper不知道如何将SqlGeometry对象作为参数来处理.

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.

推荐答案

实现怪异而美妙的数据库特定参数的关键都归结为SqlMapper.IDynamicParameters

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已经具有此接口的数据库通用实现:DynamicParameters，它允许您处理输出和返回值.

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
       });
    }
}

用法:

cnn.Query("SELECT * FROM MyTable WHERE @parameter.STIntersects(MyGeometryColumn)",
  new SpatialParam("@parameter", builder.ConstructedGeometry));

---

接口的这种简单实现只处理一个参数，但是可以很容易地扩展为处理多个参数，方法是从构造函数中传入，或者添加一个辅助AddParameter方法.

---

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.

这篇关于结合使用Dapper和SQL Spatial Types作为参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！