I'm trying to use NHibernate to connect to multiple databases (all on the same server). I'm looking for a little help and/or validation that I can do so with NHibernate. I'm fairly comfortable using NHibernate with a single database, but I've never had to work with more than one.

The way the databases are setup is like this: there is a main (or "master") database and several individual (or child/slave) databases. The way the app is supposed to work is that when it needs to get or save some data, it looks up which database to use in the main database, gets a new connection string, then connects to a specified individual database to do all it's work.

So far the best solution I can come up with is to get the info from the main database then open a new connection and pass that in to sessionFactory.OpenSession(connection).

What I don't like about that is then I have to open a connection before it is really needed, and then I have to keep track of that connection so that I can close it after the request is done. I came up with a partial solution by wrapping the session in a new class that implements ISession and closes the connection in Dispose and Disconnect and a few other places.

Does this seem like a good idea? Is there a better way to do this? Could I create a SessionFactory per DB?

I'd really like to be able to use NHibernate (or at least some ORM tool). There's several reasons behind this multiple database structure and I don't think there's any other option that fits our requirements.

Anyone have any ideas or input?
Thanks,
David

BTW: This is using SQL Server 2005 and ASP.NET 2.0 if that makes any difference.

Are the "databases" in fact different schemas from a same database server?
If so, yo do have some range of maneuver.
You can either set a different default_schema property on your hibernate connection info, or have multiple mapping files (even on the same class), with different "schema" property values.

Hi,

Yes, you can create a SessionFactory per connection; I've used that approach (which has the benefit that the DBs can be on different boxes, and doesn't require too much jiggery pokery with mapping files). You do need to run the same setup code for each factory, which I ended up doing programmatically (though I'm sure it's possible through the config file). You can cache the session factories if you want to construct them lazily (I think I used some kind of map) since they're expensive to create.

Steve

Gonzalo: the databases are separate physical databases on the same server. Every individual database has an identical schema, but the main database has its own schema. All the classes are either in the main database or one of the individual databases, never both. In the future I suppose the databases might even be on separate physical servers too.

Steve: Good to hear that someone else has done the SessionFactory per database thing. I think I might give that a try next. I could wait until I need a connection to a specific individual database and then keep the factory in a Dictionary<string, SessionFactory> where the string is the database name.

I was thinking it might be a little costly to create several SessionFactories since the docs say to only create one, but I think in this case it makes sense.

Thanks for the input guys! I feel a little more confident that this is doable now.

The docs don't mention this scenario at all; I'm not sure why it's unwise to create more than one factory. On the other hand, if all your databases (except the main one) are the same you might as well use a single SessionFactoy.

I guess I can't really find anywhere that says you shouldn't create more than one SessionFactory. In fact I found this in the docs:



I think there was a quickstart guide that suggested creating a single factory at application startup, but the example had just one database, so that works there.

The reason I want more than one SessionFactory is so that NHibernate can deal with creating, opening, and closing connections. If there was an overload for OpenSession that took a connection string that would work as well for my purposes.

I'm feeling fairly confident with the code I have now that creates a Configuration object, then creates a few factories calling cfg.SetProperty("hibernate.connection.connection_string", connectionString); before each factory is created.

Yep, that'll do the trick. Good luck!