Switch between databases with one DbContext

Question

I'm working on a developing a web application which is having several databases with the same schema. Depends on the logged in user the database to execute queries may differ.

I'm planning to have connection string for each DB and when executing the query in the repository level change the connection string when the DB context is creating.

My problem is that I've tried to pass the connection string name to the DB context dynamically before executing the linq query, but failed.

Would be grate if someone can help me on doing this and also let me know any pros and cons in this approach.

Please show your code and explain what actually happened. What you describe should work. And don't ask for "any pros and cons", that makes this question way too broad. — CodeCaster, Sep 30 '15 at 11:17
The `DbContext`-constructor has a parameter which takes connection string. Why don't you use that? Before creating the instance of `DbContext` you would then need to execute your logic which gets the correct connection string depending on the logged in user.. — PzYon, Sep 30 '15 at 11:18
@PzYon if you read the question, that seems to be exactly what OP is doing, at least I guess that's what they mean by _"pass the connection string name to the DB context dynamically"_. — CodeCaster, Sep 30 '15 at 11:18
@CodeCaster jep, could be. But it could also be that they're some how trying to change the connection string of an existing instance. Maybe we need some more information or even better, a code sample? — PzYon, Sep 30 '15 at 11:23
Hi thanks for your valuable information. I mainly want to know whether it is ok to change the connection string dynamically or is to a bad design. — Thanushka, Sep 30 '15 at 11:25
As long as you are sure you always have the same schema I don't see anything wrong with using a different connection string depending on some other parameter (in your case the user). Just be sure you don't mix up entities coming from different contexts, etc. If you have clear design this shouldn't be a problem from my point of view. — PzYon, Sep 30 '15 at 11:33
@Thanushka such an architecture can be used for example in a multi tenant application with separate database for each tenant. — Reza Aghaei, Sep 30 '15 at 11:47
@Thanushka you may find these answers helpful too: http://stackoverflow.com/a/32885496/3110834 and http://stackoverflow.com/a/32887186/3110834 — Reza Aghaei, Oct 01 '15 at 12:17

Reza Aghaei · Accepted Answer · 2015-09-30T11:45:57.620

2

You can simply add an overload to your db context constructor that accepts connection string as input:

public partial class SampleDbEntities
{
    public SampleDbEntities(string connectionString)
        : base(connectionString)
    {
    }
}

Then when you need to create an instance of your db context, use this overload and inject suitable username and password in the connection string:

For example when your connection string looks like this:

var connectionTemplate = @"provider=System.Data.SqlClient;" +
                       @"provider connection string=""data source={0};" +
                       @"initial catalog=SERVERNAME;persist security info=True;" +
                       @"user id={1};password={2};" +
                       @"MultipleActiveResultSets=True;App=EntityFramework""";

then you can use string.Format(connectionTemplate, DatabaseName, UserName, Password) to create connection string and pass to constructor of your db context;

//You will provide Databasename, UserName, Password based on your logic
//for example based on the user who logged in application.
var connectionString= string.Format(connectionTemplate, DatabaseName, UserName, Password);
var db = new SampleDbEntities(connectionString);

edited Sep 30 '15 at 11:45

answered Sep 30 '15 at 11:29

Reza Aghaei

120,393
18
203
398

I am trying to use the same approach. I want to generate different databases for each new user. I have added a conn string to config file programatically. Then I open EntityState conn. But when I tried to query it, it throws the exception in ModelBuilder as "The context is being used in Code First mode with code that was generated from an EDMX file ...." – Awais Mahmood Oct 01 '15 at 08:19
@AwaisMahmood For connecting to multiple database there is no problem and it will be done this way. I'll take a look at your question about creating multiple database. – Reza Aghaei Oct 01 '15 at 08:29
ok. Thanks.. If I will post my question then I will tag you. THANKS – Awais Mahmood Oct 01 '15 at 08:51
@AwaisMahmood, Did this problem get solved? http://stackoverflow.com/questions/32865627/using-multiple-databases-with-single-dbcontext-and-entites-and-generating-conn-s – Reza Aghaei Oct 01 '15 at 09:23
@AwaisMahmood I Posted a new answer to your question [here](http://stackoverflow.com/a/32885496/3110834) Hope it solve your problem and you find it helpful. – Reza Aghaei Oct 01 '15 at 10:46

score 0 · Answer 2 · answered Sep 30 '15 at 11:29

For changing the connexion string of the DBContext you have to override the constructor of the DBContext. You have to add a constructor with a connection in parameter (the connexion you have defined previously...)

ex : public MYEntities(EntityConnection entityConnection) : base(entityConnection, true) { }

Switch between databases with one DbContext

2 Answers2