I have an ancient mess I'm trying to shovel into tiers using SubSonic. The trouble is, I have this scenario:
When Hal logs in, his login uses database X for lookup data, database Y for his accounts and database Z for his contacts.
When Barry logs in, his login uses database X for lookupdata, database Q for his accounts and database R for his contacts.
X,Y,Z,Q and R are all on the same server. Y has an identical schema to Q, and and Z has an identical schema to R. Don't get me started on how stupid a setup this is :)
I have to make my .NET Windows Forms application (using SubSonic) point to the correct databases.
As far as I can tell, I'll have to get my hands dirty changing the SubSonic source (and maintain those changes with every SubSonic release), so it can accept parameters rather than use file app.config. Is there an alternative to this?
I already had the multiple providers worked out, but I didn't know how to set them at runtime (and I already searched this forum. Must have used the wrong keywords).
SharedDBConnectionScope seems perfect for on the fly, but it didn't seem designed to set the provider for the whole user session.
So I did more searching based on your answers above and came up with the following solution:
Add three providers for lookups, accounts and contacts, and generate the DAL.
add this to the DAL:
public static void SetProvider(string strProvider,string strConnectionString) { DataService.GetInstance(strProvider).DefaultConnectionString = strConnectionString; }
call it on login, once my app has worked out which databases the user uses, eg
MyDAL.SSProvider.SetProvider("Lookups", "server=10.123.456.78;port=3306;uid=whatever;pwd=blah;database=X")
MyDAL.SSProvider.SetProvider("Accounts", "server=10.123.456.78;port=3306;uid=whatever;pwd=blah;database=Y")
MyDAL.SSProvider.SetProvider("Contacts", "server=10.123.456.78;port=3306;uid=whatever;pwd=blah;database=Z")
And off it goes.