ASP.NET Core change EF connection string when user logs in

Solution 1:

Create a DbContext factory

public static class DbContextFactory
{
    public static Dictionary<string, string> ConnectionStrings { get; set; }

    public static void SetConnectionString(Dictionary<string, string> connStrs)
    {
        ConnectionStrings = connStrs;
    }

    public static MyDbContext Create(string connid)
    {
        if (!string.IsNullOrEmpty(connid))
        {
            var connStr = ConnectionStrings[connid];
            var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();
            optionsBuilder.UseSqlServer(connStr);
            return new MyDbContext(optionsBuilder.Options);
        }
        else
        {
            throw new ArgumentNullException("ConnectionId");
        }
    }
}

Intialize DbContext factory

In startup.cs

public void Configure()
{
  Dictionary<string, string> connStrs = new Dictionary<string, string>();
  connStrs.Add("DB1", Configuration["Data:DB1Connection:ConnectionString"]);
  connStrs.Add("DB2", Configuration["Data:DB2Connection:ConnectionString"]);
  DbContextFactory.SetConnectionString(connStrs);
}

Usage

var dbContext= DbContextFactory.Create("DB1");

Solution 2:

According to your question, I going to provide a solution assuming some things:

First, I've created three databases in my local SQL Server instance:

create database CompanyFoo
go

create database CompanyBar
go

create database CompanyZaz
go

Then, I going to create one table with one row in each database:

use CompanyFoo
go

drop table ConfigurationValue
go

create table ConfigurationValue
(
    Id int not null identity(1, 1),
    Name varchar(255) not null,
    [Desc] varchar(max) not null
)
go

insert into ConfigurationValue values ('Company name', 'Foo Company')
go

use CompanyBar
go

drop table ConfigurationValue
go

create table ConfigurationValue
(
    Id int not null identity(1, 1),
    Name varchar(255) not null,
    [Desc] varchar(max) not null
)
go

insert into ConfigurationValue values ('Company name', 'Bar Company')
go

use CompanyZaz
go

drop table ConfigurationValue
go

create table ConfigurationValue
(
    Id int not null identity(1, 1),
    Name varchar(255) not null,
    [Desc] varchar(max) not null
)
go

insert into ConfigurationValue values ('Company name', 'Zaz Company')
go

Next step is create an user with SQL Authentication and grant access to read the databases, in my case my user name is johnd and password is 123.

Once we have these steps completed, we proceed to create an MVC application in ASP.NET Core, I used MultipleCompany as project name, I have two controllers: Home and Administration, the goal is to show a login view first and then redirect to another view to show data according to selected database in "login" view.

To accomplish your requirement, you'll need to use session on ASP.NET Core application you can change this way to storage and read data later, for now this is for concept test only.

HomeController code:

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using MultipleCompany.Models;

namespace MultipleCompany.Controllers
{
    public class HomeController : Controller
    {
        public IActionResult Index()
        {
            return View();
        }

        [HttpPost]
        public IActionResult Index(LoginModel model)
        {
            HttpContext.Session.SetString("CompanyCode", model.CompanyCode);
            HttpContext.Session.SetString("UserName", model.UserName);
            HttpContext.Session.SetString("Password", model.Password);

            return RedirectToAction("Index", "Administration");
        }

        public IActionResult Error()
        {
            return View();
        }
    }
}

AdministrationController code:

using System.Linq;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Filters;
using MultipleCompany.Models;
using MultipleCompany.Services;

namespace MultipleCompany.Controllers
{
    public class AdministrationController : Controller
    {
        protected IDbContextService DbContextService;
        protected CompanyDbContext DbContext;

        public AdministrationController(IDbContextService dbContextService)
        {
            DbContextService = dbContextService;
        }

        public override void OnActionExecuting(ActionExecutingContext context)
        {
            DbContext = DbContextService.CreateCompanyDbContext(HttpContext.Session.CreateLoginModelFromSession());

            base.OnActionExecuting(context);
        }

        public IActionResult Index()
        {
            var model = DbContext.ConfigurationValue.ToList();

            return View(model);
        }
    }
}

Code for Home view:

