ITKeyword,专注技术干货聚合推荐

注册 | 登录

解决sql server 2008 - Using Dapper with SQL Spatial Types as a parameter

itPublisher 分享于

2020腾讯云双十一活动,全年最低!!!(领取3500元代金券),
地址https://cloud.tencent.com/act/cps/redirect?redirect=1073

【阿里云】双十一活动,全年抄底价,限时3天!(老用户也有),
入口地址https://www.aliyun.com/1111/home

推荐:Sql Server 2008

创建函数: create function sector_pid(@id varchar(10)) returns @t_level table(id varchar(10)) as begin   insert into @t_level select @id   select @id 

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-2008 orm spatial dapper

|
  this question
edited Jul 14 '11 at 8:58 asked Jul 14 '11 at 8:41 Erik van Brakel 13.9k 2 42 61
    
What is the error that you are getting? –  Neil Knight Jul 14 '11 at 8:52


 | 

3 Answers
3

解决方法

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:

推荐:SQL Server 2008

΢

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.


|
  this answer
edited May 7 '14 at 20:10 stimms 20.5k 21 74 127 answered Jul 14 '11 at 23:46 Sam Saffron 78.8k 57 264 451
    
It works perfectly! Thanks :) –  Erik van Brakel Jul 15 '11 at 11:26
    
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 9 '13 at 4:06


 | 

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.


|
  this answer
edited Jul 11 '14 at 4:44 answered Feb 15 '13 at 16:28 Brendan 2,417 10 18


 | 
  • 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.


|
  this answer
edited Aug 28 '14 at 9:09 answered Aug 28 '14 at 8:55 Marc Gravell ♦ 670k 164 1875 2324


 | 

推荐:SQL Server 2008 的安装

               SQL Server 2008简体中文企业版下载(SQL2008)   SQL Server 2008分为SQL Server 2008企业版、标准版、工作组版、Web版、开发者版、Express版、Co


相关阅读排行


相关内容推荐

最新文章

×

×

请激活账号

为了能正常使用评论、编辑功能及以后陆续为用户提供的其他产品,请激活账号。

您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱

如果您没有收到激活邮件,请注意检查垃圾箱。