Using NHibernate with Multiple Databases

Introduction

A previous CodeProject article described using NHibernate with ASP.NET; it offered guidelines for communicating with a single database. But, it is sometimes necessary to communicate with multiple databases concurrently. For NHibernate to do this, a session factory needs to exist for each database that you will be communicating with. But, as is often the case with multiple databases, some of the databases are rarely used. So, it may be a good idea to not create session factories until they're actually needed. This article picks up where the previous NHibernate with ASP.NET article left off, and describes the implementation details of this simple-sounding approach. Although the previous article focused on ASP.NET, the below suggestion is supported in both ASP.NET and Windows Forms.

A thank you goes out to Larry Silverman whose collaboration assisted in the development of this approach.

Multiple Database Configuration

The first thing to do when working with multiple databases is to configure proper communications. Create a separate config file for each database, put them all into a central config folder, and then reference them from the web/app.config.

Sample DB Config File

As an example, the following config file could be named ~/config/MyProjectDb.config.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.0" >
    <session-factory name="myProjectDb">
        <property name="hibernate.connection.provider">
          NHibernate.Connection.DriverConnectionProvider</property>
        <property name="hibernate.dialect">
          NHibernate.Dialect.MsSql2000Dialect</property>
        <property name="hibernate.connection.driver_class">
          NHibernate.Driver.SqlClientDriver</property>
        <property name="hibernate.connection.connection_string">
            Data Source=MySqlServer;Database=MyDb;
            User ID=username;Password=password;</property>
        <property name="hibernate.connection.isolation">
           ReadCommitted</property>
        <property name="hibernate.default_schema">MyDb.dbo</property>
        <!-- Assembly containing the embedded HBM mapping files -->
        <mapping assembly="MyProject.Core" />
    </session-factory>
</hibernate-configuration>

Web.config / App.config Settings

Now that the database configuration has been defined in one or more config files, the NHibernate session manager needs to be informed about which session factories are available for use and where their respective config files reside. A solution that will provide a great amount of reuse is to create a custom config file reader. The web/app.config then states where each config file lives, as follows:

<?xml version="1.0"?>
<configuration>
    <configSections>
        <section name="nhibernateSettings"
           type="SomeReusableProject.OpenSessionInViewSection, 
                 SomeReusableProject" />
    </configSections>
    <nhibernateSettings>
        <!-- List every session factory that will be needed; 
             transaction management and closing sessions 
        will be managed with an open-session-in-view HTTP module -->
        <sessionFactories>
            <clearFactories />
            <sessionFactory name="myProjectDb" 
                factoryConfigPath="C:\MyProject.Web\Config\MyProjectDb.config" 
                isTransactional="true" />
            <sessionFactory name="otherDb" 
                factoryConfigPath="C:\MyProject.Web\Config\JdeNHibernate.config" />
        </sessionFactories>
    </nhibernateSettings>
...

The above configuration is not an "out of the box" NHibernate settings section. Three classes must be written to parse this custom configuration information: OpenSessionInViewSection will be the overall handler for parsing the config file, SessionFactoriesCollection will serve as the container for the listing of "sessionFactory" elements, and SessionFactoryElement will map the individual session factory settings.

OpenSessionInViewSection

namespace SomeReusableProject
{
    /// <summary>
    /// Encapsulates a section of Web/App.config
    /// to declare which session factories are to be created.
    /// Kudos go out to 
    /// http://msdn2.microsoft.com/en-us/library/
    ///    system.configuration.configurationcollectionattribute.aspx
    /// for this technique - it was by far the best overview of the subject.
    /// </summary>

    public class OpenSessionInViewSection : ConfigurationSection
    {
        [ConfigurationProperty("sessionFactories", IsDefaultCollection = false)]
        [ConfigurationCollection(typeof(SessionFactoriesCollection), 
            AddItemName="sessionFactory", 
            ClearItemsName="clearFactories")]
        public SessionFactoriesCollection SessionFactories {
            get {
                SessionFactoriesCollection sessionFactoriesCollection =
                    (SessionFactoriesCollection) base["sessionFactories"];
                return sessionFactoriesCollection;
            }
        }
    }
}

SessionFactoriesCollection

