NHibernate and dynamic(sorta) table names.

I have had the very bad fortune to be working with some very old database structures at the moment. Upgrading those structures is not possible, however there are times when it would be nice to map against them.  Recently I have been banging my head against a scenario where an entity may exist in one database at “X” location and in an entirely different database at “Y” location.  This works fine as long as this database is the only one that you are connecting and mapping too.  You can simply change the database name in the connection string and life is good.  However there are situations – mine in particular – where you have to map relations between your entities across databases, ouch.  In this situation a person needs to prefix the names of their table mappings with the database name as well.  This seems to work at least until you need to change the name of the database that you are connecting to.

So is this situation even possible with NHiberante(2.0.1GA)?  It turns out that yes it is, but first you have to get NHibernate to perform some gymnastics.  This seems to work fine for me but that is not a guarantee that your experience will be similar.  There is an additional caveat as well, that is that this only works for specifying the db names before you start an NH session factory.  Once you start the session the names are finalized and this method will not work to change them.   This method actually works by putting placholder values in your mapping files instead of actually database names.  What we need to do then is to strip out the placeholders and replace them with real db names when the NH session factory is being created.  We do this by looking through the configuration file that is generated by NH and replacing our values before the session factory is created.  In theory this would also work with table names as well as db names, but that is not something that I have tested with.

Lets start out with a very simple mapping.  Remember that we are not exactly sure of the database name that this will be residing in.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="MemX.Data.Entities" namespace="MemX.Data.Entities.RMS">
  <class name="Item" table="$RmsDB.dbo.Item" lazy="false">
    <id name="ID" column="ID" type="Int32">
      <generator class="native" />
    </id>
    <property name="Description" column="Description"/>
  </class>
</hibernate-mapping>

What you can see, when you look at the attribute containing the table name, is that we’ve put a placeholder there instead of the actual table name.

Now that we have a placeholder there instead of a database name we need a class that can help us to easily modify NH’s configuration.  Lets start out with a dictionary to contain our placholder to value mappings.

/// <summary>
/// Helps to map table symbols in the NH mappings to actual table/database names
/// </summary>
public class NHTableMappings
{
    /// <summary>
    /// Maps from the placeholder string to the real table/database name
    /// </summary>
    public IDictionary<string, string> Mappings { get; protected set; }
 
    /// <summary>
    /// Creates a set of table/database mappings
    /// </summary>
    public NHTableMappings()
    {
        Mappings = new Dictionary<string, string>();
    }
}

And to push those mappings into the NH configuration we would need to do something like this inside our NHTableMappings class.

/// <summary>
/// maps table symbols to actual table/database names
/// </summary>
/// <param name="configuration"></param>
/// <returns></returns>
public void MapTables(Configuration configuration)
{
    if (configuration == null) throw new ArgumentNullException("configuration");
 
    //get the tables using reflection
    FieldInfo tablesField = typeof (Configuration).GetField("tables", BindingFlags.Instance | BindingFlags.NonPublic);
    IDictionary<string, Table> tables = tablesField.GetValue(configuration) as IDictionary<string, Table>;
 
    if(tables == null)
        throw new InvalidOperationException("Couldn't retrieve table information from NHibernate configuration.");
 
    //look through NHTableMappings
    foreach (KeyValuePair<string, string> key_tableName in Mappings)
    {
        string tableKey = key_tableName.Key;
        string tableName = key_tableName.Value;
 
        IEnumerable<Table> tablesToChange = tables.Where(p => p.Key.Contains(tableKey)).Select(p => p.Value);
        foreach (Table table in tablesToChange)
        {
            table.Name = table.Name.Replace(tableKey, tableName);
        }
    }
}

It’s pretty simple really.  We use reflection to get the tables field from the NH configuration because it is not public.  We scan through the table names looking for keys that are in our mapping and we do the old switcheroo.  Once this has been done we can start up a session factory and our entity will now be mapped against the correct database.

//Build the NH configuration
Configuration nhConfiguration = new Configuration().Configure();
 
//add your mappings
NHTableMappings tableMappings = new NHTableMappings();
tableMappings.Mappings.Add("$RmsDB", "MyRmsDatabaseName");
 
//modify the NH configuration
tableMappings.MapTables(nhConfiguration);
 
//build your session factory
ISessionFactory sessionFactory = nhConfiguration.BuildSessionFactory();

I hope this helps someone who is trying solve the same problem that I was.

I’ve included some complete classes here in case you just want to copy and paste (plus some includes).

The main class.

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Reflection;
using System.Xml;
using log4net;
using NHibernate.Mapping;
using Configuration=NHibernate.Cfg.Configuration;
 
namespace MemX.Data
{
    /// <summary>
    /// Helps to map table symbols in the NH mappings to actual table/database names
    /// </summary>
    public class NHTableMappings
    {
        private static readonly ILog log = LogManager.GetLogger(typeof(NHTableMappings));
        public static readonly string ConfigSection = "nh-table-mappings";
        public static readonly string ConfigKey = "mapping";
        public static readonly string KeyAttribute = "table-key";
        public static readonly string NameAttribute = "table-name";
 
        /// <summary>
        /// Maps from the placeholder string to the real table/database name
        /// </summary>
        public IDictionary<string, string> Mappings { get; protected set; }
 
        /// <summary>
        /// Creates a set of table/database mappings
        /// </summary>
        public NHTableMappings()
        {
            Mappings = new Dictionary<string, string>();
        }
 
