Using NHibernate with Multiple Databases

This article describes using NHibernate with multiple databases concurrently.

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.

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:

<?xmlversion="1.0"?><configuration><configSections><sectionname="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/><sessionFactoryname="myProjectDb"factoryConfigPath="C:\MyProject.Web\Config\MyProjectDb.config"isTransactional="true"/><sessionFactoryname="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.

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.

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:

NHibernateSessionManager will use this path as a key in a hashtable to look for an already created session factory, and

NHibernateSessionManager will use the related config file to initialize a new session factory, if one was not found.

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.

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.

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.

I need at some point to load data from a "dbOne session" a save it to the "dbTwo Session".
the copy is working, so I can persist data loaded from the sessionOne,
but it looks the sessionOne got corrupted since I'm getting errors on sessionOne.Flush(),
I guess because the objects saved to sessionTwo are still connected to sessionOne.

Anyone tried to do something like this with success?
Basically I need a way to replicate some data from sessionOne to sessionTwo.

THEN I realized we needed to hit 3 DBs at once (legacy mess). Replication wasn't an option and neither was linked servers. So I found your article based on searching for multiple db nHibernate practices. What strikes me most is that nHibernate doesn't seem to consider the possibility that an application has to hit multiple databases, and seemingly has no multiple DB functionality built in.

So, I'm trying to get my head around your article and the bootstrapper article above to a degree where I can merge the two. Yours seems to have a lot of similarity to bootstrapper design (best practices, I suspect).

So I have two questions for you.

1) Are you aware of nHibernate 2 making ANY of this any easier?
2) Your code requires passing of the config file path as a differentiator between dbs. Would it not be more friendly to pass and use the name attribute of the config entry? Is there an example project that consumes this? Somewhere we could see what it's like to work with these objects in a multiple DB environment?

I would recommend checking out how multiple database support is included within S#arp Architecture (http://wiki.sharparchitecture.net/[^]). There is no "built in" mechanism for communicating with multiple databases in NHibernate but a framework such as S#arp shows how it can be done with the latest version.

I created a separate project/assembly for this set of classes, except for the DAOs.
I referenced this external project/assembly from the project/assembly where I have my model.
Config files are being loaded fine until <mapping assembly="..." /> is reached.
I am so sorry to bother anybody with something that should be easy to solve, but I don't know how to fully qualify the Assembly holding my model so that it can be reached. Any help? Thanks in advance.

Hi, no problem. The assembly mappint should simply be the assebly which contents the HBMs. So if your HBMs are in MyProject.Core, then it should <mapping assembly="MyProject.Core" />. If your HBMs are in a project just called MyProject, then it should be &lt;mapping assembly="MyProject" /&gt;. Give it a try and let me know if that works for you.

I have a test project trying to use the framework provided in the NHibernate Best Practices 1.2 article. This project is attempting to have a domain model where persistent classes are coming from both databases and are part of the same namespace. The goal is to not care where any of the data lives but to just build a rich domain layer.

If I create an object from each database on there own there is not an issue and the objects are hydrated properly from each database. If one object references the other then an exception is thrown on the referenced object from the other DB. Enclosed are the exceptions being thrown.

The way I've addressed this situation is to have a factory object which loads and combines the objects together. So the factory would load Support and then it would load Production, and then it would attach Production to the Support object and Support's mapping would exclude any references to Production. Since there haven't been many times that I've needed this, I haven't pursued the lazy loading from one DB to another. Do you feel this might be an adequate solution for you as well? It forces you to make some degree of DB faciliation in your domain, but not too much if it's an isolated case.

You may also want to try the NHibernate forums to see if others have had luck with this.

As long as the DBs are on the same SQL server, then you should be able to change the DB name for each mapping file since they can share the same session factory. (I haven't actually tried this, but that's my understanding.) If they're on separate servers, then they'll need separate session factories.

