I need to be able to allow the users of an ASP.NET web app to select the appropriate database schema at the login screen. Depending on what schema is selected at runtime I will need to use that schema to retrieve the data (but only for that user) for any subsequent data retrievals.

The reason for this is that we keep monthly backups of the data (as the data being stored is MASSIVE), and users may want to do queries on older data.

I have already tried something like this:
GetNHibernateSessionFactory().ConnectionProvider.GetConnection().ChangeDatabase(databaseName);

but that did nothing.

Next i'm thinking of changing the config file for the session, but I believe that would change it for each user.

Any suggestions?

Come on, this should be easy!
It would take me about 5 minutes to do using ADO.NET.

This will be the last straw, if I cannot get this working I will scrap NHibernate and use something that can actually get the job done.

Are you really using different schemas? Then you also need different mappings, and probably need different SessionFactories using different configurations.

If you just need different connection strings, why not open the ADO-connection yourself and pass it to OpenSession()?

The schemas are technically the same, so I do not need different mappings.

Using the method you propose I'm not sure how NHibernate will be able to handle different users using different connection strings.

Either way I will give this a shot and let you know how it goes.

Thanks for the reply!

Unless you're reconnecting your ISession between http requests there's at most one user (i.e connection string) per ISession. Shouldn't be a problem.

Ok I managed to get it working (somewhat). I did it the way you suggested.
However I don't think the way I implemented is very good, maybe you can suggest a better way?

On the login screen I generate the query string based on what DB is selected and then open and store a MySqlConnection in an ASP.NET Session[]

In my HttpModule event (the first event that has access to Sessions), I retrieve the MySqlConnection from the Session[] and use it to call OpenSession(openConnection) .

I really would like an alternate way to do this without using asp.net Sessions...

Oh god... I hope you aren't storing a connection object in session. You'll kill your app as soon as you put any kind of load on it.

We had a similar situation. We ended building the connection string per request in an http module once we knew the users database. I don't really like this implementation myself, but it was necessary.

Yeah, I was storing the connection object in a session :(.

I was just wondering with the code example above, how did you store/retrieve "users database".

I am now putting the connection string into the session[] instead of the connection object, I'd imagine you would've done something similar?

The last thing that I need to take care of now is opening/closing the SqlConnection.
This should be openened for each user only once ever, so how do I persist that?
And when/where should I close it? calling session.close() will not close the database connection.

Thanks for the help!

IF you simply must keep some sort of reference to a DB connection object, use it on a PER request basis (ie HttpContext.Current.Items).

My best recommendation is to use a custom (or alter the existing one) ConnectionProvider (NHibernate.Connection.ConnectionProvider) that way you can truly control the connections being created and closing them specifically when NHibernate is saying it is done with them. The abstract class is super simple to use.

I'll have a look at the NHibernate.Connection.ConnectionProvider, might prove to be useful in the future.

I discovered an even easier way to change the database dynamically.
Here is an example of what I do now:



Where session is ISession.
This seems to do the job, and makes the code look much cleaner.