NHibernate: Creating a ConnectionProvider that dynamically chooses which of several databases to connect to?

Question

I have a project that connects to many SQL Server databases. They all have the same schema, but different data. Data is essentially separated by customer. When a request comes in to the asp.net app, it can tell which database is needed and sets up a session.

What we're doing now is creating a new SessionFactory for each customer database. This has worked out alright for a while, but with more customers we're creating more databases. We're starting to run into memory issues because each factory has it's own QueryPlanCache. I wrote a post about my debugging of the memory.

I want to make it so that we have one SessionFactory that uses a ConnectionProvider to open a connection to the right database. What I have so far looks something like this:

public class DatabaseSpecificConnectionProvider : DriverConnectionProvider
{
    public override IDbConnection GetConnection()
    {
        if (!ThreadConnectionString.HasValue)
            return base.GetConnection();

        var connection = Driver.CreateConnection();
        try
        {
            connection.ConnectionString = ThreadConnectionString.Value;
            connection.Open();
        }
        catch(DbException)
        {
            connection.Dispose();
            throw;
        }
        return connection;
    }
}

This works great if there is only one database needed to handle the request since I can set the connection string in a thread local variable during initization. Where I run into trouble is when I have an admin-level operation that needs to access several databases.

Since the ConnectionProvider has no idea which session is opening the connection, it can't decide which one to use. I could set a thread local variable before opening the session, but that has trouble since the session connections are opened lazily.

I'm also going to need to create a CacheProvider to avoid cache colisions. That's going to have run into a similar problem.

So any ideas? Or is this just asking too much from NHibernate?

Edit: I found this answer that suggests I'd have to rely on some global state which is what I'd like to avoid. If I have multiple sessions active, I'd like the ConnectionProvider to respond with a connection to the appropriate database.

Edit 2: I'm leaning towards a solution that would create a ConnectionProvider for the default Session that is always used for each site. And then for connections to additional databases I'd open the connection and pass it in. The downsides to this I can see is that I can't use the second level cache on ancillary Sessions and I'll have to track and close the connection myself.

Answer 1

I've settled on a workaround and I'm listing it here in case anyone runs across this again.

It turned out I couldn't find anyway to make the ConnectionProvider change databases depending on session. It could only realistically depend on the context of the current request.

In my case, 95% of the time only the one customer's database is going to be needed. I created a SessionFactory and a ConnectionProvider that would handle that. For the remaining corner cases, I created a second SessionFactory and when I open the Session, I pass in a new Connection.

The downside to that is that the Session that talks to the second database can't use the second level cache and I have to make sure I close the connection at the end of the request.

That seems to be working well enough for now, but I'm curious how well it'll stand up in the long run.