With respect to the factory, you'll still be able to work with POCO's; simply pass IObject1Dao and IObject2Dao to the factory's constructor. In this way, you can unit test the factory with mocked DAOs and inject its DAO dependencies from your controller/presenter/service-layer/code-behind.

Item has already been added. Key in dictionary: 'sessionFactoryConfigPath' Key being added: 'sessionFactoryConfigPath'
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Line 37: // Supplying "XmlInterpreter" as the parameter tells Windsor
Line 38: // to look at web.config for any necessary configuration.
Line 39: windsorContainer = new WindsorContainer(new XmlInterpreter());
Line 40: }
Line 41:

I think the problem is the CastleComponents.config. Do you know what is the problem?
Do you have multiple database example to show how to configure?
How can I dynamically pass "sessionFactoryConfigPath"?

2. the ListAuthors.aspx(my new page to access pubs.dbo.authors table) works, but ListCustomers.aspx (accwssing northwind.dbo.Customers table) got below error message, it looks like that it takes the last component: PubsNHibernate.config, do you know why?

Invalid object name 'pubs.dbo.Customers'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

I am working with 2 databases in a project. One is a legacy database and the other is a new database where we want to store only some data from the other.

I am using your NHibernateSessionManager and it works fine with both databases, my question is how can I have different mapping files that map the same class but to the 2 different databases. Is it possible? For example I have the class Cat that in the legacy database has an id, name, color, etc and in the new database only has id and name. I want to use the same Cat class being able to, according to the current session, map to the new or the old database.

I tried adding the mapping property to each session-factory config file like in the NHibernate documentation:
<mapping resource="Core.Cat.hbm.xml" assembly="Core.Cat" />

I'm a beginner to NHibernate. But i think it's not possible to use the same class for 2 databases. I recommend to create 2 separate assemblies with classes - 1st assemlby for the first, 2nd for the second database. The assemblies may have the "same" classes.

If you want to copy Users from DB1 to DB2, you'll have to:
1) get DB1.User[]
2) then create new DS2.User[] classes
3) copy content from DB1.User[] to DB2.User[]
4) persist DB2.User[]

I don't think it's possible to do exactly what you're trying to do. Alternatively, you could create a public Cat and a protected CatDetail objects that would each have an HBM to different databases. You could then include CatDetail as a many-to-one relationship from Cat and expose CatDetail properties from Cat so that other classes could work with Cat, not knowing the CatDetail is behind the scenes.

Since the previous poster didn't include the modified code for using the "name" rather than the full path of the session factory configuration, I'll post what I did, plus I use annotated assemblies, so I added that to the configuration enhancements (see new util code at bottom).

SessionFactoryElementusing System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;
using Configuration_StringValidator = System.Configuration.StringValidator;

NHibernateSessionManagerusing System;
using System.Collections;
using System.IO;
using System.Web.Caching;
using System.Runtime.Remoting.Messaging;
using System.Web;

using NHibernate;
using NHibernate.Cache;
using NHibernate.Cfg;

using [x].Utilities;
using System.Reflection;
using NHibernate.Mapping.Attributes;