namespace SomeReusableProject
{
    [ConfigurationCollection(typeof(SessionFactoryElement))]
    public sealed class SessionFactoriesCollection : 
                        ConfigurationElementCollection
    {
        public SessionFactoriesCollection() {
            SessionFactoryElement sessionFactory = 
               (SessionFactoryElement) CreateNewElement();
            Add(sessionFactory);
        }

        public override ConfigurationElementCollectionType CollectionType {
            get {
                return ConfigurationElementCollectionType.AddRemoveClearMap;
            }
        }

        protected override ConfigurationElement CreateNewElement() {
            return new SessionFactoryElement();
        }

        protected override object GetElementKey(ConfigurationElement element) {
            return ((SessionFactoryElement)element).Name;
        }
        
        public SessionFactoryElement this[int index] {
            get {
                return (SessionFactoryElement) BaseGet(index);
            }
            set {
                if (BaseGet(index) != null) {
                    BaseRemoveAt(index);
                }

                BaseAdd(index, value);
            }
        }
        
        new public SessionFactoryElement this[string name] {
            get {
                return (SessionFactoryElement) BaseGet(name);
            }
        }
        
        public int IndexOf(SessionFactoryElement sessionFactory) {
            return BaseIndexOf(sessionFactory);
        }
        
        public void Add(SessionFactoryElement sessionFactory) {
            BaseAdd(sessionFactory);
        }

        protected override void BaseAdd(ConfigurationElement element) {
            BaseAdd(element, false);
        }
        
        public void Remove(SessionFactoryElement sessionFactory) {
            if (BaseIndexOf(sessionFactory) >= 0) {
                BaseRemove(sessionFactory.Name);
            }
        }
        
        public void RemoveAt(int index) {
            BaseRemoveAt(index);
        }
        
        public void Remove(string name) {
            BaseRemove(name);
        }
        
        public void Clear() {
            BaseClear();
        }
    }
}

SessionFactoryElement

Note that the configuration element allows an optional setting of "isTransactional" to be provided. By default, this setting is false. If it's set to true, then all calls to a DB during a single HTTP request will be contained within a single transaction for the given session factory. To illustrate this, assume two DBs exist: MyDb and OtherDb. The web/app.config setting for MyDb declares "isTransactional" to be true. Furthermore, assume no transactional setting is provided for OtherDb; i.e., "isTransactional" remains false. As will be described later, since MyDb is to be transactional, a transaction will be begun at the start of each HTTP request, thus encapsulating every call to MyDb for the life of the HTTP request. At the end of the HTTP request, the single transaction for calls to MyDb will be committed. On the flipside, since OtherDb won't be transactional, any failed call to OtherDb will not cause a rollback of any other call to OtherDb during the life of each HTTP request.

using Configuration_StringValidator=System.Configuration.StringValidator;

namespace SomeReusableProject
{
    public class SessionFactoryElement : ConfigurationElement
    {
        public SessionFactoryElement() {}
        
        public SessionFactoryElement(string name, string configPath) {
            Name = name;
            FactoryConfigPath = configPath;
        }
        
        [ConfigurationProperty("name", IsRequired = true, 
             IsKey=true, DefaultValue="Not Supplied")]
        public string Name {
            get { return (string) this["name"]; }
            set { this["name"] = value; }
        }

        [ConfigurationProperty("factoryConfigPath", IsRequired = true, 
                 DefaultValue = "Not Supplied")]
        public string FactoryConfigPath {
            get { return (string)this["factoryConfigPath"]; }
            set { this["factoryConfigPath"] = value; }
        }

        [ConfigurationProperty("isTransactional", 
                      IsRequired = false, DefaultValue = false)]
        public bool IsTransactional {
            get { return (bool)this["isTransactional"]; }
            set { this["isTransactional"] = value; }
        }
    }
}

Open-Session-in-View Revisited

Now that the configuration files and config readers are in place, an HTTP module can be enlisted to read the configuration settings and begin transactions, accordingly. (Obviously, this HTTP module is ASP.NET specific, and does not apply to .NET WinForms - but the underlying concepts are the same.) In the previous NHibernate with ASP.NET article, it was assumed that Open-Session-in-View would always utilize a transaction for the life of the HTTP request. The modified NHibernateSessionModule.cs below begins a transaction only when "isTransactional" is set to true in the web.config file. As stated previously, this HTTP module only begins one transaction per HTTP request per session factory when isTransactional="true" for the given session factory. Lastly, each transaction is then committed at the end of the HTTP request.

One other interesting item to note in the code is that it passes the FactoryConfigPath, pulled from web.config, to the NHibernateSessionManager, for two purposes:

