使用SQL Server 2008地理学类型和nHibernate的CreateSQLQuery

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

我正在尝试用nHibernate (2.0.1GA)发出一个SQL update语句:

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

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

但是，我收到以下错误:'geography@p0'不是一个公认的内置函数名。

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?

我认为CreateSQLQuery会传递我给它的SQL并执行它…猜不是。在nHibernate的背景下我怎么做呢?

4 个解决方案

#1

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

我很确定我能告诉你发生了什么，但我不知道是否有解决办法。

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

我认为问题在于，NHibernate使用了“:”字符来创建一个命名参数。你的表情变成:

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

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.

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

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源代码的快速浏览没有找到;命名参数在nhibernate . engineering . query中处理。如果您想花更多时间搜索的话，可以使用ParameterParser。

#2

"{whatever} is not a recognized built-in function name" is a SQL Server error message, not sure what Hibernate is doing there but SQL Server is the one complaining about it.

“{whatever}不是公认的内置函数名”是一条SQL Server错误消息，不确定Hibernate在那里做什么，但是SQL Server才是抱怨它的人。

#3

There is an implicit conversion from varchar to Point.

从varchar到Point有一个隐式转换。

Use NHibernate to set the geographic parameters to their string representation

使用NHibernate将地理参数设置为它们的字符串表示形式

Define a SQL query template with named paramter loc:

定义一个名为paramter loc的SQL查询模板:

const string Query = @"SELECT   {location.*}
FROM     {location}
WHERE    {location}.STDistance(:loc) is not null
ORDER BY {location}.STDistance(:loc)";

Set the parameter to a string representation of Point:

将参数设置为点的字符串表示形式:

return session
                .CreateSQLQuery(Query)
                .AddEntity("location", typeof (Location))
                .SetString("loc", "Point (53.39006999999999 -3.0084007)")
                .SetMaxResults(1)
                .UniqueResult<Location>();

This is for a Select. but I see no reason why it wouldn't work for an Insert or Update.

这是一个选择。但是我看不出为什么它不能用于插入或更新。

#4

Following on @Chris's answer, here is a copy and paste solution:

根据@Chris的回答，这里有一个复制粘贴解决方案:

CREATE FUNCTION GetPoint 
(
    @lat float,
    @lng float,
    @srid int
)
RETURNS geography
AS
BEGIN

declare @point geography = geography::Point(@lat, @lng, @srid);

RETURN @point

END
GO

The you do

你做

dbo.GetPoint(@Latitude, @Longitude, 4326)

instead of

而不是

geography::Point(@Latitude, @Longitude, 4326);

And NH is happy

NH是快乐的

#1