Handling multiple connection strings in asp.net co

Question

In one my application I came across in a situation where user has to select the database needs to be used from the front-end.

And then in every request the selected value is passing as a parameter as all are web api calls and the connection needs to be made based on the selected database.

So currently I am writing the code for initializing the connection in every action method. like :-

public async Task<IActionResult> GetData([FromQuery]string Id, [FromQuery]string database)
{
    if(database=="A")
       connection conn=new connection("connStringA");//dummy code
    if(database=="B")
       connection conn=new connection("connStringB");//dummy code
    // and so on the logic......... 
}

I can create a separate method also which will do same thing for me but again I do need have to call that method in each time for all the actions.

Here also I have a constructor with DI.

My question is is there any other better way of doing this without writing/calling in each action. I believe there should be but I am unable to get through it.

What I am looking for like:-

• Any way to have this by injecting through DI.
• Any way to initialize by constructor.
• Any way by action filters.
• Or any better approach.

Answer 1

I recommend creating a DbContextProvider class which has a method that creates a connection string for the db name. Then register it scoped in the DI and use an action filter to populate a property of the Controller.

That way the db selection logic is separated from the request and can be used (and tested) outside of a web application. Also you have the option to prevent the action from being called if an invalid db name is provided (within the action filter).

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }
}

public class AppDbContextProvider : IDisposable
{
    //enforcing 1 DbContext per request
    private Dictionary<string, AppDbContext> _contexts = new Dictionary<string, AppDbContext>();

    public AppDbContext GetDbContext(string dbName)
    {
        if (dbName == null)
            return null;
        if (_contexts.TryGetValue(dbName, out AppDbContext ctx))
            return ctx;

        var conStr = GetConnectionString(dbName);
        if (conStr == null)
            return null;

        var dbOptionsBuilder = new DbContextOptionsBuilder<AppDbContext>();
        dbOptionsBuilder.UseSqlServer(conStr);
        ctx = new AppDbContext(dbOptionsBuilder.Options);
        _contexts[dbName] = ctx;
        return ctx;
    }

    //Any connection string selection logic, either hard-coded or configurable somewhere (e.g. Options).
    private string GetConnectionString(string dbName)
    {
        switch (dbName)
        {
            case "A":
                return "a";

            case "B":
                return "b";

            default:
                return null;
        }
    }

    //ensure clean dispose after DI scope lifetime
    public void Dispose()
    {
        if (_contexts.Count > 0)
        {
            foreach (var ctx in _contexts.Values)
                ctx.Dispose();
        }
    }
}

public class PopulateDbContextFilter : ActionFilterAttribute
{
    public override void OnActionExecuting(ActionExecutingContext filterContext)
    {
        var dbName = filterContext.HttpContext.Request.Query["db"];
        var provider = filterContext.HttpContext.RequestServices.GetRequiredService<AppDbContextProvider>();
        var ctx= provider.GetDbContext(dbName);
        if (ctx == null)
        {
            filterContext.Result = new RedirectToRouteResult(new RouteValueDictionary(new { controller = "Home", action = "Error" }));
        }else
        {
            //could also be stored to any other accessible location (e.g. an controller property)
            filterContext.HttpContext.Items["dbContext"] = ctx;
        }
        base.OnActionExecuting(filterContext);
    }
}

Then finally add the AppDbContextProvider to the application DI as a scoped service. services.AddScoped<AppDbContextProvider>();

This also allows using the same provider in background jobs or cases where you have to access multiple databases. However you can't directly inject the DbContext by DI anymore.

If you require migrations, you might have to look into Design-time DbContext creation as well: https://docs.microsoft.com/en-us/ef/core/miscellaneous/cli/dbcontext-creation