How to connect to a different database based on logged in user with LINQ

Question

I will try to be as clear as possible on this Smile | :)

Situation
I have one overlooking database, that hold a table with users. Depending on which user logs in, the application should connect to a database that holds more information about that user's organization.

So there are multiple (dynamic) databases where users belong to. All the organizational databases have the same structure of tables but are in separate databases.

Questions

If I would like to work with Linq, is it possible to connect to different databases on-the-fly?
Can I save the the connection strings of the organizational databases in my overlooking database then or what is best here?

Thank you!

possible duplicate of [Linq to Sql - Set connection string dynamically based on environment variable](http://stackoverflow.com/questions/1188962/linq-to-sql-set-connection-string-dynamically-based-on-environment-variable) — M.Babcock, Mar 12 '12 at 14:56
How many users are we talking about here? If its in the order of a dozen or less, databases will clearly be an overkill. — code4life, Mar 12 '12 at 15:43
Well, a database is created per organization.. so not per user. There are multiple users per organization. But i think the number of organizations will stay less then a dozen.. — user1264381, Mar 12 '12 at 15:54

score 0 · Answer 1 · answered Mar 12 '12 at 14:56

0

Add a bunch of connection strings in your web.config. Toggle the strings as needed.

answered Mar 12 '12 at 14:56

Har

4,864
2
19
22

The problem is that the organizations can be managed from the web, so added or deleted... Should i overwrite web.config then? – user1264381 Mar 12 '12 at 15:00
store them in a database then – Har Mar 12 '12 at 15:38
Well how do i switch between them then? And does linq know the structure then? – user1264381 Mar 12 '12 at 16:01

score 0 · Answer 2 · answered Mar 12 '12 at 15:01

LINQ is only a framework to generate queries in SQL or another language based on code you have written in ASP.NET. The real power of LINQ is it's ability to use different frameworks to produce the queries. I know of three off the top of my head: Linq to SQL, Entity Framework, and the Telerik Open Access ORM.

In Entity Framework, The situation you've described is something I had to code for. The biggest problem is generating a proper "Entity Connection String" from your SQL connection string. If needed I should be able to provide the bit of code I got from somewhere to switch out the SQL connection string, but it probably does makes some assumptions about the model name, so you might have to tweak it.

score 0 · Answer 3 · answered Mar 13 '12 at 08:28

I would make a repository for organazation data that has methods with a connection string as one of the arguments. Like

public User GetUser(string connectionString, string name)
{
    using(var context = new OrganizationContext(connectionString))
    {
        ....
    }
}

Where OrganizationContext can be a Linq-to-Sql DataContext or an Entity Framework ObjectContext. You may want to use EntityConnectionStringBuilder for the latter.

Storing the connection strings in the overlooking database is probably the best option.

How to connect to a different database based on logged in user with LINQ

3 Answers3