1

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.

Improve this question
8
  • 1
    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. Commented Sep 30, 2015 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.. Commented Sep 30, 2015 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". Commented Sep 30, 2015 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? Commented Sep 30, 2015 at 11:23
  • 1
    @Thanushka such an architecture can be used for example in a multi tenant application with separate database for each tenant. Commented Sep 30, 2015 at 11:47

2 Answers 2

Reset to default
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);
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

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 ...."
@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.
ok. Thanks.. If I will post my question then I will tag you. THANKS
@AwaisMahmood, Did this problem get solved? stackoverflow.com/questions/32865627/…
@AwaisMahmood I Posted a new answer to your question here Hope it solve your problem and you find it helpful.
0

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) { }

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.