        /// <summary>
        /// Adds a mapping from a key to an actual name
        /// <para>Can be used for table names or DB names</para>
        /// </summary>
        /// <param name="tableKey"></param>
        /// <param name="tableName"></param>
        /// <param name="overwrite">Do we want to overwrite an existing value</param>
        public void AddMapping(string tableKey, string tableName, bool overwrite)
        {
            if (String.IsNullOrEmpty(tableKey)) throw new ArgumentNullException("tableKey");
            if (String.IsNullOrEmpty(tableName)) throw new ArgumentNullException("tableName");
 
            if (!Mappings.ContainsKey(tableKey))
                Mappings.Add(tableKey, tableName);
            else if(overwrite)
                Mappings[tableKey] = tableName;
        }
 
        /// <summary>
        /// maps table symbols to actual table/database names
        /// </summary>
        /// <param name="configuration"></param>
        /// <returns></returns>
        public void MapTables(Configuration configuration)
        {
            if (configuration == null) throw new ArgumentNullException("configuration");
 
            //get the tables using reflection
            FieldInfo tablesField = typeof (Configuration).GetField("tables", BindingFlags.Instance | BindingFlags.NonPublic);
            IDictionary<string, Table> tables = tablesField.GetValue(configuration) as IDictionary<string, Table>;
 
            if(tables == null)
                throw new InvalidOperationException("Couldn't retrieve table information from NHibernate configuration.");
 
            //look through NHTableMappings
            foreach (KeyValuePair<string, string> key_tableName in Mappings)
            {
                string tableKey = key_tableName.Key;
                string tableName = key_tableName.Value;
 
                IEnumerable<Table> tablesToChange = tables.Where(p => p.Key.Contains(tableKey)).Select(p => p.Value);
                foreach (Table table in tablesToChange)
                {
                    table.Name = table.Name.Replace(tableKey, tableName);
                }
            }
        }
 
        /// <summary>
        /// Reads the configuration from xml
        /// </summary>
        /// <param name="section"></param>
        /// <param name="overwrite">Do we want to overwrite existing configuration</param>
        public void ReadFromXml(XmlNode section, bool overwrite)
        {
            log.Debug("Reading table mappings from xml");
            if (section == null) throw new ArgumentNullException("section");
 
            if(section.LocalName != ConfigSection)
                throw new ConfigurationErrorsException(String.Format("Invalid section {0} in NHTableMappings configuration", section.LocalName));
 
            foreach (XmlNode node in section.ChildNodes)
            {
                if(node.LocalName != ConfigKey)
                    throw new ConfigurationErrorsException(String.Format("Invalid section {0} in NHTableMappings configuration", node.LocalName));
 
                string tableKey = node.Attributes[KeyAttribute].Value;
                string tableName = node.Attributes[NameAttribute].Value;
 
                AddMapping(tableKey, tableName, overwrite);
            }
        }
 
        /// <summary>
        /// Creates the mappings from an xml section
        /// </summary>
        /// <param name="section"></param>
        /// <param name="overwrite">Do we want to overwrite existing configuration</param>
        /// <returns></returns>
        public static NHTableMappings CreateFromXml(XmlNode section, bool overwrite)
        {
            NHTableMappings tableMappings = new NHTableMappings();
            tableMappings.ReadFromXml(section, overwrite);
 
            return tableMappings;
        }
 
        /// <summary>
        /// Creates the mappings from a config section
        /// </summary>
        /// <returns></returns>
        public static NHTableMappings CreateFromConfig()
        {
            NHTableMappings tableMappings = ConfigurationManager.GetSection(ConfigSection) as NHTableMappings;
            if(tableMappings == null)
                throw new ConfigurationErrorsException("Could not load table mappings confugration from app/web.config");
 
            return tableMappings;
        }
 
        /// <summary>
        /// Creates the mappings from a config section if it exists, or defaults to an empty mappings file
        /// </summary>
        /// <returns></returns>
        public static NHTableMappings CreateFromConfigOrDefault()
        {
            NHTableMappings mappings;
 
            try
            {
                mappings = CreateFromConfig();
            }
            catch (ConfigurationErrorsException)
            {
                mappings = new NHTableMappings();
            }
 
            return mappings;
        }
    }
}

A configuration section handler.

using System.Configuration;
using System.Xml;
 
namespace MemX.Data
{
    public class NHTableMappingsConfigurationSectionHandler : IConfigurationSectionHandler
    {
        #region IConfigurationSectionHandler Members
 
        public object Create(object parent, object configContext, XmlNode section)
        {
            return NHTableMappings.CreateFromXml(section, true);
        }
 
        #endregion
    }
}

The usage looks something like this.

<configuration>
  <configSections>
    <section name="nh-table-mappings" type="MemX.Data.NHTableMappingsConfigurationSectionHandler, MemX.Data"/>
  </configSections>
  <nh-table-mappings>
    <mapping table-key="$RmsDB" table-name="RMSCal3"/>
  </nh-table-mappings>
</configruation>

And even an extension method

    public static class NHTableMappingsExtension
    {
        public static Configuration MapTables(this Configuration configuration)
        {
            NHTableMappings tableMappings = NHTableMappings.CreateFromConfig();
 
            tableMappings.MapTables(configuration);
 
            return configuration;
        }
    }

Using the configuration and the extension it would looking something like this.

            ISessionFactory sessionFactory = new Configuration()
                .Configure()
                .MapTables()
                .BuildSessionFactory();

You could also include some default mappings and include them as a resource along with your mappings and only override when needed as well.

One Response to “NHibernate and dynamic(sorta) table names.”

  1. Veera Pallati Says:

    Excellent content. It’s quite usefull for nHibernate developers

Leave a Reply