Asp.net MVC2 - Multiple databases

Question

I have a database called "Config" where I have a table called "Customer" In the Customer table I hold the login credentials, along with a connection String to the client's database.

Inside of my controller I'm checking to see what customer database i'm querying

        String connectionString = "";
        if (id == 1)
            connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Client1.mdf;Integrated Security=True;User Instance=True";
        else if (id == 2)
            connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Client2.mdf;Integrated Security=True;User Instance=True";

        using (TestDbaseDataContext db = new TestDbaseDataContext(connectionString))
        {
            var searchRecords = db.SearchRecords;
            return View(searchRecords.ToList());
        }

There will be an undetermined number of client's using separate databases, these connection strings will change based on archived status, and other factors. I don't think hard coding these strings in app.config will do what I need.

I have a "Master" database, that holds all of the client's configuration settings, along with the client's database connection string. The mater database is only used to mitigate which client database to search. I store the connection strings in the master database so I can pass that connection string for the logged in customer to query the correct database. It is a requirement to have each client in it's own database

How would you store this connection string so I can pass to my datacontext when needed? Should it be stored in a session variable, or should I query the Config database Customer table in each controller method?

Answer 1

I've set up a similar "siloed" multi-client application before. This is a good case for a session variable. Each user relates to a client, which has its own database. On login, each user should get a "context" object in Session state containing all necessary identifying data, including database connection string.