asp.net - Single website multiple databases, database switching

Question

I have created a content management system (CMS) for my company’s product databases. The CMS is based on asp.net scaffolding with many custom pages and actions mixed in. We have 7 products currently, all of which share the same database schema (Entity Framework model-first) and all run perfectly in the CMS. The issue is that every time we get a new product we must clone the CMS and change the connection string in the app.config to point to the correct database in order to work with the new database. While this works, it’s becoming bothersome to maintain and will fail us completely as we acquire more products.

What I would like to do is have a centralized landing page where a user is directed to log in, then given the option to connect to and edit a specific product based on their selection. The idea is that we would have one CMS site which would be able to switch between the databases depending on the user. It is not an option to combine all of the product database in to a single master product database.

I am not sure where to start to achieve this goal, or if this is even the correct plan to achieve my goal of having a single CMS to maintain, and am looking for some guidance in this.

Answer 1

Assuming that your database structures are identical, you could use a factory method anywhere you get an instance of your entity context and put logic in there to grab the correct connection string (or calculate it if there's a naming convention that you could use). Something like this might work for example:

    public static MyDatabaseEntities CreateEntityContext(string productName)
    {
        string connectionString = null;
        switch (productName.Trim().ToLower())
        {
            case "apples":
                connectionString = ConfigurationManager.ConnectionStrings["MyDatabase_Apples"].ConnectionString;
                break;
            case "pears":
                connectionString = ConfigurationManager.ConnectionStrings["MyDatabase_Pears"].ConnectionString;
                break;
            default:
                connectionString = ConfigurationManager.ConnectionStrings["MyDatabase"].ConnectionString;
                break;
        }
        return new MyDatabaseEntities(connectionString);
    }

Then use this method anywhere you need an instance of your CRM data context passing in the product name that you've calculated on your landing page.