@{
    ViewData["Title"] = "Home Page";
}

<form action="/home" method="post">
    <fieldset>
        <legend>Log in</legend>

        <div>
            <label for="CompanyCode">Company code</label>
            <select name="CompanyCode">
                <option value="CompanyFoo">Foo</option>
                <option value="CompanyBar">Bar</option>
                <option value="CompanyZaz">Zaz</option>
            </select>
        </div>

        <div>
            <label for="UserName">User name</label>
            <input type="text" name="UserName" />
        </div>

        <div>
            <label for="Password">Password</label>
            <input type="password" name="Password" />
        </div>

        <button type="submit">Log in</button>
    </fieldset>
</form>

Code for Administration view:

@{
    ViewData["Title"] = "Home Page";
}

<h1>Welcome!</h1>

<table class="table">
    <tr>
        <th>Name</th>
        <th>Desc</th>
    </tr>
    @foreach (var item in Model)
    {
        <tr>
            <td>@item.Name</td>
            <td>@item.Desc</td>
        </tr>
    }
</table>

LoginModel code:

using System;
using Microsoft.AspNetCore.Http;

namespace MultipleCompany.Models
{
    public class LoginModel
    {
        public String CompanyCode { get; set; }

        public String UserName { get; set; }

        public String Password { get; set; }
    }

    public static class LoginModelExtensions
    {
        public static LoginModel CreateLoginModelFromSession(this ISession session)
        {
            var companyCode = session.GetString("CompanyCode");
            var userName = session.GetString("UserName");
            var password = session.GetString("Password");

            return new LoginModel
            {
                CompanyCode = companyCode,
                UserName = userName,
                Password = password
            };
        }
    }
}

CompanyDbContext code:

using System;
using Microsoft.EntityFrameworkCore;

namespace MultipleCompany.Models
{
    public class CompanyDbContext : Microsoft.EntityFrameworkCore.DbContext
    {
        public CompanyDbContext(String connectionString)
        {
            ConnectionString = connectionString;
        }

        public String ConnectionString { get; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(ConnectionString);

            base.OnConfiguring(optionsBuilder);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }

        public DbSet<ConfigurationValue> ConfigurationValue { get; set; }
    }
}

ConfigurationValue code:

using System;

namespace MultipleCompany.Models
{
    public class ConfigurationValue
    {
        public Int32? Id { get; set; }

        public String Name { get; set; }

        public String Desc { get; set; }
    }
}

AppSettings code:

using System;

namespace MultipleCompany.Models
{
    public class AppSettings
    {
        public String CompanyConnectionString { get; set; }
    }
}

IDbContextService code:

using MultipleCompany.Models;

namespace MultipleCompany.Services
{
    public interface IDbContextService
    {
        CompanyDbContext CreateCompanyDbContext(LoginModel model);
    }
}

DbContextService code:

using System;
using Microsoft.Extensions.Options;
using MultipleCompany.Models;

namespace MultipleCompany.Services
{
    public class DbContextService : IDbContextService
    {
        public DbContextService(IOptions<AppSettings> appSettings)
        {
            ConnectionString = appSettings.Value.CompanyConnectionString;
        }

        public String ConnectionString { get; }

        public CompanyDbContext CreateCompanyDbContext(LoginModel model)
        {
            var connectionString = ConnectionString.Replace("{database}", model.CompanyCode).Replace("{user id}", model.UserName).Replace("{password}", model.Password);

            var dbContext = new CompanyDbContext(connectionString);

            return dbContext;
        }
    }
}

Startup code:

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using MultipleCompany.Models;
using MultipleCompany.Services;

