4

I'm in a situation where I have a single SQL Server database (call it my "logins" database) that contains records for a number of clients, most importantly what the database name is for that client. My login screen for this web app has the standard username and password fields along with a "unique ID" field too. Herein lies the problem: to try clear this up here's an example.

My database structure

  • Logins (customer's unique login code and their database name here)
  • Customer_1
  • Customer_2
  • etc...

Requirement

Upon login, I need to verify the customer's unique code against the logins database and then somehow persist the fact that further queries for the duration of the authenticated session must run against their own database and that is where I'm stuck.

Firstly, is this even possible? I don't want to store a connection string for each possible client database - I want to work this out upon login.

Secondly, if it is possible, how would I go about doing this using Entity Framework and ASP.NET MVC4?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
tominyorks421
  • 73
  • 1
  • 10
  • 2
    Is the superset of databases static? That is, can you generate multiple EF data contexts and just use the one you need in the application? If the databases are essentially identical and just differ by tenant then it sounds like an improper design, but there could easily be legal reasons for something like that. In that case you can instantiate a new data context with a different connection string (wherever you get that string). Another idea might be to treat other databases as services and not EF contexts if they only provide minimal support to the application. – David Sep 16 '14 at 20:15
  • The databases are indeed identical in structure but you are right, they must be kept separate for legal reasons. I'd love to be able to store all the data in a single database but unfortunately that's not an option. By the sounds of it, I would need to instantiate a new data context when a unique client code is verified but I'm not completely sure how to persist this for the duration of the session (bearing in mind the amount of people suggesting using Session state is a very bad idea in MVC (and I can understand why)) – tominyorks421 Sep 16 '14 at 20:21
  • That's funny. The place I worked for once would love to be able to keep the data in separate db's because it was so big for one client that it was slowing down everyone else. – granadaCoder Sep 16 '14 at 20:23
  • 1
    Some nice tips over here : http://stackoverflow.com/questions/14440698/setup-entity-framework-for-dynamic-connection-string – granadaCoder Sep 16 '14 at 20:25
  • In that case what you might try is to store the connection strings as values in the "master" database that's used for the initial login and identification of the user. Once identified, you'd have the connection string for their database, which the application would then use to initialize a new data context. Maybe have a factory which creates the data context and that factory has a method to set a static connection string? – David Sep 16 '14 at 20:28

1 Answers1

0

I did that recently in a project. We went with 2 data contexts a MasterContext and a ClientContext, with a factory to generate the ClientContext based on the user, something like

public static class ClientContextFactory
{
    public static MyDbContext(string userName)
    {
        //look up the user to find his database

        //build connectio string based on users database

        return new MyDbContext(userConnectionString);

    }
}
Miniver Cheevy
  • 1,667
  • 2
  • 14
  • 20
  • As it happens, this is exactly what I was looking for and is now implemented and working perfectly in my project. Despite my research, I never discovered that a connection string could be passed in when newing up a context – tominyorks421 Sep 22 '14 at 23:14