namespace [x].NHibernate
{
///
/// 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.
///
public sealed class NHibernateSessionManager
{
#region Thread-safe, lazy Singleton

///
/// This is a thread-safe, lazy singleton. See http://www.yoda.arachsys.com/csharp/singleton.html
/// for more details about its implementation.
///
public static NHibernateSessionManager Instance
{
get
{
return Nested.NHibernateSessionManager;
}
}

///
/// This method attempts to find a session factory in the
/// 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.
///
/// Path location of the factory config
private ISessionFactory GetSessionFactoryFor(string sessionName)
{

if (string.IsNullOrEmpty(sessionName))
throw new ArgumentNullException("sessionFactoryConfigPath may not be null nor empty");

if (!File.Exists(path))
// It would be more appropriate to throw a more specific exception than ApplicationException
throw new ApplicationException(
"The config file at '" + sessionName + "' could not be found");

///
/// 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.
///
private Hashtable contextTransactions
{
get
{
if (CallContext.GetData("CONTEXT_TRANSACTIONS") == null)
{
CallContext.SetData("CONTEXT_TRANSACTIONS", new Hashtable());
}

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

///
/// 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.
///
private Hashtable contextSessions
{
get
{
if (CallContext.GetData("CONTEXT_SESSIONS") == null)
{
CallContext.SetData("CONTEXT_SESSIONS", new Hashtable());
}

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

NHibernateConfigUtilusing System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Xml;
using System.IO;
using NHibernate;
using NHibernate.Cfg;
using NHibernate.Mapping;
using NHibernate.Mapping.Attributes;

I'm a newbie in using NHibernate. Your articles impressed me! I'd like to move some projects and a team to use NHibernate, but the only way is to persuade the leaders and show them a way that even multiple DB handling (even with the same DB structure) is possible and easy.

Billy, I'm wondering if you've put any thought into how this solution could be applied in an application that needs to have web transactional access to the business layer, as well as winforms access. I have a back end that is exposed via a web service, and calls to the webservice cause the session to be configured and the transaction to be started, using the HTTPModule approach. I would also like for a windows service to be able to make transactional calls to the same business layer methods. I've been thinking that the codes that is in the HttpModule that is used to configure the sessions (and in my case add an interceptor etc.) could be pulled out into a separate class/method for NHibernate configuration, which can then be called from either the HttpModule, or another application (whether winforms/web) to set things up and begin a transaction. A this stage I am only looking at windows service integration, for use by batch jobs etc. I was just curious as to your thoughts on this.

I have been looking over this article as well as the approach mentioned by Hector Cruz in the Hibernate forums thread you linked to from here...

You mentioned in that thread...
"Ah, I now see what you're saying. The exact same HBM files need to work with different databases. In that case, how about programmatically set the DB schema before the session is created for the given HTTP request? In this way, you'll only need one session per HTTP request and it'll be "tuned" to the proper database. "

I think I have found a way to do this and it only requires a (very) small modification to your original article "NHibernate Best Practices with ASP.NET, Generics, and Unit Tests" ...

I don't think you could have described a better approach. Sorry, I neglected to mention that you could have a single transaction across multiple databases on the same server. I haven't tested it out myself, but it seems like it should work. Please let me know your experiences either way.

That's correct, you cannot have a single transaction across multiple DBs. To get close, you may commit a second transaction after a first one commits successfully. But if the second one fails, there's no way to then rollback the first one.

With respect to WinForms, I suggest keeping your transactions as short as possible although you may keep a session around for a little bit. It really depends on the setup. If there will be one database per user, then it doesn't matter too much. If you have a single database being hit by many users, then you'll want to keep your sessions short as well. But remember, once you close the session, you will no longer be able to lazily load collections until you open a new session and re-attach your parent object to the session.

If you're not using transactions, then you can simply close the session; otherwise, you'll want to commit the transaction and then close the session.

Does anyone knows if this approach supports transactions between different DBs? Let's suppose that I have entity A wich contains a collections of entities B. Entity A is mapped to database A and entity B is mapped to database B (I don't even know if this is posible!). Anyway, suppose that I drop entity A and B entities begin to drop on cascade, until a B entity fails. How the rollback will be??

So, does anyone knows if this approach supports transactions between different DBs?

I don't think you'd have a problem having a relationship between objects in different databases. The problem is that you won't be able to have a single transaction if the databases are on different servers since there can only be one transaction per session factory. But...you may be able to maintain a single transaction if both databases are on the same SQL server. I'm speculating here, but it might work since you can reference multiple databases on the same SQL server using the same session factory. Take a look at Hector Cruz's entry on the NHibernate discussion about this topic: http://forum.hibernate.org/viewtopic.php?t=962161&highlight=multiple+databases[^].

I'd be interested in knowing what you decide to do and how it works out.

if (!File.Exists(path))
// 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");

I put in the test for the HttpContext, so the code will run in a non-web application. It will then search the config file in the current path, depending on your application that may or may not be what you want.