CODEWITHSUNDEEP
nhibernatenamed-querysqlgeography
Russ Cam
Russ Cam

Reputation: 125498

NHibernate pass Geography type to Named Query SetParameter()

I'm in the process of introducing NHibernate 3 into a data access layer for an ASP.NET MVC web application.

Within the SQL Server 2008 R2 database, a geography datatype is used to store lat/long coordinates in a table that maps to an entity within the application (call this EntityA).

I've set up fluent mappings to succesfully map the datatype to a property on EntityA of type GisSharpBlog.NetTopologySuite.Geometries.Point, with a custom mapping convention that maps it to a derived MsSql2008GeographyType.

The above all appears to be working correctly, but there are some stored procedures that are still used that expect one parameter to be of type geography. I've mapped the stored procedures as named queries, but I can't seem to figure out what type I should pass for the parameter of type geography. The method in which the query call happens takes a double latitude and a double longitude. In the old sproc call the mapping was done with

private static SqlGeography GetPoint(double latitude, double longitude)
{
    var geographyBuilder = new SqlGeographyBuilder();
    geographyBuilder.SetSrid(4326);
    geographyBuilder.BeginGeography(OpenGisGeographyType.Point);
    geographyBuilder.BeginFigure(latitude, longitude);
    geographyBuilder.EndFigure();
    geographyBuilder.EndGeography();
    return geographyBuilder.ConstructedGeography;
}

var point = GetPoint(latitude, longitude);

command.Parameters.Add(
    new SqlParameter("@Location", point) { UdtTypeName = "Geography" });

Running the SQL Profiler, the following command is executed

declare @p3 sys.geography
set @p3=convert(sys.geography,0xE6100000010CAD4D637B2DBA49400BEE77280AB404C0)
exec my-sproc-name @Location=@p3

When I try mapping with the following

var query = Session.GetNamedQuery("my-sproc-name");

var point = 
    new GisSharpBlog.NetTopologySuite.Geometries.Point(longitude, latitude);
query.SetParameter("Location", point);

I get a SqlException with Error converting data type varbinary to geography