1. NHibernateSessionManager will use this path as a key in a hashtable to look for an already created session factory, and
2. NHibernateSessionManager will use the related config file to initialize a new session factory, if one was not found.

NHibernateSessionModule

namespace SomeReusableProject
{
    /// <summary>
    /// Implements the Open-Session-In-View pattern
    /// using <see cref="NHibernateSessionManager" />.
    /// Inspiration for this class came from Ed Courtenay at 
    /// http://sourceforge.net/forum/message.php?msg_id=2847509.
    /// </summary>

    public class NHibernateSessionModule : IHttpModule
    {
        public void Init(HttpApplication context) {
            context.BeginRequest += new EventHandler(BeginTransaction);
            context.EndRequest += new EventHandler(CommitAndCloseSession);
        }

        public void Dispose() { }

        /// <summary>
        /// Opens a session within a transaction
        /// at the beginning of the HTTP request. Note that 
        /// it ONLY begins transactions for those designated as being transactional.
        /// </summary>

        private void BeginTransaction(object sender, EventArgs e) {
            OpenSessionInViewSection openSessionInViewSection = 
                                       GetOpenSessionInViewSection();

            foreach (SessionFactoryElement sessionFactorySettings in 
                         openSessionInViewSection.SessionFactories) {
                if (sessionFactorySettings.IsTransactional) {
                    NHibernateSessionManager.Instance.BeginTransactionOn(
                                sessionFactorySettings.FactoryConfigPath);
                }
            }
        }

        /// <summary>
        /// Commits and closes the NHibernate session provided
        /// by the supplied <see cref="NHibernateSessionManager"/>.
        /// Assumes a transaction was begun at the beginning
        /// of the request; but a transaction or session does
        /// not *have* to be opened for this to operate successfully.
        /// </summary>

        private void CommitAndCloseSession(object sender, EventArgs e) {
            OpenSessionInViewSection openSessionInViewSection = 
                                               GetOpenSessionInViewSection();
            
            try {
                // Commit every session factory that's holding a transactional session

                foreach (SessionFactoryElement sessionFactorySettings in 
                         openSessionInViewSection.SessionFactories) {
                    if (sessionFactorySettings.IsTransactional) {
                        NHibernateSessionManager.Instance.CommitTransactionOn(
                               sessionFactorySettings.FactoryConfigPath);
                    }
                }
            }
            finally {
                // No matter what happens,
                // make sure all the sessions get closed

                foreach (SessionFactoryElement sessionFactorySettings in 
                         openSessionInViewSection.SessionFactories) {
                    NHibernateSessionManager.Instance.CloseSessionOn(
                             sessionFactorySettings.FactoryConfigPath);
                }
            }
        }

        private OpenSessionInViewSection GetOpenSessionInViewSection() {
            OpenSessionInViewSection openSessionInViewSection = ConfigurationManager
                .GetSection("nhibernateSettings") as OpenSessionInViewSection;

            if (openSessionInViewSection == null)
        throw new ConfigurationErrorsException("The nhibernateSettings " + 
                  "section was not found by ConfigurationManager.");

            return openSessionInViewSection;
        }
    }
}

Managing Multiple Session Factories

A remaining task is to modify the NHibernateSessionManager class from the original NHibernate with ASP.NET article so that it now manages multiple session factories. The general idea is that each session factory is stored in a hashtable using its associated config file path as the key. As described previously, this makes it easy to locate the session factory, or create a new one, if not found.

Note that even though the following class uses HttpRuntime.Cache for storing and retrieving active session factories, it will still work in a .NET WinForms environment.

NHibernateSessionManager

namespace SomeReusableProject
{
    /// <summary>
    /// Handles creation and management of sessions and transactions.
    /// It is a singleton because building
    /// the initial session factory is very expensive.
    /// Inspiration for this class came from Chapter 8 of Hibernate in Action
    /// by Bauer and King. Although it is a sealed singleton you can use TypeMock
    /// (http://www.typemock.com) for more flexible testing.
    /// </summary>

    public sealed class NHibernateSessionManager
    {
        #region Thread-safe, lazy Singleton

        /// <summary>
        /// This is a thread-safe, lazy singleton.
        /// See http://www.yoda.arachsys.com/csharp/singleton.html
        /// for more details about its implementation.
        /// </summary>

