Is it possible to use one database to dynamically define the ConnectionString of another?

asp.net-core-2.0 dbcontext entity-framework-core

Question

I've reached a bit of a brick-wall with my current project.

I have three normalised databases, one of which I want to dynamically connect to; these are:

  • Accounts: For secure account information, spanning clients
  • Configuration: For managing our clients
  • Client: Which will be atomic for each of our clients & hold all of their information

I need to use data stored in the "Configuration" database to modify the ConnectionString that will be used to connect to the "Client" database, but this is the bit I'm getting stuck on.

So far I've generated the entities from the databases into a project by hooking up EntityFrameWorkCore Tools and using the "Scaffold-DbContext" command & can do simple look-ups to make sure that the databases are being connected to okay.

Now I'm trying to register the databases by adding them to the ServiceCollection, I have them added in the StartUp class as follows:

    // This method gets called by the runtime. Use this method to add services to the container.
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddMvc();
        services.Configure<MvcOptions>(options =>
        {
            options.Filters.Add(new RequireHttpsAttribute());
        });
        services.AddDbContext<Accounts>( options =>
            options.UseSqlServer(Configuration.GetConnectionString("Accounts"))
        );
        services.AddDbContext<Support>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("Configuration"))
        );

        // Erm?
        SelectClientDatabase(services);
    }

Obviously the next stage is to dip into the "Configuration" database, so I've been trying to keep that contained in "SelectClientDatabase()", which just takes the IServiceCollection as a parameter and is for all intents and purposes empty for now. Over the last few days I've found some excellent write-ups on EFC and I'm currently exploring a CustomConfigurationProvider as a possible route, but I must admit I'm a little lost on starting out in ASP.Net Core.

Is it possible to hook into the freshly added DbContext within the ConfigureServices method? Or can/must I add this database to the service collection at a later point?

Thanks!

Edit 1:

I just found this post, which mentions that a DbContext cannot be used within OnConfiguring as it's still being configured; which makes a lot of sense. I'm now wondering if I can push all three DbContexts into a custom middleware to encapsulate, configure and make the connections available; something new to research.

Edit 2: I've found another post, describing how to "Inject DbContext when database name is only know when the controller action is called" which looks like a promising starting point; however this is for an older version of ASP.Net Core, according to https://docs.microsoft.com "DbContextFactory" has been renamed so I'm now working to update the example given into a possible solution.

1
1
11/7/2017 1:50:09 PM

Accepted Answer

So, I've finally worked it all out. I gave up on the factory idea as I'm not comfortable enough with to spend time working it out & I'm rushing head-long into a deadline so the faster options are now the better ones and I can always find time to refactor the code later (lol).

My appsettings.json file currently just contains the following (the relevant bit of appsettings.Developments.json is identical):

{
    "ConnectionStrings" : {
        "Accounts": "Server=testserver;Database=Accounts;Trusted_Connection=True;",
        "Client": "Server=testserver;Database={CLIENT_DB};Trusted_Connection=True;",
        "Configuration": "Server=testserver;Database=Configuration;Trusted_Connection=True;"
    },
    "Logging": {
        "IncludeScopes": false,
        "Debug": {
            "LogLevel": {
                "Default": "Warning"
            }
        },
        "Console": {
            "LogLevel": {
                "Default": "Warning"
            }
        }
    }
}

I've opted to configure the two static databases in the ConfigureServices method of StartUp, these should be configured and ready to use by the time the application gets around to having to do anything. The code there is nice & clean.

public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc();
    services.Configure<MvcOptions>(options =>
    {
        //options.Filters.Add(new RequireHttpsAttribute());
    });
    services.AddDbContext<AccountsContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("Accounts"))
    );
    services.AddDbContext<ConfigContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("Configuration"))
    );
    services.AddSingleton(
        Configuration.GetSection("ConnectionStrings").Get<ConnectionStrings>()
    );
}

It turns out that one can be spoilt for choice in how to go about accessing configuration options set in the appsettings.json, I'm currently trying to work out how I've managed to get it to switch to the release version instead of the development one. I can't think what I've done to toggle that...

To get the placeholder config setting I'm using a singleton to hold the string value. This is just dipping into the "ConnectionStrings" group and stuffing that Json into the "ClientConnection" object (detailed below).

    services.AddSingleton(
        Configuration.GetSection("ConnectionStrings").Get<ClientConnection>()
    );

Which populates the following structure (that I've just bunged off in its own file):

[DataContract(Name = "ConnectionStrings")]
public class ClientConnection
{
    [DataMember]
    public string Client { get; set; }
}

I only want this holding the connection string for the dynamically assigned database, so it's not too jazzy. The "Client" DataMember is what is selecting the correct key in the Json, if I wanted a different named node in the Json I'd rename it to "Accounts", for instance.

Another couple of options I tested, before settling on the Singleton option, are:

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

and

var derp = Configuration.GetSection("ConnectionStrings:Client");

Which I discounted, but it's worth knowing other options (they'll probably be useful for loading other configuration options later).

