CODEWITHSUNDEEP
.netnhibernate
Sachin Chavan
Sachin Chavan

Reputation: 5614

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

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.

Upvotes: 9

Views: 7097

Answers (7)

Sachin Chavan
Sachin Chavan

Reputation: 5614

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;
        }
    }
}

Upvotes: 1

Deepak
Deepak

Reputation: 11

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
}

Upvotes: 1

user127835
user127835

Reputation: 216

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

Upvotes: 20

MrTelly
MrTelly

Reputation: 14865

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;
}

Upvotes: 2

Vilx-
Vilx-

Reputation: 106920

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?

Upvotes: 0

Anton Gogolev
Anton Gogolev

Reputation: 115751

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

Upvotes: 0

Anton Gogolev
Anton Gogolev

Reputation: 115751

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?

Upvotes: 0

Related Questions