如何在ASP.NET MVC中动态更改连接字符串实现多租户分库

Hey there! Let's walk through exactly how to make this work—you're already halfway there with your shared user/company database, so we just need to tie the login flow to dynamic DbContext switching.

Core Idea #

The key steps are straightforward:

• Capture the user's associated company name after successful login and store it where every subsequent request can access it.
• Modify your DbContext to dynamically load the connection string for that company's database when it initializes.

Step 1: Store Company Name in User Claims (Post-Login) #

When a user logs in, after validating their credentials, fetch their company name from your shared user database and add it to their authentication claims. This makes it available in every request context.

Here's an example with ASP.NET Core Identity (adjust to your auth system if needed):

public async Task<IActionResult> Login(LoginViewModel model)
{
    if (ModelState.IsValid)
    {
        var user = await _userManager.FindByEmailAsync(model.Email);
        if (user != null && await _userManager.CheckPasswordAsync(user, model.Password))
        {
            // Fetch company name from your shared database
            var companyName = await _sharedDbContext.UserCompanies
                .Where(uc => uc.UserId == user.Id)
                .Select(uc => uc.CompanyName)
                .FirstOrDefaultAsync();

            // Add company name to claims
            var claims = new List<Claim>
            {
                new Claim(ClaimTypes.NameIdentifier, user.Id),
                new Claim("CompanyName", companyName ?? throw new InvalidOperationException("User has no associated company"))
            };

            var identity = new ClaimsIdentity(claims, CookieAuthenticationDefaults.AuthenticationScheme);
            await HttpContext.SignInAsync(CookieAuthenticationDefaults.AuthenticationScheme, new ClaimsPrincipal(identity));

            return RedirectToAction("Index", "Home");
        }
    }
    ModelState.AddModelError("", "Invalid login attempt");
    return View(model);
}

Step 2: Build a Dynamic Tenant DbContext #

Modify your DbContext (or create a base class) to pull the company name from the current request's claims, then build the corresponding database connection string.

We'll use IHttpContextAccessor to access the current user's claims, and a connection string template from your config (so you don't hardcode server details).

public class TenantDbContext : DbContext
{
    private readonly IHttpContextAccessor _httpContextAccessor;
    private readonly string _connectionStringTemplate;

    // Inject dependencies
    public TenantDbContext(
        DbContextOptions<TenantDbContext> options,
        IHttpContextAccessor httpContextAccessor,
        IConfiguration configuration) : base(options)
    {
        _httpContextAccessor = httpContextAccessor;
        _connectionStringTemplate = configuration.GetConnectionString("TenantDatabaseTemplate");
    }

    // Override OnConfiguring to set dynamic connection string
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            var companyName = _httpContextAccessor.HttpContext?.User.FindFirstValue("CompanyName");
            
            if (string.IsNullOrEmpty(companyName))
            {
                throw new UnauthorizedAccessException("No company identifier found for the current user.");
            }

            // Replace placeholder in template with company name to get full connection string
            var tenantConnectionString = string.Format(_connectionStringTemplate, companyName);
            optionsBuilder.UseSqlServer(tenantConnectionString); // Swap with your DB provider if needed
        }
    }

    // Add your DbSets here (e.g., public DbSet<Order> Orders { get; set; })
}

Step 3: Register Services in Startup/Program.cs #

Make sure to register the IHttpContextAccessor and your TenantDbContext with the DI container:

// For .NET 6+ Program.cs
builder.Services.AddHttpContextAccessor();
builder.Services.AddDbContext<TenantDbContext>();

Key Considerations #

• Database Existence Checks: Add logic to verify the company's database exists before trying to connect (throw a friendly error or redirect if not).
• Connection String Caching: If you have many tenants, cache built connection strings to avoid formatting the template on every request.
• Schema Consistency: Ensure all tenant databases have the same schema. Use EF Core migrations to generate a schema script, then run it for each new tenant's database when they sign up.
• Unauthenticated Requests: Add middleware to block requests that don't have a valid CompanyName claim (unless you have public endpoints).

That's it! Now every authenticated request will automatically switch to the user's company-specific database via the DbContext.

内容的提问来源于stack exchange，提问作者akyj