EF Core multiple database same schema

Question

Using EF Core .net 2.2.

Trying to have an app where there is a "live" database and a "test" database backing my app. Currently I publish multiple sites each with their own DBContexts and just before publishing I comment out and swap the code for the connection string/db in my startup.cs.

ex:

//services.AddDbContext<DataContext>(options =>
//    options.UseSqlServer(Configuration.GetConnectionString("TestDataContext")));

 services.AddDbContext<DataContext>(options =>
               options.UseSqlServer(Configuration.GetConnectionString("LiveDataContext")));

Then my two sites are

testdata.site.com and livedata.site.com

This works but it is time consuming and inefficient whenever updates are made to the site/controllers/views etc. Plus if i ever wanted more than two sites to share the same database schema, the publishing work required would compound even more.

Here is my ideal solution but I don't know how to accomplish it:

I want to send route data to the controller and have the controller decide the connection string when it does this portion of the controller:

private readonly POSContext _context;

    public CashierController(POSContext context)
    {
        _context = context;
    }

Example, the URL would be something like:

www.site.com/{test or live}/{controller}/{action}

Then a user could swap between the databases on the fly if needed.

I can work through the routing portion but I am really stuck on what to with the controller and startup database portion to make this work.

Anyone have an idea or can get me going on the right path?

score 1 · Answer 1 · answered Feb 15 '19 at 19:39

It all depends on how you publish your applications and what level of control you have on your hosting server. You can use multiple configuration files which have different connection string values, so instead of having two connection string names, you should have only one, for example, "MyAppConnectionString", and use environment based configuration files to override it when needed. To read more about configuration, visit: https://learn.microsoft.com/en-us/aspnet/core/fundamentals/configuration/?view=aspnetcore-2.2

Alternatively, you can use the hosting environment capability: https://learn.microsoft.com/en-us/aspnet/core/fundamentals/environments?view=aspnetcore-2.2

Please find some useful information in this answer as well: Automatically set appsettings.json for dev and release environments in asp.net core?

thank you. I will look into the hosting configuration portion as I have total control over everything related to the server, app etc — dave317, Feb 18 '19 at 04:22
Then this will help you a lot. Note that you can use the environment also locally in dev environment. When developing, for example i use appsettings.development.json, but when I run the integration and E2E tests I use appsettings.test.json which allow me to use an in memory database for optimal peformance. — Norcino, Feb 18 '19 at 10:56

score 0 · Accepted Answer · answered Mar 29 '19 at 19:29

This is what I ended up doing. I looked at what @Norcino said above and referenced the links in his post.

I created multiple Appsettings{DBIdentifier}.json files (still kept the usual appsettings.json file as well), ex appsettingsste3.json and in these JSON files I put a connection string, all with the same DB Name, but pointing to different DBs on my SQL server.

ex:

 {
      "ConnectionStrings": {
          "SiteDBContext":\\Connection string for unique DB, all with same schema/tables, etc\\
      }
  }

In my program.cs I created a function that looks at the current directory on my web server, since each site is in its own folder on my webserver (ex d:\inetpub\wwwsites\ste1, d:\inetpub\wwwsites\ste3, d:\inetpub\wwwsites\ste3), then take the last four characters of that string then run a switch statement on adding the extra json file.

The portion of program.cs that i modified looks like this:

public static IWebHost BuildWebHost(string[] args) =>
            WebHost.CreateDefaultBuilder(args)
            .ConfigureAppConfiguration((hostingContext, config) =>
            {
                var dirStr = Directory.GetCurrentDirectory().ToString(); //Gets path of directory into string
                var lastFour = dirStr.Substring(dirStr.Length - 4); //gets name of directory (last four characters in path)
                switch (lastFour)
                {
                    case "ste1":
                    case "ste2":
                    case "ste3":
                        string appStr = "appsettings" + lastFour.Substring(3) + ".json";
                        config.AddJsonFile(appStr, optional: false);
                        break;                                               
                }    
            })
                .UseStartup<Startup>()
                .Build();

Then of course, ConfigureServices in Startup.cs needs this:

services.AddDbContext<DataContext>(options =>
options.UseSqlServer(Configuration.GetConnectionString("SiteDBContext")));

Have not yet performed hard testing to know what performance will be like but i think it should be fine since program.cs only runs when the app is first started and so once the app is running there shouldn't be any performance degradation at all. (Am I right?)

EF Core multiple database same schema

2 Answers2