ASP.NET Core 2.0 Angular项目EF多连接串切换配置求助

Alright, let's tackle this problem step by step. You want users to switch their database connection string via an API and have all subsequent requests use that new string—here's how to make that work in your stack:

Core Concept #

The key here is to store the user's selected connection string in a user-scoped storage (like Session) and dynamically inject it into your DatabaseContext for each request. Since EF Core's DbContext is registered as a scoped service by default, each request gets its own instance, which lets us override the connection string per request.

Step 1: Update Your DatabaseContext #

Modify your context to pull the connection string from the current request's context instead of using a fixed configuration. We'll use IHttpContextAccessor to access the request context:

using Microsoft.AspNetCore.Http;
using Microsoft.EntityFrameworkCore;

public class DatabaseContext : DbContext
{
    private readonly IHttpContextAccessor _httpContextAccessor;
    private readonly string _defaultConnectionString;

    // Inject IHttpContextAccessor and default connection string from config
    public DatabaseContext(DbContextOptions<DatabaseContext> options, 
                          IHttpContextAccessor httpContextAccessor,
                          IConfiguration configuration)
        : base(options)
    {
        _httpContextAccessor = httpContextAccessor;
        _defaultConnectionString = configuration.GetConnectionString("DefaultConnection");
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // Check if we have a user-specific connection string in the request
        var userConnectionString = _httpContextAccessor.HttpContext?.Items["CurrentConnectionString"] as string;
        
        // Use user's string if available, else fall back to default
        var connectionString = string.IsNullOrEmpty(userConnectionString) 
            ? _defaultConnectionString 
            : userConnectionString;

        optionsBuilder.UseSqlServer(connectionString);
        base.OnConfiguring(optionsBuilder);
    }

    // Your DbSet definitions go here
    // public DbSet<YourEntity> YourEntities { get; set; }
}

Step 2: Register Required Services in Startup.cs #

We need to register IHttpContextAccessor (it's not added by default) and enable Session to persist the connection string across requests:

public void ConfigureServices(IServiceCollection services)
{
    // Add HttpContextAccessor to access request context in DbContext
    services.AddHttpContextAccessor();
    
    // Add DbContext with default connection string
    services.AddDbContext<DatabaseContext>(options => 
        options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
    
    // Enable Session for storing user-specific connection strings
    services.AddSession(options =>
    {
        options.IdleTimeout = TimeSpan.FromHours(2); // Adjust timeout as needed
        options.Cookie.HttpOnly = true;
        options.Cookie.IsEssential = true;
    });

    // Add other services (Mvc, AutoMapper, etc.)
    services.AddMvc();
    services.AddAutoMapper(typeof(Startup));
}

public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
    // ... other middleware (like error handling, static files)
    
    // Session middleware must come before Mvc
    app.UseSession();
    
    // Add a custom middleware to load the connection string from Session into HttpContext.Items
    app.UseMiddleware<ConnectionStringLoaderMiddleware>();
    
    app.UseMvc();
}

Step 3: Create the Connection String Loader Middleware #

This middleware will pull the connection string from Session and store it in HttpContext.Items for the current request—so your DatabaseContext can access it easily:

public class ConnectionStringLoaderMiddleware
{
    private readonly RequestDelegate _next;

    public ConnectionStringLoaderMiddleware(RequestDelegate next)
    {
        _next = next;
    }

    public async Task InvokeAsync(HttpContext context)
    {
        // Get the stored connection string from Session
        var storedConnectionString = context.Session.GetString("UserConnectionString");
        
        if (!string.IsNullOrEmpty(storedConnectionString))
        {
            // Store it in HttpContext.Items for the DbContext to pick up
            context.Items["CurrentConnectionString"] = storedConnectionString;
        }

        // Pass the request to the next middleware
        await _next(context);
    }
}

Step 4: Implement the ChangeDatabase API in AdminController #

Update your controller to validate and store the new connection string in Session. We don't need to inject DatabaseContext here since we're just setting the string:

using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System.Data.SqlClient;

[Produces("application/json")]
[Route("api/admin")]
public class AdminController : Controller 
{
    private readonly IMapper _mapper;

    public AdminController(IMapper mapper)
    {
        _mapper = mapper;
    }

    [HttpPost("change-database")]
    public IActionResult ChangeDatabase([FromBody] string databaseConnectionString)
    {
        // Validate input
        if (string.IsNullOrWhiteSpace(databaseConnectionString))
        {
            return BadRequest("Connection string cannot be empty or whitespace.");
        }

        // Optional: Validate the connection string works before storing it
        try
        {
            using (var connection = new SqlConnection(databaseConnectionString))
            {
                connection.Open();
            }
        }
        catch (Exception ex)
        {
            return BadRequest($"Invalid connection string: {ex.Message}");
        }

        // Store the valid connection string in Session
        HttpContext.Session.SetString("UserConnectionString", databaseConnectionString);
        return Ok("Database connection switched successfully.");
    }

    // Optional: Add a reset endpoint to revert to default connection
    [HttpPost("reset-database")]
    public IActionResult ResetDatabase()
    {
        HttpContext.Session.Remove("UserConnectionString");
        return Ok("Database connection reset to default.");
    }
}

Key Notes & Best Practices #

• Session Isolation: Session is user-specific, so each user will have their own connection string—perfect for your use case.
• Security: Connection strings contain sensitive data (like SQL credentials). Consider encrypting the string before storing it in Session, and use HTTPS for all API calls.
• Production Session Storage: By default, Session uses in-memory storage, which isn't suitable for multi-server deployments. For production, configure a distributed cache (like Redis) to store Session data.
• Connection String Validation: Validating the connection string in the API prevents invalid strings from causing errors in subsequent requests.

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