Subsonic, SharedDbConnectionScope and ApplicationState

Question

I'm looking at using Subsonic with a multi-tenant ASP.net web application. There are multiple DB's (one per client/instance). The user logs in with a domain suffix to their username (e.g. user@tenant1, user@tenant2).

The custom membership provider will then determine which database a user is using, and authenticate against it. All user-initiated calls in the webapp will be wrapped in a SharedDbConnectionScope call, however I have a question regarding caching subsonic items.

Basically each instance will have a few records that rarely change (search options/configurations). I would like to read these in the Application_Start event, and cache them into the ApplicationState.

In the Application_Start event, it would loop over each client database, use a SharedDbConnectionScope to connect to each DB, and create these cached records (e.g. Application('tenant1_search_obj') = subsonic_object

When a user loads the search page, it would then check what domain a user is in, and then retreive that search option from the cache.

Is this feasible? I'm just concerned that if I cache an object, when I retrieve it from the application cache it won't know what connection its using, and might possibly pull in the wrong data.

I'd like to avoid putting this in the session object if possible.

Answer 1

it's possible, but probably not a good idea since it doesn't scale at all - you're going to pop a new connection for every single client whether they show up or not.

Maybe your best bet is to "lazy load" the setting - first hit on the search page loads the config into the cache or Application settings and there it stays.

Other than that - to answer your question it is possible. If you're using SubSonic 3, just create a new provider on the fly using ProviderFactory.GetProvider(connectionString, "System.Data.SqlClient") and then execute your stuff against it.

For SubSonic 2 - SharedConnectionScope is what you want.