SQL Server 2012 Dialect

[casualtrash]

Hello

Would you upgrade the project and add also a new version for the SQL Server 2012 dialect?

[suryapratap]

can you explain a little more. dialect is usually handled by NHibernate. The spatial extensions to the dialects are added by NH Spatial

In general you should be able to use SQL 2008 just as well for SQL 2012.

you are welcome to send in patches too :)

[casualtrash]

I though you can specify only one dialect in the configuration, so if I configure nhibernate as here below, I use the sql server 2008 dialect with the spatial extension. If I want to use the sql server 2012 dialect with the spatial extension, what I have to set in the nhibernate configuration file?

<session-factory> ... <property name="dialect">NHibernate.Spatial.Dialect.MsSql2008GeographyDialect,NHibernate.Spatial.MsSql2008</property > ... </session-factory>

[suryapratap]

The dialects in Nhibernate are just changes to a basic SQL as per server type and version. In this case SQL2008 Dialect should work just fine in SQL2012, there might be some edge cases, we can look into those as they are reported.

[casualtrash]

Using it with sql server 2012 throws the following exception:

Unable to load DLL 'SqlServerSpatial.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)
   at Microsoft.SqlServer.Types.GLNativeMethods.IsValid(GeoMarshalData g, Boolean& result)
   at Microsoft.SqlServer.Types.GLNativeMethods.IsValid(GeoData g)
   at Microsoft.SqlServer.Types.SqlGeometry.IsValidExpensive()
   at Microsoft.SqlServer.Types.SqlGeometry..ctor(GeoData g, Int32 srid)
   at Microsoft.SqlServer.Types.SqlGeometryBuilder.get_ConstructedGeometry()
   at NHibernate.Spatial.Type.MsSql2008GeometryWriter.Write(IGeometry geometry) in C:\Test\Nhibernate.Spatial-master\NHibernate.Spatial.MsSql2008\Type\MsSql2008GeometryWriter.cs:line 32
   at NHibernate.Spatial.Type.MsSql2008GeometryType.FromGeometry(Object value) in C:\Test\Nhibernate.Spatial-master\NHibernate.Spatial.MsSql2008\Type\MsSql2008GeometryType.cs:line 71
   at NHibernate.Spatial.Type.GeometryTypeBase`1.DeepCopy(Object value) in C:\Test\Nhibernate.Spatial-master\NHibernate.Spatial\Type\GeometryTypeBase.cs:line 76
   at NHibernate.Spatial.Type.GeometryType.DeepCopy(Object value) in C:\Test\Nhibernate.Spatial-master\NHibernate.Spatial\Type\GeometryType.cs:line 75
   at NHibernate.Type.CustomType.DeepCopy(Object value, EntityMode entityMode, ISessionFactoryImplementor factory)
   at NHibernate.Type.TypeHelper.DeepCopy(Object[] values, IType[] types, Boolean[] copy, Object[] target, ISessionImplementor session)
   at NHibernate.Event.Default.AbstractSaveEventListener.PerformSaveOrReplicate(Object entity, EntityKey key, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess)
   at NHibernate.Event.Default.AbstractSaveEventListener.PerformSave(Object entity, Object id, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess)
   at NHibernate.Event.Default.AbstractSaveEventListener.SaveWithGeneratedId(Object entity, String entityName, Object anything, IEventSource source, Boolean requiresImmediateIdAccess)
   at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.SaveWithGeneratedOrRequestedId(SaveOrUpdateEvent event)
   at NHibernate.Event.Default.DefaultSaveEventListener.SaveWithGeneratedOrRequestedId(SaveOrUpdateEvent event)
   at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.EntityIsTransient(SaveOrUpdateEvent event)
   at NHibernate.Event.Default.DefaultSaveEventListener.PerformSaveOrUpdate(SaveOrUpdateEvent event)
   at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.OnSaveOrUpdate(SaveOrUpdateEvent event)
   at NHibernate.Impl.SessionImpl.FireSave(SaveOrUpdateEvent event)
   at NHibernate.Impl.SessionImpl.Save(Object obj)
   at Tests.NHibernate.Spatial.OgcSfSql11Compliance.ConformanceItemsFixture.OnTestFixtureSetUp() in C:\Test\Nhibernate.Spatial-master\Tests.NHibernate.Spatial\OgcSfSql11Compliance\ConformanceItemsFixture.cs:line 92
   at Tests.NHibernate.Spatial.AbstractFixture.TestFixtureSetUp() in C:\Test\Nhibernate.Spatial-master\Tests.NHibernate.Spatial\AbstractFixture.cs:line 74

I suppose it tries to use load a dll of sql server 2008 but there is only sql server 2012

[suryapratap]

Unable to load DLL 'SqlServerSpatial.dll': The specified module could not be found.

This generally occurs when the dll specified is not available in the path mentioned, what you should try to do is to find the actual location of the dll and re-register it in the project.

may be this will help http://stackoverflow.com/questions/16868326/unable-to-load-dll-sqlserverspatial-dll

[casualtrash]

I had already googled for the error and tried all the suggested solutions but nothing did work, because:

1. The project doesn't reference the "SqlServerSpatial.dll", also if you add the reference to this library is useless.
2. The nHibernate Spatial project is "referncing" the "SqlServerSpatial.ddl" and exactly with the nuget package "Microsoft.SqlServer.Types", which is a sql server 2008 dll
3. Removing the reference of the nuget package and adding the reference to the "Microsoft.SqlServer.Types" of sql server 2012 is also useless, because then you have a dll version mismatching, the sql server 2008 dialect uses the dll version 10.50.1600 and the generated objects of this version can't be converted to version 11.0.3000 of the sql server 2012. Here somebody asking for retro compatibility of sql server 2012 with sql server 2008, and the dll aren't compatible. http://stackoverflow.com/questions/14675774/is-the-sql-server-2012-version-of-microsoft-sqlserver-types-geometry-udt-backwa The only solution is to download from microsoft the sql server 2008 clr types and install it, although this is not really what you want, you would like to have only sql server 2012 and use only its assemblies and dialect. This is exactly, what is pushing me away from nhibernate, this project is dying slowly and I don't want to be on this ship when it'll be too late.

[suryapratap]

can you make a test project and send it over, I will install sql2012 in my computer and check.

have you tried setting an assembly binding redirect in your app.config.

you are welcome to send in patches or solutions if you have any.

[augustonunespg]

I am developing an application that uses spatial data. with fluent nhibernate to use sql 2012 and my problem's there. I did a project for sql 2008 using nhibernate.spatial and worked perfectly, have any idea how I map in 2012?

in 2008

public virtual IPolygon Location2 { get; set; }

Map(x => x.Location2).CustomType();

[Serializable] public class Wgs84GeographyType : MsSql2008GeographyType { protected override void SetDefaultSRID(GeoAPI.Geometries.IGeometry geometry) { geometry.SRID = 4326; } }

Dll: GeoAPI.Geometries and NHibernate.Spatial.Type.

who i use in 2012?

[suryapratap]

If you do not need any special features from SQL2012 you can use the same mapping and components as of SQL2008

[augustonunespg]

Can you help me?

Mapping

using System; using FluentNHibernate.Mapping; using NHibernate.Spatial.Type;

namespace MvcApplication1 { public class CitySpatialMap : ClassMap { public CitySpatialMap() { Table("City"); Id(x => x.CityId); Map(x => x.Location).CustomType(); } }

[Serializable]
public class GeographyType : MsSql2008GeographyType
{
    protected override void SetDefaultSRID(GeoAPI.Geometries.IGeometry geometry)
    {
        geometry.SRID = 4326;
    }
}

}

Class

using System; using GeoAPI.Geometries;

namespace MvcApplication1 { public class CitySpatial { public virtual Guid CityId { get; set; }

    public virtual IGeometry Location { get; set; }
}

}

SQL server 2012

CREATE TABLE [dbo].[City]([CityId] [uniqueidentifier] NOT NULL, [CreatedAt] [datetime] NOT NULL, [UpdatedAt] [datetime] NULL, [StateId] [uniqueidentifier] NOT NULL, [Name] [varchar]%2850%29 NOT NULL, [FullName] [varchar]%28100%29 NOT NULL, [Code] [bigint] NOT NULL, [BACEN] [bigint] NOT NULL, [IBGE] [bigint] NOT NULL, [Location] [geometry] NULL)

Where am I going wrong?

[suryapratap]

Can you upload your project or atleast the part causing the issue, I will have a look, I cannot promise on anything soon but will give it a shot :)

[andrerav]

SQL Server 2012 support has been implemented. To specify which version of the Microsoft.SqlServer.Types should be used at runtime, specify Type System Version in the connection string, as so:

server=localhost;database=MyDB;Type System Version=SQL Server 2012