用户登录时ASP.NET Core更改EF连接字符串 [英] ASP.NET Core change EF connection string when user logs in

问题描述

经过几个小时的研究，并没有找到解决办法；是时候问这个问题了.

After a few hours of research and finding no way to do this; it's time to ask the question.

我有一个使用EF Core和MVC的ASP.NET Core 1.1项目，该项目由多个客户使用.每个客户都有其自己的数据库，它们具有完全相同的架构.目前，该项目是一个正在迁移到Web的Windows应用程序.在登录屏幕上，用户具有三个字段，公司代码，用户名和密码.当用户尝试根据他们在公司代码"输入中键入的内容进行登录时，我需要能够更改连接字符串，然后在整个会话持续时间内记住他们的输入.

I have an ASP.NET Core 1.1 project using EF Core and MVC that is used by multiple customers. Each customer has their own database with the exact same schema. This project is currently a Windows application being migrated to the web. At the login screen the user has three fields, Company Code, Username and Password. I need to be able to change the connection string when the user attempts to login based on what they type in the Company Code input then remember their input throughout the session duration.

我发现一些方法可以对一个数据库和多个架构执行此操作，但是对于使用同一模式的多个数据库则无法实现.

I found some ways to do this with one database and multiple schema, but none with multiple databases using the same schema.

我解决此问题的方法不是解决问题的实际方法，而是解决该问题的方法.我的数据库和应用程序托管在Azure上.我的解决方法是将我的应用程序服务升级到支持插槽的计划(5个插槽每月仅需额外支付20美元).每个插槽都有相同的程序，但是保存连接字符串的环境变量是公司特定的.这样，如果需要，我还可以对每个公司的访问进行子域访问.尽管这种方法可能不是别人会做的，但对我而言，这是最具成本效益的.发布到每个广告位比花几个小时进行其他无法正常工作的编程要容易得多.在Microsoft轻松更改连接字符串之前，这就是我的解决方案.

The way I solved this problem isn't an actual solution to the problem, but a work around that worked for me. My databases and app are hosted on Azure. My fix to this was to upgrade my app service to a plan that supports slots (only an extra $20 a month for 5 slots). Each slot has the same program but the environment variable that holds the connection string is company specific. This way I can also subdomain each companies access if I want. While this approach may not be what others would do, it was the most cost effective to me. It is easier to publish to each slot than to spend the hours doing the other programming that doesn't work right. Until Microsoft makes it easy to change the connection string this is my solution.

对Herzl的回答

这似乎可行.我试图将其实施.我正在做的一件事是使用访问我的上下文的存储库类.我的控制器将存储库注入其中，以调用存储库中访问上下文的方法.如何在存储库类中执行此操作.我的存储库中没有OnActionExecuting重载.另外，如果会话仍然存在，那么当用户再次打开浏览器访问应用程序并仍然使用持续7天的Cookie登录时，会发生什么情况?这不是新的会话吗?听起来该应用程序会抛出异常，因为会话变量将为null，因此没有完整的连接字符串.我想我也可以将其存储为Claim，如果会话变量为null，则可以使用Claim.

This seems like it could work. I have tried to get it implemented. One thing I am doing though is using a repository class that accesses my context. My controllers get the repository injected into them to call methods in the repository that access the context. How do I do this in a repository class. There is no OnActionExecuting overload in my repository. Also, if this persists for the session, what happens when a user opens their browser to the app again and is still logged in with a cookie that lasts 7 days? Isn't this a new session? Sounds like the app would throw an exception because the session variable would be null and therefor not have a complete connection string. I guess I could also store it as a Claim and use the Claim if the session variable is null.

这是我的存储库类. IDbContextService是ProgramContext，但我开始添加您的建议以尝试使其正常工作.

Here is my repository class. IDbContextService was ProgramContext but I started adding your suggestions to try and get it to work.

public class ProjectRepository : IProjectRepository
{
    private IDbContextService _context;
    private ILogger<ProjectRepository> _logger;
    private UserManager<ApplicationUser> _userManager;

    public ProjectRepository(IDbContextService context,
                            ILogger<ProjectRepository> logger,
                            UserManager<ApplicationUser> userManger)
    {
        _context = context;
        _logger = logger;
        _userManager = userManger;
    }

    public async Task<bool> SaveChangesAsync()
    {
        return (await _context.SaveChangesAsync()) > 0;
    }
}

对FORCE JB的回答

我尝试实施您的方法.我在Program.cs中遇到了异常

I tried to implement your approach. I get an exception in Program.cs on line

host.Run();

这是我的"Program.cs"类.保持不变.

Here is my 'Program.cs' class. Untouched.

using System.IO;
using Microsoft.AspNetCore.Hosting;

namespace Project
{
    public class Program
    {
        public static void Main(string[] args)
        {
            var host = new WebHostBuilder()
                .UseKestrel()
                .UseContentRoot(Directory.GetCurrentDirectory())
                .UseIISIntegration()
                .UseStartup<Startup>()
                .Build();

            host.Run();
        }
    }
}

还有我的'Startup.cs'类.

And my 'Startup.cs' class.

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Logging;
using Newtonsoft.Json.Serialization;
using System;
using System.Collections.Generic;
using Project.Entities;
using Project.Services;

namespace Project
{
    public class Startup
    {
        private IConfigurationRoot _config;

        public Startup(IHostingEnvironment env)
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(env.ContentRootPath)
                .AddJsonFile("appsettings.json")
                .AddEnvironmentVariables();

