How to change connection string dynamically in asp.net mvc

Question

In our company we have an app which was made for 1 database. Now I am told to make it multitenant with separate database for each client company. So I made a new database where I would store all users and store their company name, which I will use for changing database. What I want to do: 1. User logs in 2. backend checks the company name of the user 3. retrieved company name will be assigned to dbcontext : base which will switch the database with a name of a company

Ofcourse I looked through other questions related to this in stackoverflow and other places and most of them say this as a solution:

    public FacilityEntities() : base("name=Demo") { }
    public FacilityEntities(string dbConnection) : base(dbConnection)
    {
    }

and most people say that this works. But this doesn't work for me. Also although it is not recommeded I also tried to change the web.config file on runtime, but everytime user logs in, the app refreshes and can't get through the login process.

Code that I have right now:

Login

        public async Task<ActionResult> Login(LoginViewModel model, string returnUrl)
    {
        if (!ModelState.IsValid)
        {
            return View(model);
        }

        // This doesn't count login failures towards account lockout
        // To enable password failures to trigger account lockout, change to shouldLockout: true
        var result = await SignInManager.PasswordSignInAsync(model.Email, model.Password, model.RememberMe, shouldLockout: false);

        switch (result)
        {
            case SignInStatus.Success:
                returnUrl = CheckFirstLogin(model.Email, returnUrl);
                using (var context = new Facility.AdgarWeb.Models.UserCommonDBContext())
                {
                    var companyName = context.CommonUser.Where(x => x.CommonUserEmail == model.Email).FirstOrDefault().CommonUserCompanyName;

                    new FacilityEntities(companyName.ToString());

                }
                        await OnSignInSuccess(model);
                //FormsAuthentication.SetAuthCookie(model.Email, false);
                return RedirectToLocal(returnUrl);
            case SignInStatus.LockedOut:
                return View("Lockout");
            case SignInStatus.RequiresVerification:
                return RedirectToAction("SendCode", new { ReturnUrl = returnUrl, RememberMe = model.RememberMe });
            case SignInStatus.Failure:
            default:
                ModelState.AddModelError("", "Invalid login attempt.");
                return View(model);
        }

DbContext

    public partial class FacilityEntities : DbContext
{        

    public FacilityEntities() : base("name=Demo") { }
    public FacilityEntities(string dbConnection) : base(dbConnection)
    {

    }

}

When I set debugging points on 1.

public FacilityEntities() : base("name=Demo") { }

and 2.

public FacilityEntities(string dbConnection) : base(dbConnection)

I can see that app first hits the first code, then hits second code, but eventually hits back on first.

I also found that I have DbFactory file: DbFactory

        FacilityEntities dbContext;
    public FacilityEntities Init()
    {
        return dbContext ?? (dbContext = new FacilityEntities());
    }

    protected override void DisposeCore()
    {
        if (dbContext != null)
            dbContext.Dispose();
    }
}

Please anyone help me solve this problem.

UPDATE I know that I can use it this way:

public FacilityEntities() : base("name=Demo") { }
public FacilityEntities(string dbConnection) : base(dbConnection)
{
}

and

        using(var db = new FacilityEntities(dbConnection) 
{
//do sth in this db
}

But how do I set the changed db without having to use using(){}? How can I let the user who logs in is using this db all the time? Do I have to call this new db every time I do something with db? Is there any way to set this changed db as a "main database" for this logged in user?

Answer 1

Based on objectively C's good suggestion to create multiple connection strings, I found that it works the following way:

public FacilityEntities() : base("name=Demo") { } // parameterless
public FacilityEntities(string dbConnection) : base($"name={dbConnection}") { } // with param

The only thing that was missing was name=... in the base constructor.

Now you can use it the following way (simplified code):

var dbConnection = "DefaultConnection2";
using (var ctx = new FacilityEntities(dbConnection))
{
   // any EF queries using ctx go here
}

Note: Because FacilityEntities is likely part of an EDMX which consists of generated c# code, the best place to add the parameter constructor as a separate C# file (say FacilityEntities.cs) in the same namespace, which contains a partial class

namespace YourNameSpace // important: use the same namespace your EDMX is using
{
    public partial class FacilityEntities 
    {
        public FacilityEntities(string dbConnection) : base($"name={dbConnection}") { } 
    }
}

The parameterless constructor is already existing (in the generated code), so you only specify the one with parameter.

Answer 2

Your dbcontext class have 2 constructors. 1 default and one parameterized. You need to call parameterized version

return dbContext ?? (dbContext = new FacilityEntities());

Try changing it to

return dbContext ?? (dbContext = new FacilityEntities("New Connection String"));

In either case you have to modify your connection code.

Answer 3

You don't necessarily need to get connection string from web.config. You can use this code to directly pass the connection string

public FacilityEntities(string connString)
{
    this.Database.Connection.ConnectionString = connString;
}

or if your connection string for all your tenant is the same and only database name is different, you can add a connection string in your web.config and replace db name with CompanyName.

Web.Config

<connectionStrings>
  <add name="FacilityEntities" connectionString="Server=.;Database=_DBNAME_;User Id=myUsername; Password=myPassword;" providerName="System.Data.SqlServerCe.4.0"/>    
</connectionStrings>

DbContext

public FacilityEntities(string companyName)
{
    string connString = ConfigurationManager.ConnectionStrings["FacilityEntities"].ConnectionString;
    connString = connString.Replace("_DBNAME_", companyName);
    this.Database.Connection.ConnectionString = connString;
} 

This way you can choose your database name dynamically.

Answer 4

For this problem, I would simply add a second connection string for the new entity to your web.config

<connectionStrings>
 <add name="DefaultConnection" .../>
 <add name="DefaultConnection2" .../>
</connectionStrings