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?

I'd go with @objectively C's suggestion and in your code, determine which connection string to use based on the user's company information you got from the login. You said one DB per company, right? — Phil N DeBlanc
– Phil N DeBlanc, Commented Apr 9, 2018 at 18:14
I already have all the database connectionstrings in web.config. I want user to login, backend retrieves companyName of the user (which is also a database name listed in connectionstrings in webconfig). The new FacilityEntities(companyName.ToString()); in Login code puts in company name to dbcontext and should choose the correct db from web.config, but it doesn't work — akyj
– akyj, Commented Apr 9, 2018 at 18:20
When you say "separate database" are you referring to a separate SQL Server instance or do you have several databases within the instance? Or are you not using SQL Server but something else? — John Wu
– John Wu, Commented Apr 10, 2018 at 1:11
@JohnWu I have one mysql instance with several databases which are named as user company names — akyj
– akyj, Commented Apr 10, 2018 at 6:09
"I can see that app first hits the first code, then hits second code, but eventually hits back on first." It's supposed to do that. Did the connection not get set properly? What was the issue? — John Wu
– John Wu, Commented Apr 10, 2018 at 7:11

Kahbazi · Accepted Answer · 2018-04-09 18:36:56Z

4

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.

answered Apr 9, 2018 at 18:36

Kahbazi

15.1k3 gold badges52 silver badges80 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

akyj Over a year ago

on that second option, what do I do with public FacilityEntities() : base("name=demo")? I can't remove it without getting errors. While debugging in the debugging console when I type this.Database.Connection.ConnectionString it returns a correct value, but doesn't switch to that database

akyj Over a year ago

If I remove that default constructor i have error because i have a dbfactory where we have return dbContext ?? (dbContext = new FacilityEntities()) which doesnt accept any parameters. I thonk that your answer is close but app after hitting the code you provided in the end, eventually hits the default constructor with demo database

Kahbazi Over a year ago

you can still have public FacilityEntities() : base("name=Demo") { } so your DbContextFactory call this constructor

akyj Over a year ago

But the database stays as demo and doesnt change to the database with company Name. If I set debugging poits on both constructors App hits your code and debugging console shows the correct connection string. But after that it hits default constructor with name=demo and sets database as demo

Kahbazi Over a year ago

I don't know the point of our dbContextFactory, if you use it all the time, dbContextFactory should pass the company name and call the new constructor

|

objectively C · Accepted Answer · 2018-04-09 18:08:12Z

3

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

answered Apr 9, 2018 at 18:08

objectively C

1,0281 gold badge10 silver badges27 bronze badges

4 Comments

akyj Over a year ago

But how to choose which one to use? I don't want db name hardcoded. I want to change database based on the user logging in.

objectively C Over a year ago

@akyj "How to choose which one to use" is a business rule question. I would consult with your client or PM. You can change the DB based on the user with a simple if-else.

objectively C Over a year ago

@akyj Consider marking this question as answered, then you should post a new, separate, specific question for your second issue. I will be happy to take a look once you post the new question.

Matt Over a year ago

How to make EF using the additional connection strings, you can find here. I have used that in one of my own projects and it is working fine.

Anis ur Rehman · Accepted Answer · 2018-04-09 18:55:11Z

0

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.

answered Apr 9, 2018 at 18:55

Anis ur Rehman

895 bronze badges

1 Comment

akyj Over a year ago

I need to provide db name dynamically instead of hardcoding

Matt · Accepted Answer · 2019-11-26 08:29:33Z

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.

Collectives™ on Stack Overflow

How to change connection string dynamically in asp.net mvc

4 Answers 4

9 Comments

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

9 Comments

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related