Set ConnectionString for EntityFramework Programmatically.

Question

We have a Database (MySQL) say DemoDB. We have different accounts using the db. So one db for each account is created say Account1DB, Account2DB. The Tables stored procedures and functions are all same. We need to develop a Web API and provide some service for inserting and updating data. The user logs in by specifying AccountName ( say from a WPF application where the fields are UserName, Password and Account ComboBox). We need to set the Initial Catalog of the connection string to the account to which the User Logs in ( Actually we need to set the Initial Catalog even before logging in the user as we need to check his credentials based on account).

An Example WPF Screen:

UserName : A

Password: Secret

Account: Account1

When the user clicks on login this is what I would like to do :

1:-> Set Initial Catalog in Connection String to Account1DB

2:-> Then all the DB Operations(Including Authentication) the user does should be on Account1DB Schema

I would like to go with Database First approach. How do a approach this?

Thanks in Advance,

Abhilash D K

Answer 1

You can change you class derived from DbContext to take connectionString as a parameter and pass it to base DbContext constructor.

public partial class MyContext : DbContext
{
    public MyContext() : base("name=MyDbName")
    {
    }

    public MyContext(string connectionString) : base(connectionString)
    {
    }

    // Class members
}

Then you can use something like connection string factory:

public class ConnectionStringFactory
{
    public string GetConnectionString(string initialCatalog)
    {
        SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        builder.InitialCatalog = initialCatalog;

        // Set another connection parameters

        return builder.ToString();
    }
}

Now you can work with separate contexts, check credentials and perform all the DB operations, something like this:

    ConnectionStringFactory factory = new ConnectionStringFactory();
    string connectionString = factory.GetConnectionString("Account1DB");
    MyContext context = new MyContext(connectionString);