            _config = builder.Build();
        }

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddSingleton(_config);
            services.AddIdentity<ApplicationUser, IdentityRole>(config =>
            {
                config.User.RequireUniqueEmail = true;
                config.Password.RequireDigit = true;
                config.Password.RequireLowercase = true;
                config.Password.RequireUppercase = true;
                config.Password.RequireNonAlphanumeric = false;
                config.Password.RequiredLength = 8;
                config.Cookies.ApplicationCookie.LoginPath = "/Auth/Login";
                config.Cookies.ApplicationCookie.ExpireTimeSpan = new TimeSpan(7, 0, 0, 0); // Cookies last 7 days
            })
            .AddEntityFrameworkStores<ProjectContext>();
            services.AddScoped<IUserClaimsPrincipalFactory<ApplicationUser>, AppClaimsPrincipalFactory>();
            services.AddScoped<IProjectRepository, ProjectRepository>();
            services.AddTransient<MiscService>();
            services.AddLogging();
            services.AddMvc()
            .AddJsonOptions(config =>
            {
                config.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();
            });
        }

        public void Configure(IApplicationBuilder app, ILoggerFactory loggerFactory)
        {
            Dictionary<string, string> connStrs = new Dictionary<string, string>();
            connStrs.Add("company1", "1stconnectionstring"));
            connStrs.Add("company2", "2ndconnectionstring";
            DbContextFactory.SetDConnectionString(connStrs);
            //app.UseDefaultFiles();

            app.UseStaticFiles();
            app.UseIdentity();
            app.UseMvc(config =>
            {
                config.MapRoute(
                    name: "Default",
                    template: "{controller}/{action}/{id?}",
                    defaults: new { controller = "Auth", action = "Login" }
                    );
            });
        }
    }
}

还有一个例外:

InvalidOperationException: Unable to resolve service for type 'Project.Entities.ProjectContext' while attempting to activate 'Microsoft.AspNetCore.Identity.EntityFrameworkCore.UserStore`4[Project.Entities.ApplicationUser,Microsoft.AspNetCore.Identity.EntityFrameworkCore.IdentityRole,Project.Entities.ProjectContext,System.String]'.

不确定在这里做什么.

部分成功编辑

好吧，我让你的例子行之有效.我可以在存储库构造函数中使用其他ID设置连接字符串.我现在的问题是登录并选择正确的数据库.我考虑过让存储库从会话或声明中拉出，无论什么都不为空.但是在登录控制器中使用SignInManager之前，我无法设置该值，因为SignInManager已注入到控制器中，该控制器会在更新会话变量之前创建上下文.我能想到的唯一方法是进行两页登录.第一页将询问公司代码并更新会话变量.第二页将使用SignInManager并将存储库注入到控制器构造函数中.这将在第一页更新会话变量之后发生.实际上，两个登录视图之间的动画在视觉上都更具吸引力.除非有人对没有两个登录视图的方法有任何想法，否则我将尝试实现两页登录并在可行的情况下发布代码.

Okay I got your example working. I can set the connection string in my repository constructor using a different id. My problem now is logging in and choosing the right database. I thought about having the repository pull from a session or claim, whatever wasn't null. But I can't set the value before using the SignInManager in the Login controller because SignInManager is injected into the controller which creates a context before I update the session variable. The only way I can think of is to have a two page login. The first page will ask for the company code and update the session variable. The second page will use the SignInManager and have the repository injected into the controllers constructor. This would happen after the first page updates the session variable. This may actually be more visually appealing with animations between both login views. Unless anyone has any ideas on a way to do this without two login views I am going to try and implement the two page login and post the code if it works.

它实际上已损坏

当它正常工作时，是因为我还有一个有效的cookie.我将运行该项目，并且将跳过登录.现在，清除缓存后，出现异常InvalidOperationException: No database provider has been configured for this DbContext.我已逐步完成所有操作，并且上下文已正确创建.我的猜测是，身份信息存在某种问题.下面的在ConfigureServices中添加实体框架存储的代码是否会引起此问题?

When it was working, it is because I still had a valid cookie. I would run the project and it would skip the login. Now I get the exception InvalidOperationException: No database provider has been configured for this DbContext after clearing my cache. I have stepped through it all and the context is being created correctly. My guess is that Identity is having some sort of issues. Could the below code adding the entity framework stores in ConfigureServices be causing the issue?

services.AddIdentity<ApplicationUser, IdentityRole>(config =>
{
    config.User.RequireUniqueEmail = true;
    config.Password.RequireDigit = true;
    config.Password.RequireLowercase = true;
    config.Password.RequireUppercase = true;
    config.Password.RequireNonAlphanumeric = false;
    config.Password.RequiredLength = 8;
    config.Cookies.ApplicationCookie.LoginPath = "/Company/Login";
    config.Cookies.ApplicationCookie.ExpireTimeSpan = new TimeSpan(7, 0, 0, 0); // Cookies last 7 days
})
.AddEntityFrameworkStores<ProgramContext>();

编辑

我验证了Identity是问题.我在执行PasswordSignInAsync之前从存储库中提取了数据，它也很好地提取了数据.如何为身份创建DbContext?

I verified Identity is the problem. I pulled data from my repository before executing PasswordSignInAsync and it pulled the data just fine. How is the DbContext created for Identity?

推荐答案

创建DbContext工厂

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");
        }
    }
}

初始化DbContext工厂

在startup.cs

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);
}

用法

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

这篇关于用户登录时ASP.NET Core更改EF连接字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！