How to have different connection string for different users in Entity Framework

Question

I have a requirement in which I need to have different connection strings for different users. The idea is to have the username and password supplied at the login screen to be used as the username and password of the connection string. Thus making application to use different connection string for different user, and to use this connection string throughout the application.

How to get this setup in EF 4.1

PS: I am using DbContext

you need this http://stackoverflow.com/questions/1453701/different-connection-strings-with-entity-framework-based-on-context — spajce, Jan 23 '13 at 13:22

score 6 · Accepted Answer · answered Jan 23 '13 at 14:33

Thanks to Kevin Junghans

This is how I have done it.

in the model context class

public class MyEntities : DbContext
{
    public MyEntities (string connectionString)
        : base(connectionString)
    {
    }

then in the login controller

var dataConnection = WebConfigurationManager.OpenWebConfiguration("/").ConnectionStrings.ConnectionStrings["MyConnectionString"].ConnectionString;
dataConnection = dataConnection.Substring(0, dataConnection.LastIndexOf("\"")) + ";USER ID=" + userName +";Password=" + password + "\"";
Session["connectionString"] = dataConnection;

and the from else where

var _db = new MyEntities (Session["connectionString"].ToString());

score 4 · Answer 2 · answered Jan 23 '13 at 13:37

4

You could use the following DbContext constructor which accepts the connections string or name as an argument.

public DbContext(
string nameOrConnectionString,
DbCompiledModel model
)

answered Jan 23 '13 at 13:37

Kevin Junghans

17,475
4
45
62

score 3 · Answer 3 · answered Jan 23 '13 at 13:55

I dont know which is specifically your question, its not about MVC its only for EF.

If I understand correctly what you want to do, you probably have separeted databases for each user, but you have only ONE database for the users account information for login

You can add one more field to that database, the users login databse, with the specific connectionString for that user. Then when you login the user use the DbContext for that databse and login, then get the value for the connectionString and generate the new DbContext for the specific database for the user loged in.

If you need more help please comment.

I don't have different databases for different user but rather different privilege for different users — Syed Mohd Mohsin Akhtar, Jan 23 '13 at 14:15

How to have different connection string for different users in Entity Framework

3 Answers3

Linked