        public static NHibernateSessionManager Instance {
            get {
                return Nested.NHibernateSessionManager;
            }
        }

        /// <summary>
        /// Private constructor to enforce singleton
        /// </summary>

        private NHibernateSessionManager() {}

        /// <summary>
        /// Assists with ensuring thread-safe, lazy singleton
        /// </summary>

        private class Nested
        {
            static Nested() { }
            internal static readonly NHibernateSessionManager 
               NHibernateSessionManager = new NHibernateSessionManager();
        }

        #endregion

        /// <summary>
        /// This method attempts to find a session factory
        /// in the <see cref="HttpRuntime.Cache" /> 
        /// via its config file path; if it can't be
        /// found it creates a new session factory and adds
        /// it the cache. Note that even though this uses HttpRuntime.Cache,
        /// it should still work in Windows applications; see
        /// http://www.codeproject.com/csharp/cacheinwinformapps.asp
        /// for an examination of this.
        /// </summary>
        /// <param name="sessionFactoryConfigPath">Path location
        /// of the factory config</param>

        private ISessionFactory GetSessionFactoryFor(string sessionFactoryConfigPath) {
            if (string.IsNullOrEmpty(sessionFactoryConfigPath))
                throw new ArgumentNullException("sessionFactoryConfigPath" + 
                          " may not be null nor empty");
            
            //  Attempt to retrieve a cached SessionFactory from the HttpRuntime's cache.
            ISessionFactory sessionFactory = 
              (ISessionFactory)HttpRuntime.Cache.Get(sessionFactoryConfigPath);

            //  Failed to find a cached SessionFactory so make a new one.
            if (sessionFactory == null) {
                if (! File.Exists(sessionFactoryConfigPath))
                    // It would be more appropriate to throw
                    // a more specific exception than ApplicationException

                    throw new ApplicationException(
                        "The config file at '" + sessionFactoryConfigPath + 
                        "' could not be found");
                
                NHibernate.Cfg.Configuration cfg = new NHibernate.Cfg.Configuration();
                cfg.Configure(sessionFactoryConfigPath);

                //  Now that we have our Configuration object, create a new SessionFactory

                sessionFactory = cfg.BuildSessionFactory();

                if (sessionFactory == null) {
                    throw new InvalidOperationException(
                      "cfg.BuildSessionFactory() returned null.");
                }

                HttpRuntime.Cache.Add(sessionFactoryConfigPath, 
                            sessionFactory, null, DateTime.Now.AddDays(7),
                    TimeSpan.Zero, CacheItemPriority.High, null);
            }

            return sessionFactory;
        }

        public void RegisterInterceptorOn(string sessionFactoryConfigPath, 
                                          IInterceptor interceptor) {
            ISession session = (ISession)contextSessions[sessionFactoryConfigPath];

            if (session != null && session.IsOpen) {
                throw new CacheException("You cannot register " + 
                      "an interceptor once a session has already been opened");
            }

            GetSessionFrom(sessionFactoryConfigPath, interceptor);
        }

        public ISession GetSessionFrom(string sessionFactoryConfigPath) {
            return GetSessionFrom(sessionFactoryConfigPath, null);
        }

        private ISession GetSessionFrom(string sessionFactoryConfigPath, 
                                        IInterceptor interceptor) {
            ISession session = (ISession)contextSessions[sessionFactoryConfigPath];

            if (session == null) {
                if (interceptor != null) {
                    session = GetSessionFactoryFor(
                       sessionFactoryConfigPath).OpenSession(interceptor);
                }
                else {
                    session = 
                     GetSessionFactoryFor(sessionFactoryConfigPath).OpenSession();
                }

                contextSessions[sessionFactoryConfigPath] = session;
            }
            
            if (session == null) 
                // It would be more appropriate to throw
                // a more specific exception than ApplicationException

                throw new ApplicationException("session was null");
            
            return session;
        }

        public void CloseSessionOn(string sessionFactoryConfigPath) {
            ISession session = (ISession)contextSessions[sessionFactoryConfigPath];
            contextSessions.Remove(sessionFactoryConfigPath);

            if (session != null && session.IsOpen) {
                session.Close();
            }
        }

        public void BeginTransactionOn(string sessionFactoryConfigPath) {
            ITransaction transaction = 
              (ITransaction)contextTransactions[sessionFactoryConfigPath];

            if (transaction == null) {
                transaction = GetSessionFrom(sessionFactoryConfigPath).BeginTransaction();
                contextTransactions.Add(sessionFactoryConfigPath, transaction);
            }
        }