namespace MultipleCompany
{
    public class Startup
    {
        public Startup(IHostingEnvironment env)
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(env.ContentRootPath)
                .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
                .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true)
                .AddEnvironmentVariables();
            Configuration = builder.Build();
        }

        public IConfigurationRoot Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            // Add framework services.
            services.AddMvc();

            services.AddEntityFrameworkSqlServer().AddDbContext<CompanyDbContext>();

            services.AddScoped<IDbContextService, DbContextService>();

            services.AddDistributedMemoryCache();
            services.AddSession();

            services.AddOptions();

            services.Configure<AppSettings>(Configuration.GetSection("AppSettings"));

            services.AddSingleton<IConfiguration>(Configuration);
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
        {
            loggerFactory.AddConsole(Configuration.GetSection("Logging"));
            loggerFactory.AddDebug();

            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
                app.UseBrowserLink();
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
            }

            app.UseStaticFiles();

            app.UseSession();

            app.UseMvc(routes =>
            {
                routes.MapRoute(
                    name: "default",
                    template: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}

I've added this packages for my project:

"Microsoft.EntityFrameworkCore": "1.0.1",
"Microsoft.EntityFrameworkCore.SqlServer": "1.0.1",
"Microsoft.AspNetCore.Session":  "1.0.0"

My appsettings.json file:

{
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Debug",
      "System": "Information",
      "Microsoft": "Information"
    }
  },
  "AppSettings": {
    "CompanyConnectionString": "server=(local);database={database};user id={user id};password={password}"
  }
}

Please get focus on the concept about to connect to selected database in home view, you can change any part of this code as an improvement, please remember I'm providing this solution making some assumptions according to your brief question, please feel free to ask about any exposed aspect in this solution to improve this piece of code according to your requirements.

Basically, we need to define a service to create the instance of db context according to selected database, that's IDbContextService interface and DbContextService it's the implementation for that interface.

As you can see on DbContextService code, we replace the values inside of {} to build different connection string, in this case I've added the database names in drop down list but in real development please avoid this way because for security reasons it's better to don't expose the real names of your databases and other configurations; you can have a parity table from controller's side to resolve the company code according to selected database.

One improvement for this solution, it would be to add some code to serialize login model as json into session instead of store each value in separate way.

Please let me know if this answer is useful. PD: Let me know in comments if you want the full code to upload in one drive

Solution 3:

You found your answer but maybe my post can be helpful for someones. I had a similar problem like this question. I have had to change my entity framework connectionstring to connect different database server after user logged in. And for solution first I deleted this function from my context class,

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
                optionsBuilder.UseSqlServer("your connectionstring...");
            }

because I couldn't called this function from outside. And I had this auto generated constructor

 public ClientContext(DbContextOptions<ClientContext> options)
        : base(options)
    {
    }

After deletion, I added this code to my context class.

public ClientContext CreateConnectionFromOut(string connectionString)
{
    var optionsBuilder = new DbContextOptionsBuilder<Esdesk_ClientContext>();
    optionsBuilder.UseSqlServer(connectionString);
    var context = new ClientContext(optionsBuilder.Options);
    return context;
}

Now finally, I can change my connection string from wherever I want. It is just like that,

   ClientContext cc = new ClientContext();
   var db = cc.CreateConnectionFromOut("your connection string");

Hope this may be fine for someone.

Solution 4:

Since you are building a multi-tenant web application, you have to first decide how will you distinguish between tenants. Are you going to use differnent URL? or maybe the same URL but adding a part in the URL?

Assuming that you chose the latter, so tenant 1 would have a URL similar to this: http://localhost:9090/tenant1/orders

Tenant 2 would have a URL like: http://localhost:9090/tenant2/orders

You can do that using URL routing:

 routes.MapRoute(
                name: "Multitenant",
                url: "{tenant}/{controller}/{action}/{id}",
                defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
            );

As for the connection string, you need a class to decide the connection string based on the URL, and inject this class into the DB context.

public interface ITenantIdentifier
{
 string GetCurrentTenantId();
}

public class UrlTenantIdentifier : ITenantIdentifier
{
  public string GetCurrentTenantId()
  { 
    //Get the current Http Context and get the URL, you should have a table or configration that maps the URL to the tenant ID and connection string
  }
}

In your DB Context:

public class MyDbContext: DbContext
{
 public MyDbContext(ITenantIdentifier tenantIdentifier)
 { 
   var connectionStringName = "TenantConnectionString"+tenantIdentifier.GetCurrentTenantId(); //here assuming that you are following a pattern, each tenant has a connection string in the shape of TenantConnectionString+ID

  var connectionString = //get connection string
  base(connectionString);
 }
}