问题描述
我有一个系统,它基本上必须执行这样的查询:
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.
推荐答案
实现奇葩的 DB 特定参数的关键都归结为 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 已经有这个接口的 DB 通用实现,称为: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.
这篇关于使用带有 SQL 空间类型的 Dapper 作为参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!