        public void CommitTransactionOn(string sessionFactoryConfigPath) {
            ITransaction transaction = 
              (ITransaction)contextTransactions[sessionFactoryConfigPath];

            try {
                if (transaction != null && !transaction.WasCommitted 
                                        && !transaction.WasRolledBack) {
                    transaction.Commit();
                    contextTransactions.Remove(sessionFactoryConfigPath);
                }
            }
            catch (HibernateException) {
                RollbackTransactionOn(sessionFactoryConfigPath);
                throw;
            }
        }

        public void RollbackTransactionOn(string sessionFactoryConfigPath) {
            ITransaction transaction = 
              (ITransaction) contextTransactions[sessionFactoryConfigPath];

            try {
                contextTransactions.Remove(sessionFactoryConfigPath);

                if (transaction != null && !transaction.WasCommitted 
                                        && !transaction.WasRolledBack) {
                    transaction.Rollback();
                }
            }
            finally {
                CloseSessionOn(sessionFactoryConfigPath);
            }
        }

        /// <summary>
        /// Since multiple databases may be in use, there may be one transaction per database 
        /// persisted at any one time. The easiest way to store them is via a hashtable
        /// with the key being tied to session factory.
        /// </summary>

        private Hashtable contextTransactions {
            get {
                if (CallContext.GetData("CONTEXT_TRANSACTIONS") == null) {
                    CallContext.SetData("CONTEXT_TRANSACTIONS", new Hashtable());
                }

                return (Hashtable)CallContext.GetData("CONTEXT_TRANSACTIONS");
            }
        }

        /// <summary>
        /// Since multiple databases may be in use, there may be one session per database 
        /// persisted at any one time. The easiest way to store them is via a hashtable
        /// with the key being tied to session factory.
        /// </summary>

        private Hashtable contextSessions {
            get {
                if (CallContext.GetData("CONTEXT_SESSIONS") == null) {
                    CallContext.SetData("CONTEXT_SESSIONS", new Hashtable());
                }
                
                return (Hashtable)CallContext.GetData("CONTEXT_SESSIONS");
            }
        }
    }
}

Using NHibernateSessionManager

Finally, the only other modification to the original NHibernate with ASP.NET article is to retrofit GenericNHibernateDao.cs to pass the session factory's config file path to the NHibernateSessionManager class for determining which session factory to use.

namespace SomeReusableProject
{
    public abstract class GenericNHibernateDAO<T, ID> : IGenericDAO<T, ID>
    {
        /// <param name="sessionFactoryConfigPath">Fully qualified
        /// path of the session factory's config file</param>

        public GenericNHibernateDAO(string sessionFactoryConfigPath) {
           if (string.IsNullOrEmpty(sessionFactoryConfigPath))
                throw new ArgumentNullException("sessionFactoryConfigPath " + 
                          "may not be null nor empty");

            SessionFactoryConfigPath = sessionFactoryConfigPath;
        }
        
        /// <summary>
        /// Exposes the ISession used within the DAO.
        /// </summary>

        private ISession session {
            get {
                return NHibernateSessionManager.Instance.GetSessionFrom(
                       SessionFactoryConfigPath);
            }
        }

        /// <summary>
        /// Loads an instance of type T from the DB based on its ID.
        /// </summary>

        public T GetById(ID id, bool shouldLock) {
            T entity;

            if (shouldLock) {
                entity = (T) session.Load(persitentType, id, LockMode.Upgrade);
            }
            else {
                entity = (T)session.Load(persitentType, id);
            }

            return entity;
        }

        protected readonly string SessionFactoryConfigPath;

        ...

Any concrete DAO that inherits from GenericNHibernateDao must then pass the session factory config path to the parent class' constructor; e.g., public UserDao(string sessionFactoryConfigPath) : base(sessionFactoryConfigPath) { }.

Summary

Communicating with multiple databases concurrently doesn't come up often, but when it does, NHibernate is still a fitting solution. The solution discussed above is only one possible approach, but provides a set of reusable classes for communicating with any number of databases, with easy to maintain config files.

As always, your experiences, feedback, and suggestions are most welcome.

Additional Resources

• NHibernate with ASP.NET.
• Communication with multiple DBs on the same server using a single session factory.

History

• 2006.07.17 - Initial posting.