How to change database context

My web app has a database per customer. The LightSpeedContext will initially always point to one DB say AccountDB and then I wish to change the context of the DB to the customer specific one. I'm thinking I will do this using the DDL statement 'USE CUSTOMERADB GO". I can see that ExecuteNativeCommand is available during Migrations but is this available for a normal UnitOfWork?

What is you recommendation for working with multiple databases?

Thanks.

I think sending a USE command would probably work if you were careful, but it's not something we've tested. Care would definitely be required though. For example, if you loaded something from AccountDB, then did a USE command to switch to CustomerADB behind LightSpeed's back, then if you modified the entity from AccountDB and did a SaveChanges(), it would try to save the entity from AccountDB back to CustomerADB (because it just saves over the UOW's connection).

If you decide to go this route, IUnitOfWork doesn't have a direct equivalent to ExecuteNativeCommand, but you can easily implement it using PrepareCommand, something like this:

public static void ExecuteNativeCommand(this IUnitOfWork uow, string sql) {
  using (IDbCommand cmd = uow.Context.DataProviderObjectFactor.CreateCommand()) {
    cmd.CommandText = sql;
    uow.PrepareCommand(cmd);  // Enrols cmd in uow's connection and transaction
    cmd.ExecuteNonReader();
  }
}

A safer approach might be to use two LightSpeedContexts and hence two units of work -- one for working with AccountDB, and one for working with the customer-specific database.

Thanks Ivan.

I did some further digging around found this post: http://www.mindscapehq.com/forums/thread/4081

I am now passing in a customer specific connection string and creating a UnitOfWork this way. One question I have though is how would this affect connection pooling? My understanding is that connection pooling works per connection string so does this mean if I have 1000 customers I will now have 1000-ish connections open assuming all customers are online at the same time?

The reason I though of the 'USE CUSTOMERDB1' approach is that connection string doesn't change so connection pooling would be fine. I agree this approach is slightly riskier though.

You're right, pooling is by connection string. So an approach which uses the same connection string then switches the database would indeed result in more efficient use of the pool. Like I say, we haven't tested LightSpeed in this configuration but we believe it should work with care -- we'd be interested to hear your experiences!

Great. Will update the thread after some testing!