How to use Nhibernate with variable or dynamic table names like Jan08Tran,Feb08Tran,Mar08Tran

Question

I have legacy database in which transaction tables are stored by monthly names.

eg.

     Jan08Tran
     Feb08Tran

How can I use NHibernate to work with this tables?

Read a bit about entity-name property, SubClass etc. But not able to find concrete solution.

Answer 1

Holy #$%^! =)

I reckon this can be achieved with custom IEntityPersister, but this would be no easy task.

I'm not sure about that but is updatable view of any help?

Answer 2

One more option is to either use custom SQL to do persistence or to write sprocs to handle the ever-changing table names.

Answer 3

Searched a lot but nothing specific was available finally stepped line by line throgh the NHibernate source and found the following solution. This is not easy but still has a work around.

1. Create a new classs SqlInterceptor which implements IInterceptor
2. In the OnPrepareStatement method you can change the Sql as you want
3. Then add this SqlInterceptor to sesion though configuration.SetInterceptor(new SqlInterceptor());

Following is the code for SqlInterceptor

using System;
using System.Collections;
using NHibernate.SqlCommand;
using NHibernate.Type;

namespace NHibernate
{
    [Serializable]
    public class SqlInterceptor : IInterceptor
    {
        public virtual void OnDelete(object entity, object id, object[] state, string[] propertyNames, IType[] types)
        {
        }

        public void OnCollectionRecreate(object collection, object key)
        {
        }

        public void OnCollectionRemove(object collection, object key)
        {
        }

        public void OnCollectionUpdate(object collection, object key)
        {
        }

        public virtual bool OnFlushDirty(object entity, object id, object[] currentState, object[] previousState,
                                         string[] propertyNames, IType[] types)
        {
            return false;
        }

        public virtual bool OnLoad(object entity, object id, object[] state, string[] propertyNames, IType[] types)
        {
            return false;
        }

        public virtual bool OnSave(object entity, object id, object[] state, string[] propertyNames, IType[] types)
        {
            return false;
        }

        public virtual void PostFlush(ICollection entities)
        {
        }

        public virtual void PreFlush(ICollection entitites)
        {
        }

        public virtual bool? IsTransient(object entity)
        {
            return null;
        }

        public virtual object Instantiate(string clazz, EntityMode entityMode, object id)
        {
            return null;
        }

        public string GetEntityName(object entity)
        {
            return null;
        }

        public object GetEntity(string entityName, object id)
        {
            return null;
        }

        public virtual int[] FindDirty(object entity, object id, object[] currentState, object[] previousState,
                                       string[] propertyNames, IType[] types)
        {
            return null;
        }

        public virtual void AfterTransactionBegin(ITransaction tx)
        {
        }

        public virtual void BeforeTransactionCompletion(ITransaction tx)
        {
        }

        public virtual void AfterTransactionCompletion(ITransaction tx)
        {
        }

        public virtual void SetSession(ISession session)
        {
        }

        public SqlString OnPrepareStatement(SqlString sql)
        {
            ///Do something fancy here like  
            ///sql.Replace("_MonTranTable_", MonthName + "Tran");
            return sql;
        }
    }
}

I will further refine this and update back.

Answer 4

This is not a direct answer to your question, but perhaps it can be a solution after all.

Maybe you can add a view or two to the DB which will make all those dynamic tables look like one?

Answer 5

I had the exact same problem and my approach was to update NHibernate's configuration values at runtime. In this way I could decide which of my many identically named tables I would talk to. The basis of the technique is this :-

private static void SetTableMapping(Configuration config, 
     Type persistentClass, string newTableName)
{
    PersistentClass classMapping = config.GetClassMapping(persistentClass);
    Table physicalTable = classMapping.RootTable;
    physicalTable.Name = newTableName;
}

Answer 6

I had a similar situation where I had to provide an interface between to purchased applications that were in production and being used by tons of different systems. This system had different table names throughout dev, test, and prod (ridiculous...) My solution was to leave a placeholder for the table number in the NHibernate config like so:

<class name="MyClass" table="MyTable[tableNumber]">

And then implement INamingStrategy similar to:

public class MyCustomNamingStrategy : INamingStrategy
{
    public string ClassToTableName(string className)
    {
        return DefaultNamingStrategy.Instance.ClassToTableName(className);
    }

    public string PropertyToColumnName(string propertyName)
    {
        return DefaultNamingStrategy.Instance.PropertyToColumnName(propertyName);
    }

    public string TableName(string tableName)
    {
        tableName = tableName.Replace("[tableNumber]", LocalSettings.TableNumber);
        return DefaultNamingStrategy.Instance.TableName(tableName);
    }

    public string ColumnName(string columnName)
    {
        return DefaultNamingStrategy.Instance.ColumnName(columnName);
    }

    public string PropertyToTableName(string className, string propertyName)
    {
        return DefaultNamingStrategy.Instance.PropertyToTableName(className, propertyName);
    }

    public string LogicalColumnName(string columnName, string propertyName)
    {
        return DefaultNamingStrategy.Instance.LogicalColumnName(columnName, propertyName);
    }
}

And then set the naming strategy in the configuration:

myConfiguration.SetNamingStrategy(new MyCustomNamingStrategy());

This way the table number could be stored in the App.config and the application could be moved across environments by only changing values in the App.config. I'm sure you could find a way to use this to change the table name to whatever date you needed...