[SqlException (0x80131904): Error converting data type varbinary to geography. Error converting data type varbinary to geography.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +2073502
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5064460
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +33
System.Data.SqlClient.SqlDataReader.get_MetaData() +86
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +311
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +987
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +162
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +32
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +141
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +12
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader() +12 NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd) +356 NHibernate.Driver.BatcherDataReaderWrapper..ctor(IBatcher batcher, IDbCommand command) +183
NHibernate.Driver.BasicResultSetsCommand.GetReader(Nullable`1 commandTimeout) +432 NHibernate.Impl.MultiQueryImpl.DoList() +683

Running the SQL Profiler, the following command is executed

exec sp_executesql N'exec my-sproc-name @p0' @p0=0x0001000000FFFFFFFF01000000000000000C02000000574E6574546F706F6C6F677953756974652C2056657273696F6E3D312E372E332E31373239382C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D663538306130353031366562616461310C030000004947656F4150492C2056657273696F6E3D312E312E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D6131613064613764656634363536373805010000002E4769735368617270426C6F672E4E6574546F706F6C6F677953756974652E47656F6D6574726965732E506F696E74090000000B636F6F7264696E6174657308656E76656C6F70651047656F6D657472792B666163746F72791147656F6D657472792B75736572446174611147656F6D657472792B656E76656C6F70650D47656F6D657472792B737269641247656F6D657472792B64696D656E73696F6E1147656F6D657472792B626F756E646172791A47656F6D657472792B626F756E6461727944696D656E73696F6E040404020400040404404769735368617270426C6F672E4E6574546F706F6C6F677953756974652E47656F6D6574726965732E436F6F7264696E617465417272617953657175656E6365020000001B47656F4150492E47656F6D6574726965732E49456E76656C6F706503000000384769735368617270426C6F672E4E6574546F706F6C6F677953756974652E47656F6D6574726965732E47656F6D65747279466163746F7279020000001B47656F4150492E47656F6D6574726965732E49456E76656C6F706503000000081C47656F4150492E47656F6D6574726965732E44696D656E73696F6E73030000001B47656F4150492E47656F6D6574726965732E4947656F6D65747279030000001C47656F4150492E47656F6D6574726965732E44696D656E73696F6E73030000000200000009040000000A09050000000A0A0000000005FAFFFFFF1C47656F4150492E47656F6D6574726965732E44696D656E73696F6E73010000000776616C75655F5F000803000000000000000A01F9FFFFFFFAFFFFFF000000000504000000404769735368617270426C6F672E4E6574546F706F6C6F677953756974652E47656F6D6574726965732E436F6F7264696E617465417272617953657175656E6365010000000B636F6F7264696E61746573041F47656F4150492E47656F6D6574726965732E49436F6F7264696E6174655B5D030000000200000009080000000505000000384769735368617270426C6F672E4E6574546F706F6C6F677953756974652E47656F6D6574726965732E47656F6D65747279466163746F7279030000000E707265636973696F6E4D6F64656C19636F6F7264696E61746553657175656E6365466163746F72790473726964040400374769735368617270426C6F672E4E6574546F706F6C6F677953756974652E47656F6D6574726965732E507265636973696F6E4D6F64656C02000000474769735368617270426C6F672E4E6574546F706F6C6F677953756974652E47656F6D6574726965732E436F6F7264696E617465417272617953657175656E6365466163746F72790200000008020000000909000000090A000000000000000708000000000100000001000000041D47656F4150492E47656F6D6574726965732E49436F6F7264696E61746503000000090B0000000509000000374769735368617270426C6F672E4E6574546F706F6C6F677953756974652E47656F6D6574726965732E507265636973696F6E4D6F64656C02000000096D6F64656C54797065057363616C6504002147656F4150492E47656F6D6574726965732E507265636973696F6E4D6F64656C7303000000060200000005F4FFFFFF2147656F4150492E47656F6D6574726965732E507265636973696F6E4D6F64656C73010000000776616C75655F5F000803000000000000000000000000000000050A000000474769735368617270426C6F672E4E6574546F706F6C6F677953756974652E47656F6D6574726965732E436F6F7264696E617465417272617953657175656E6365466163746F72790000000002000000050B000000334769735368617270426C6F672E4E6574546F706F6C6F677953756974652E47656F6D6574726965732E436F6F7264696E6174650300000001780179017A000000060606020000000BEE77280AB404C0AD4D637B2DBA4940000000000000F8FF0B

As can be seen, the value determined from what has been passed to SetParameter() and passed in the command is different.

Is it possible to pass a type to a named query to do what I want?

Upvotes: 3

Views: 1302

Answers (1)

Russ Cam
Russ Cam

Reputation: 125498

I figured it out after inspecting the NHibernate.Type.IType concrete types that the SetParameter() takes as a third argument. The following works

private static SqlGeography GetPoint(double latitude, double longitude)
{
    var geographyBuilder = new SqlGeographyBuilder();
    geographyBuilder.SetSrid(4326);
    geographyBuilder.BeginGeography(OpenGisGeographyType.Point);
    geographyBuilder.BeginFigure(latitude, longitude);
    geographyBuilder.EndFigure();
    geographyBuilder.EndGeography();
    return geographyBuilder.ConstructedGeography;
}

var query = Session.GetNamedQuery("my-sproc-name");

var point = GetPoint(latitude, longitude);
query.SetParameter(
    "Location", 
    point, 
    new NHibernate.Spatial.Type.SqlGeographyType());

// my-sproc-name is mapped to return a collection of EntityA types
query.List<EntityA>();

the NHibernate.Spatial.Type.SqlGeographyType type can be found in the NHibernate.Spatial.dll assembly.

Upvotes: 3

Related Questions