Change connection on the fly

Question

Hello, I have a SQL server with 50 databases. Each one has the exact same schema. I used the awesome Subsonic 2.2 create the DAL based on one of them.

I need to loop though a list of database names and connect to each one and perform an update one at a time.

If there a way to alter how subsonic uses the connection string. I believe I would need to store the connection string in memory that way it can keep changing.

Is this possible?

I tried doing a

ConfigurationManager.ConnectionStrings["theConnStrName"].ConnectionString = updated-connection-string-here;

.. but that did not work

thanks!

Answer 1

I don't think it's easy to fix without creating a SubSonic provider for each database.

Instead I would put the SubSonic-source as a project in the solution and debug my way to the best and most clean place to extend the code wwith some sort of connnection string dictionary list and functionality to set the one you want at a given time.

Answer 2

Subsonic was designed primarily for one database only. I've done multiple databases a couple of ways. I had posted a complete sample for creating providers on the fly in the old forums for subsonic. Those posts are gone now. What you can try is set up one provider in the config file (yourProviderName) with a dummy connection string just to initialize subsonic, then set the actual connection string as needed in your code. This only works with one database at a time but I think this is what you needed. If you want to use multiple databases at the same time, and involves more code and some changes to the subsonic 2.x core.

DataProvider provider = DataService.GetInstance(yourProviderName);
foreach(string connString in connectionStrings)
{
    provider.DefaultConnectionString = connString;
    DataService.Provider = provider;
    // ... do stuff
}