Mapping entities to multiple databases with NHibernate

The legacy application I'm currently replacing features multiple
databases for some insane reason. Luckily it's quite easy to get
NHibernate to do joins across databases so long as they are on the same
server. The technique is detailed by Hector Cruz in
this thread on the NHibernate forum. The trick is to specify the schema you are
addressing in each mapping file. Because the schema name simply becomes
a table prefix, you can also use it to specify cross database joins. So
long as you follow good practice and have one mapping file per entity,
it means that, in theory, each entity could be persisted to a different
database. I've put together a little project to show this working using
Northwind. You can download the code here:

http://static.mikehadlow.com/Mike.NHibernate.Multiple.zip

I took a backup of Northwind and then restored it to a new database
so that I had a Northwind and a Northwind2. I'm going to get the Product
entity from the Products table on Northwind and the Supplier from the
Suppliers table in Northwind2. The great thing is that you only need a
single connection string pointing to one database (in my case the
original Northwind).

Here's the NHibernate configuration:

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <configSections>

    <section name="hibernate-configuration" type="NHibernate.Cfg.ConfigurationSectionHandler,NHibernate" />

  </configSections>

  <hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">

    <session-factory>

      <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>

      <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>

      <property name="connection.connection_string">Data Source=.\sqlexpress;Initial Catalog=Northwind;Integrated Security=True</property>

      <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>

      <property name="show_sql">true</property>

      <mapping assembly="Mike.NHibernate.Multiple"/>

    </session-factory>

  </hibernate-configuration>

</configuration>

Nothing special here. I've just nominated the original Northwind
database to be my initial catalogue. Next I've got two entities,
Product and Supplier:

namespace Mike.NHibernate.Multiple

{

    public class Product : Entity

    {

        public virtual string ProductName { get; set; }

        public virtual Supplier Supplier { get; set; }

    }

}

namespace Mike.NHibernate.Multiple

{

    public class Supplier : Entity

    {

        public virtual string CompanyName { get; set; }

    }

}

Once again pure persistence ignorance. You don't have to do anything
special with your entities. Now, here's the trick: The mapping file for
the Product entity specifies the schema as Northwind.dbo:

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="Northwind.dbo" >

  <class name="Mike.NHibernate.Multiple.Product, Mike.NHibernate.Multiple" table="Products">

    <id name="Id" column="ProductID" type="Int32">

      <generator class="identity" />

    </id>

    <property name="ProductName" />

    <many-to-one name="Supplier" class="Mike.NHibernate.Multiple.Supplier, Mike.NHibernate.Multiple" column="SupplierID" />

  </class>

</hibernate-mapping>

While the Supplier mapping file specifies Northwind2.dbo:

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" schema="Northwind2.dbo" >

  <class name="Mike.NHibernate.Multiple.Supplier, Mike.NHibernate.Multiple" table="Suppliers">

    <id name="Id" column="SupplierID" type="Int32">

      <generator class="identity" />

    </id>

    <property name="CompanyName" />

  </class>

</hibernate-mapping>

Now, hey-presto! When I run this little console program to retrieve a
Product from NHibernate, I get an object graph back with Product
entities from Northwind and Supplier entities from Northwind2.

using System;

using NHibernate.Cfg;

namespace Mike.NHibernate.Multiple

{

    class Program

    {

        static void Main()

        {

            var configuration = new Configuration();

            configuration.Configure();

            var sessionFactory = configuration.BuildSessionFactory();

            var session = sessionFactory.OpenSession();

            var product = session.Load<Product>(1);

            Console.WriteLine("Product: {0}, Supplier: {1}", product.ProductName, product.Supplier.CompanyName);

        }

    }

}

NHibernate generates this SQL:

NHibernate:

SELECT

 product0_.ProductID as ProductID0_0_,

 product0_.ProductName as ProductN2_0_0_,

 product0_.SupplierID as SupplierID0_0_

FROM Northwind.dbo.Products product0_

WHERE product0_.ProductID=@p0; @p0 = '1'

NHibernate:

SELECT

 supplier0_.SupplierID as SupplierID1_0_,

 supplier0_.CompanyName as CompanyN2_1_0_

FROM Northwind2.dbo.Suppliers supplier0_

WHERE supplier0_.SupplierID=@p0; @p0 = '1'

As you can see the Product was retrieved from Northwind and the
Supplier from Northwind2. It's similar to when you do cross database
joins in a stored procedure. The stored procedure has to live in a
particular database, but because each table gets prefixed with it's
database name the DBMS simply looks up the table in the referenced
database.

Note that this trick is simply to deal with a legacy situation that I
can't do much about. You really don't want to architect a system like
this from scratch.

Courtesy :http://mikehadlow.blogspot.com/2008/10/mapping-entities-to-multiple-databases.html