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

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...

Answer 2

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 3

public class NHibernateHelper
{       
    private static ISessionFactory _sessionFactory;

    private static ISessionFactory SessionFactory
    {
        get
        {
            if (_sessionFactory == null)
            {
                Configuration configuration = new Configuration();
                configuration.Configure();

             //   configuration.AddAssembly(typeof(clsDocumentMaster).Assembly);
                configuration.SetNamingStrategy(new MyClass());
                configuration.AddFile("clsDocumentMaster.hbm.xml");
                //configuration.AddFile("Level15.hbm.xml");

                _sessionFactory = configuration.BuildSessionFactory();
            }
            return _sessionFactory;
        }
    }
    public static ISession OpenSession()
    {
        return SessionFactory.OpenSession();
    }
}

public class MyClass : INamingStrategy
{
    #region INamingStrategy Members

    public string ClassToTableName(string className)
    {
        return DefaultNamingStrategy.Instance.ClassToTableName(className);
    }

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

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

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

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

    public string TableName(string tableName)
    {
        if (tableName.IndexOf("[TagtableName]") > -1)
            tableName = tableName.Replace("[TagtableName]", "TAG1");
        else
            tableName = DefaultNamingStrategy.Instance.TableName(tableName);

        return DefaultNamingStrategy.Instance.TableName(tableName);

    }

    #endregion
}