I am wondering if NHibernate can handle hbm.xml conifguration which refer to different databases. e.g.

I have two main databases, Order and Customer. Both persist addressing information. Currently, I duplicate the Addressing information in each database. I would like to put all the addressing information in a seperate database and reference that database in the Order and Customer hbm.xml relations. Is this possible?

thanks,
craig

You can do this (in SQL Server, at least) as long as you fully qualify your table names in your hbm mappings.

so instead of mapping just "TableName", you'll have to map "DatabaseName.owner.TableName"

Example:

I tried this once before an could not get it to work. Do you know if this is directly supported by HIbernate and have you been successful at doing this?

thanks,
craig

Craig, if database-only tricks satisfy you, then you could create a view in database A that selects data from database B. Of course for NH it will be two different table, that could create some problems with caching.

Hello Andrew,

That is a very interesting approach. Since it appears that it is not directly supported by NHibernate, I will probably duplicate the tables. What have you done to accomodate common NHibernate models across different domains?

Thanks,
craig

On the weekend I've realized that this could be solved by means of custom type.

As i understood, you have two databases, two NH configurations and two set of sessions. From the NH point of view those two problem domains are completely different, but for your applications there is no such separation. Object from domain A references object from domain B and there is no problem with it. So on load your custom type for domain A will have to load object from session B. On save - save the ID of the B-object.

Hello Andrew,

I am not sure I understand how to apply your solution. Let me give you a more concrete example of what I am trying to acheive.

I have a database EOP_Order which contains Order models.
I have a database EOP_Customer which contains Customer models.

Both the EOP_Order and EOP_Customer models reference Address information.

I have an object model for Orders, Customers and Addresses. The Address domain object is reference in both the Order and Customer domain model.

I have nhibernate mappings for Order, Customer and Address. However, the Address table is duplicated in both the EOP_Order and EOP_Customer database. I would like to have another database EOP_Common which includes the common domain tables (Address being one) and have the EOP_Order and EOP_Customer reference that in the corresponding configurations. I was hoping not to do anything special or create custom types for this to work. I simply want the Address relationships in Order and Customer to reference a different database.

If your solution addresses this, could you explain it a little more.

Thanks again for your help,
craig

Yes, Craig, i think my solution is exactly about this. Though, it will require a bit of additional work from your side. I'm completely not sure that NH can natively support several databases.

So, what have to be done:
1. Create three different NH configurations each related to one of your bases - Order.Config, Customer.Config and Common.Config.

2. I assume that you will use something like thread static session provider (like the one that is described in my comments to ASP.NET topic in the wiki). It will allow you to access any of three of your active sessions from any place of your code.

3. Make a custom type, that will be responsible for resolving an object from Common domain (given it's id) and saving a reference to such object (by extracting it's id and placing into the right column). This type should implement IUserType interface. Actually, it's quite simple thing, there are plenty of examples in the NH sources. In this class you will use Common.Session to load objects.

4. Specify the custom type in the mapping of classes from Order and Customer domains using type attribute. Please note, that in those mappings you should use not <many-to-one> element, but just a <property>. Reasons are obvious, i think.

5. While saving objects you must ensure that referenced objects (in Common domain) are saved (and recieved an ID) before saving of referencing objects (in Order and Customer domains).

Hi Andrew,

Thanks for clarifying things. It makes sense, but is probably a little more than I had expected. Since it works fine as is, I will probably keep it duplicated until I can full understand any issues that may arise (like HQL queries)

craig

Andrew,

I do have one question concerning sessions in ASP.Net. Do you create a new Session for every ASP.Net request or do you use a single session? If the latter is true, can a single session accomodate multiple threads accessing it? I was always under the assumption that there is a one-one with session and connection and multiple concurrent updates need multiple sessions.

thanks,
craig

Yes, sessions are not thread safe so you need a separate session for each request. Fortunately, opening a session is not very expensive, so if you open a session at the beginning of the request and close at the end, everything will be ok. During the request handling you can Flush your session as often as you want.
I also find it useful to explicitely connect a session to the database in the places there it is necessary. It helps working with distributed transactions. Do not mix up connecting session to the database (Reconnect/Disconnect) and opening a session (Open/Close).

I'm routinely using tables from different databases in the same mapping file with SQL Server. Just put table="DataBaseName..TableName", -notice that the owner is missing as it is optional. Beware that you must grant permission on both databases for the login you use to connect to the sql server (or enable cross database ownership chaining but with this one I have no experience)

Regards,
Radu