I'm not keen on the way the Controller dependencies work in ASP.Net Core 2, I was hoping I'd be able to hide them in a BaseController so they wouldn't have to be specified in every single Controller I knock out, but I've not found a way to do this yes. The dependencies needed in the Controllers are passed in the constructor, these weirded me out for a while because they're auto-magically injected.

My BaseController is set up as follows:

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Filters;
using Microsoft.EntityFrameworkCore.Internal;
using ServiceLayer.Entities;
using System;
using System.Collections.Generic;
using System.Linq;

namespace ServiceLayer.Controllers
{
    public class BaseController : Controller
    {
        private readonly ClientConnection connectionStrings;
        private readonly AccountsContext accountsContext;
        private readonly ConfigurationContext configContext;
        public ClientTemplateContext clientContext;

        private DbContextServices DbContextServices { get; set; }

        public BaseController(AccountsContext accounts, ConfigContext config, ClientConnection connection) : base()
        {
            accountsContext = accounts;
            configContext = config;
            connectionStrings = connection;
        }

        public override void OnActionExecuting(ActionExecutingContext context)
        {
            base.OnActionExecuting(context);
        }
    }
}

The code for selecting the database then goes in the "OnActionExecuting()" method; this proved to be a bit of a pain as well, trying to ensure that the was set up properly, in the end I settled on:

using System;
using System.Collections.Generic;
using System.Linq;

namespace ServiceLayer.Controllers
{
    public class BaseController : Controller
    {
        private readonly ClientConnection connectionStrings;
        private readonly AccountsContext accountsContext;
        private readonly ConfigurationContext configContext;
        public ClientTemplateContext clientContext;

        private DbContextServices DbContextServices { get; set; }

        public BaseController(AccountsContext accounts, ConfigurationContext config, ClientConnection connection) : base()
        {
            accountsContext = accounts;
            configContext= config;
            connectionStrings = connection;
        }

        public override void OnActionExecuting(ActionExecutingContext context)
        {
            // Temporary selection identifier for the company
            Guid cack = Guid.Parse("827F79C5-821B-4819-ABB8-819CBD76372F");

            var dataSource = (from c in configContext.Clients
                              where c.Cack == cack
                              join ds in configContext.DataStorage on c.CompanyId equals ds.CompanyId
                              select ds.Name).FirstOrDefault();

            // Proto-connection string
            var cs = connectionStrings.Client;

            if (!string.IsNullOrEmpty(cs) && !string.IsNullOrEmpty(dataSource))
            {
                // Populated ConnectionString
                cs = cs.Replace("{CLIENT_DB}", dataSource);

                clientContext = new ClientTemplateContext().Initialise(cs);
            }

            base.OnActionExecuting(context);
        }
    }
}

new ClientTemplateContext().Initialise() is a bit messy but I'll clean it up when I refactor everything else. "ClientTemplateContext" is the generated class that ties together all the entities it generated, I've added the following code to that class (I did try putting it in a separate file but couldn't get that working, so it's staying in there for the moment)...

public ClientTemplateContext() {}

private ClientTemplateContext(DbContextOptions options) : base(options) {}

public ClientTemplateContext Initialise(string connectionString)
{
    return new ClientTemplateContext().CreateDbContext(new[] { connectionString });
}

public ClientTemplateContext CreateDbContext(string[] args)
{
    if (args == null && !args.Any())
    {
        //Log error.
        return null;
    }

    var optionsBuilder = new DbContextOptionsBuilder<ClientTemplateContext>();

    optionsBuilder.UseSqlServer(args[0]);

    return new ClientTemplateContext(optionsBuilder.Options);
}

I also included using Microsoft.EntityFrameworkCore.Design; and added the IDesignTimeDbContextFactory<ClientTemplateContext> interface to the class. So it looks like this:

public partial class ClientTemplateContext : DbContext, IDesignTimeDbContextFactory<ClientTemplateContext>

This is where the CreateDbContext(string[] args) comes from & it allows us to create a new instance of a derived context at design-time.

Finally, the code for my test controller is as follows:

using Microsoft.AspNetCore.Mvc;
using ServiceLayer.Entities;
using System.Collections.Generic;
using System.Linq;

namespace ServiceLayer.Controllers
{
    [Route("api/[controller]")]
    public class ValuesController : BaseController
    {
        public ValuesController(
            AccountsContext accounts,
            ConfigurationContext config,
            ClientConnection connection
        ) : base(accounts, config, connection) {}

        // GET api/values
        [HttpGet]
        public IEnumerable<string> Get()
        {
            var herp = (from c in clientContext.Usage
                        select c).FirstOrDefault();

            return new string[] {
                herp.TimeStamp.ToString(),
                herp.Request,
                herp.Payload
            };
        }
    }
}

This successfully yields data from the database dynamically selected from the DataSource table within the Configuration database!

["01/01/2017 00:00:00","derp","derp"]

If anyone can suggest improvements to my solution I'd love to see them, my solution is mashed together as it stands & I want to refactor it as soon as I feel I'm competent enough to do so.

1
11/7/2017 12:03:36 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow