将SQL Server 2008地理类型与nHibernate的CreateSQLQuery结合使用

问题描述

我正在尝试使用nHibernate(2.0.1GA)发出这样的SQL更新语句:

I am trying to issue a SQL update statement with nHibernate (2.0.1GA) like this:

sqlstring = string.Format("set nocount on;update myusers set geo=geography::Point({0}, {1}, 4326) where userid={2};", mlat, mlong, userid);
_session.CreateSQLQuery(sqlstring).ExecuteUpdate();

但是，我收到以下错误消息:'geography @ p0'不是公认的内置函数名称.

However I receive the following error: 'geography@p0' is not a recognized built-in function name.

我以为CreateSQLQuery会传递我给它的SQL并执行它...不要猜测.关于如何在nHibernate上下文中实现此目标的任何想法?

I thought CreateSQLQuery would just pass the SQL I gave it and execute it...guess not. Any ideas on how I can do that within the context of nHibernate?

推荐答案

我很确定我可以告诉您正在发生的事情，但是我不知道是否有解决方法.

I'm pretty sure I can tell you what is happening, but I don't know if there is a fix for it.

我认为问题在于NHibernate使用':'字符来创建命名参数.您的表情将更改为:

I think the problem is that the ':' character is used by NHibernate to create a named parameter. Your expression is getting changed to:

set nocount on;update myusers set geo=geography@p0({0}, {1}, 4326) where userid={2};

@ p0将是一个SQL变量.不幸的是，我找不到用于转义冒号的任何文档，因此它们不被视为命名参数.

And @p0 is going to be a SQL variable. Unfortunately I can't find any documentation for escaping colons so they are not treated as a named parameter.

如果存在转义字符(我对NHibernate源代码的快速浏览未找到其中一个；如果您想花更多的时间在NHibernate.Engine.Query.ParameterParser中处理已命名的参数)，则可以使用那个.

If an escape character exists (my quick skim of the NHibernate source didn't find one; Named parameters are handled in NHibernate.Engine.Query.ParameterParser if you want to spend a little more time searching), then you could use that.

其他解决方案:

• 在源代码中添加一个转义字符.然后，您可以使用NHibernate的修改版.如果这样做，则应将补丁提交给团队，以便可以将其包含在真实内容中，而不必维护源代码的修改版本(不好玩).
• 在您的数据库中创建一个返回geography :: Point的用户定义函数，然后调用您的函数而不是标准SQL函数.这似乎是最快/最简单的启动和运行方式，但也有点像创可贴.
• 查看 NHibernate Spatial 中是否有东西可以让您以编程方式添加geography :: Point()[或编辑该项目的代码以添加一个代码并将补丁提交给该团队].

• Add an escape character to the source. You can then use a modified version of NHibernate. If you do this, you should submit your patch to the team so it can be included in the real thing and you don't have to maintain a modified version of the source (no fun).
• Create a user defined function in your DB that returns a geography::Point, then call your function instead of the standard SQL function. This seems like the quickest/easiest way to get up and running, but also feels a bit like a band-aid.
• See if there is something in NHibernate Spatial that will let you programmatically add the geography::Point() [or edit the code for that project to add one and submit the patch to that team].

这篇关于将SQL Server 2008地理类型与nHibernate的CreateSQLQuery结合使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！