Wednesday 8 February 2012

Fluent NHibernate PK Rename convention (SQL Server)

Following on from my previous post of using some sql to enable the SchemaExport tool to make changes outside of its current capabilities, I created a simple convention for Fluent NHibernate which enables you to name the PK’s of tables.
Currently this only works with SQL Server, but I believe that this approach should also be possible in other databases.
using System.Linq;
using System.Text;
using Iesi.Collections.Generic;
using NHibernate.Dialect;
using NHibernate.Mapping;

namespace Conventions
{
    public class CustomPrimaryKeyIndexNameConvention
    {
        public static void CreateUpdatePkNameScript(NHibernate.Cfg.Configuration config)
        {
            var script = new StringBuilder("");
            script.AppendLine("DECLARE @keyName AS SYSNAME");
            script.AppendLine();
            foreach (var tableName in config.ClassMappings.Select(m => m.Table.Name).Distinct())
            {
                script.AppendFormat(string.Format("SELECT  @keyName = name FROM sys.key_constraints kc WHERE kc.parent_object_id = OBJECT_ID('dbo.{0}', 'U')", tableName));
                script.AppendLine();
                script.AppendFormat("EXEC sp_rename @keyName, N'PK_{0}', N'OBJECT';", tableName);
                script.AppendLine();
            }

            config.AddAuxiliaryDatabaseObject(new SimpleAuxiliaryDatabaseObject(script.ToString(), null, new HashedSet<string> { typeof(MsSql2000Dialect).FullName, typeof(MsSql2005Dialect).FullName, typeof(MsSql2008Dialect).FullName }));
        }
    }
}
As you can see, the convention is to use “PK_<TableName>” for the PK name and is applied using the Auxiliary database extensions:
.ExposeConfiguration(CustomPrimaryKeyIndexNameConvention.CreateUpdatePkNameScript)

Submit this story to DotNetKicks Shout it

Monday 6 February 2012

Fluent NHibernate Solution to enable SchemaExport to create HiLo columns

I spent a few hours recently trying to figure out why Fluent NHibernate (FNH) wasn’t doing what I thought it should be doing when using the HiLo generator conventions and then using the SchemaExport tool to generate my schema. After eventually looking at the hbm files that FNH was producing for a possible error and to make sure that I was using FNH correctly, I realised that FNH wasn't the problem, and that the problem lies within SchemaExport.
Fabio Maulo puts this down to design and gives a solution for using ConfORM. I agree with the comments made and find his answer very unsatisfactory and that there is a bug and that it should be fixed.
Whilst I am sure that that debate will roll on, I have created a solution that matches Fabio’s one for ConfORM but for FNH shown below:
using System.Linq;
using System.Text;
using FluentNHibernate.Conventions;
using FluentNHibernate.Conventions.Instances;
using Iesi.Collections.Generic;
using NHibernate.Dialect;
using NHibernate.Mapping;

namespace Conventions
{
    public class CustomIdentityHiLoGeneratorConvention :IIdConvention
    {
        public const string NextHiValueColumnName= "NextHiValue";
        public const string NHibernateHiLoIdentityTableName = "NHibernateHiLoIdentity";
        public const string TableColumnName = "Entity";

        #region Implementation of IConvention<IIdentityInspector,IIdentityInstance>

        public void Apply(IIdentityInstance instance)
        {
            instance.GeneratedBy.HiLo(NHibernateHiLoIdentityTableName, NextHiValueColumnName, "500", builder => builder.AddParam("where", string.Format("{0} = '[{1}]'", TableColumnName, instance.EntityType.Name)));
        }

        #endregion
        
        public static void CreateHighLowScript(NHibernate.Cfg.Configuration config)
        {
            var script = new StringBuilder();
            script.AppendFormat("DELETE FROM {0};", NHibernateHiLoIdentityTableName);
            script.AppendLine();
            script.AppendFormat("ALTER TABLE {0} ADD {1} VARCHAR(128) NOT NULL;", NHibernateHiLoIdentityTableName, TableColumnName);
            script.AppendLine();
            script.AppendFormat("CREATE NONCLUSTERED INDEX IX_{0}_{1} ON {0} (Entity ASC);", NHibernateHiLoIdentityTableName, TableColumnName);
            script.AppendLine();
            script.AppendLine("GO");
            script.AppendLine();
            foreach (var tableName in config.ClassMappings.Select(m => m.Table.Name).Distinct())
            {
                script.AppendFormat(string.Format("INSERT INTO [{0}] ({1}, {2}) VALUES ('[{3}]',1);", NHibernateHiLoIdentityTableName, TableColumnName, NextHiValueColumnName, tableName));
                script.AppendLine();
            }
            
            config.AddAuxiliaryDatabaseObject(new SimpleAuxiliaryDatabaseObject(script.ToString(), null, new HashedSet<string> { typeof(MsSql2000Dialect).FullName, typeof(MsSql2005Dialect).FullName, typeof(MsSql2008Dialect).FullName }));
        }
    }
}
This is then used as follows:
conventions.Add<CustomIdentityHiLoGeneratorConvention>();
and:
.ExposeConfiguration(CustomIdentityHiLoGeneratorConvention.CreateHighLowScript)

Submit this story to DotNetKicks Shout it