Connecting to multiple databases of different types with NHibernate

Question

I'm writing an application where users can create one or more "Catalogs" which are stored in a database. However, I want to allow for multiple database formats (SQL Server and SQL Lite), and I want a user to be able to have multiple catalogs opened in the application simultaneously. The location of the catalog databases will not be known until run time. Thus I don't want any specific database settings stored in the application's configuration.

I'm just getting started with NHibernate, but I plan to create NHibernate mappings for all my classes related to a Catalog. I'm trying to figure out the best design pattern to allow me to use this single set of mappings with multiple databases simultaneously.

I think I can omit the hibernate.cfg.xml file in my project that points to a specific database and provider, and instead only build the mapping (.hbm.xml) files into my assembly. Is this right?

Then when my applications runs I create a NHibernate configuration from the assembly, which only contains mapping information and not any specific provider/database information. From that configuration I'll create a single ISessionFactory.

Then when user wants to open a catalog I'd create an ADO.NET IDbConnection to the database they specify with the correct provider. Then to access the database I would pass that specific connection to the single SessionFactory's OpenSession() method.

ISession session =  sessionfactory.OpenSession(IDbConnection conn);

Am I on the right track? Will what I describe work, or is there a better way?

---

Update

Now that I've done some more reading I don't think this will work. An ISessionFactory is configured for a specific dialect/driver etc. So while a single ISessionFactory can switch between multiple IDBConnections those connections need to be to the same type of database. Since ISessionFactory is immutable there's no way to switch an instance between supporting one type of database to another. Is this correct? Should I instead create induvidual ISessionFactories for each type of database I want to support?

Answer 1

This is how I did it:

You need two things - multiple session factories stored in a Dictionary, and then another layer of abstraction to access the session factory based on a code.

Each session factory will be configured differently depending on the database.

Your application code could look something like this:

var session = NHibernateSessionManager.OpenSession(factoryCode: "SqlLiteSessionFactory");

I find this is a useful pattern as there are often reasons for sharing mappings, but having different session factory configuration: e.g. you can configure the factories differently for asynchronous logging or batch operations; you can use different database logins etc. Using this pattern, you can also use different mappings for the same entities - for example, perhaps particular users need to access an entity through a view.