NHibernate and multiple databases

Question

I'm trying to solve pretty easy problem. I want to establish connection to 2 totally different databases ( but both mysql ). Now I tried to solve this by creating multiple config files and then creating multiple sessions. Everything works until I reached relations.

I have 2 tables in 2 databases:

db1 - News

db2 - News_Authors

I added News to db1 config and News_Authors to db2 config. When I try to build simple one-to-one relation error, I receive:

An association from the table songs refers to an unmapped class: db1.News_Authors

News.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="project.News, project" table="news" lazy="false">
    <id name="id" column="id" type="integer" length="11">
      <generator class="native" />
    </id>
    <property name="title" type="String" length="255" />
    <property name="authorid" type="integer" length="5" />

    <one-to-one name="NewsAuthor" cascade="all-delete-orphan" lazy="proxy" column="authorid" unique="true" />
  </class>     
</hibernate-mapping>

News_Authors.hbm.xml

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2">
  <class name="project.News_Authors, project" table="news_authors" lazy="false">
    <id name="id" column="id" type="integer" length="11">
      <generator class="native" />
    </id>
    <property name="name" type="String" length="255" />
  </class>     
</hibernate-mapping>

config I added this to enable mappings. Now If I set both in one config files, everything works...

<mapping resource="project.News.hbm.xml" assembly="project" />

So how could I during creating of session also "notify" nhibernate that I have multiple sessions? Or should I pick totally another approach?

Answer 1

What you are after is not multiple sessions but multiple session factories. See this for more details.

The key here is that you don't have to initialize your session factory through config file - you can do it programatically. And it's just a step to have two session factories.

Answer 2

One other possible solution would be to create views in one of the sql server databases to reference the tables in the other. Views map just like tables and its easy to do a view that returns something like:

select * from db2.News_Authors

in the db1 database.

this way you would only need a single .hbm.xml file that maps to one of the two databases.

Hope this helps,

-Max