Any ORM can programmatically change its connection string at runtime?

Question

I created my own CMS for my own use. currently, I already launch 3 websites using my CMS. and will keep adding.

since all the websites always use the same version of my CMS, I just need to create one administration site to rule them all.

I use Subsonic 2.x for data access layer. in administration website, I have to put all connectionStrings

<SubSonicService defaultProvider="firstSql">
    <providers>
        <clear/>
        <add name="firstSql" type="SubSonic.SqlDataProvider, SubSonic" connectionStringName="first" generatedNamespace="firstSql"/>
        <add name="secondSql" type="SubSonic.SqlDataProvider, SubSonic" connectionStringName="second" generatedNamespace="second"/>
        <add name="thirdSql" type="SubSonic.SqlDataProvider, SubSonic" connectionStringName="third" generatedNamespace="thirdSql"/>
    </providers>
</SubSonicService>

<connectionStrings>
    <clear/>
    <add name="first" connectionString="Data Source=123.123.12.3;Initial Catalog=first;User ID=first;Password=first" providerName="System.Data.SqlClient"/>
    <add name="second" connectionString="Data Source=123.123.12.3;Initial Catalog=second;User ID=second;Password=second" providerName="System.Data.SqlClient"/>
    <add name="third" connectionString="Data Source=123.123.12.3;Initial Catalog=third;User ID=third;Password=third" providerName="System.Data.SqlClient"/>
</connectionStrings>

here is the ugly code

switch(sitename){
    case "first":
        var comment1 = new firstSql.Comment(id);
        comment1.Accepted = true;
        comment1.Save();
        break;
    case "second":
        var comment2 = new secondSql.Comment(id);
        comment2.Accepted = true;
        comment2.Save();
        break;
    case "third":
        var comment3 = new thirdSql.Comment(id);
        comment3.Accepted = true;
        comment3.Save();
        break;
}

I'm looking for a way to make it like this

/* some magic to dynamically change the connection string */
cmsSql.ConnectionString( getConnectionString(sitename) ); 

var comment = new cmsSql.Comment(id);
comment.Accepted = true;
comment.Save();

Is there any ORM solution that support this?

Or,

do you know any workaround to do it with current ORM (Subsonic 2.x) ?

UPDATE: I add one more example

cmsSql.ConnectionString( DB_ConString_WebsiteABC ); 

var comment = new cmsSql.Comment(id);
comment.Accepted = true;
comment.Save();

/* some magic to dynamically change the connection string */
cmsSql.ConnectionString( DB_ConString_AnotherWebsiteThatSimilarToABC ); 

var comment = new cmsSql.Comment(id);
comment.Accepted = true;
comment.Save(); // saved to another database

Answer 1

If you only need to manage the CMS for one site at once, I would use a single SubSonic provider, and change the static DefaultConnectionString property, as per this example:

http://www.stevetrefethen.com/blog/SettingSubsonicsconnectionstringatruntime.aspx

Answer 2

I don't know about subsonic, but in LINQ-to-SQL you could write a ContextFactory which creates contexts using different connection strings.

static class ContextFactory
{
    public static MyDataContext CreateMyDataContent(string sitename)
    {
        var context;
        switch(sitename){
        case "first":
            context = new MyDataContext ("connection string");
        case "second":
            // and so on
        }

        return context;
    }
}

The connection string itself you could either hard code or read from a config file, or from whereever you want :-)

Answer 3

What exactly do you want?
Set the connection string once (but programmatically) when your application starts?
Or do you want to change the connection string while your app is running, like "run only the next query on database number two"?

If it's the first case (once when your application starts):
I don't know Subsonic, but NHibernate can do this.

var cfg = new Configuration();

// set connectionstring programmatically
cfg.Properties["connection.connection_string"] = getConnectionString(sitename);
cfg.Configure();

cfg.AddAssembly( ... );
ISessionFactory sf = cfg.BuildSessionFactory();

Answer 4

What you're essentially asking for is a multi-tenanted application.

The same application (your CMS) using the same business logic and user interface will be different depending upon which client/context/company/tenant you're operating on.

In your situation you're doing 'single application, isolated database' multi-tenancy. That is you have a single app, and each tenant has their own database, isolated from the other tenants. (Other options include a shared database where records are isolated using a key, such as "TENANT_ID" on all tables, and the where clause filters them out).

In NHibernate you can achieve this by using multiple session factories (one per tenant) identified by a unique identifier, such as the site id.

Working out which site id to use can be done in a number of ways. Our application bases it off the hostname, but if you're doing it from a single site, but wanting to switch contexts you could do it as a form field from a dropdown box of "site selector".

For SubSonic I believe you can do multiple databases as well - but swapping between them is something I don't know how to do.

Answer 5

since all your ConnectionStrings saved in the web.config file, why not just take what suitable your current website name from the connectionStrings Collection directly?

ConfigurationManager.ConnectionStrings[sitename].ConnectionString;

OR
save the connections back in a database (you may need encryption here to keep yourself safe) so you dont hardcoded them in the web.config and you just store the connection of that database, now .. one query could solve the whole issue by filtering on the website name - a small logic class can do this for you

SELECT connection FROM tblConnections WHERE siteName = 'first'

Hope this helped.