Easily switching between connection strings in .NET Core

Question

I've got a code base that uses EF Core and Dapper to perform actions on a database. I want to set up a new copy of the site to develop some features and I want this site to connect to a new isolated copy of the database (dbConnectionDEBUG).

At the moment, I use the following setup:

startup.cs

...
services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(
        Configuration.GetConnectionString("dbConnectionMain")));

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

I have a ConnectionStrings class which is being populated correctly via the DI in startup:

public class ConnectionStrings
{
    public string dbConnectionMain { get; set; }
    public string dbConnectionDEBUG { get; set; }

    public ConnectionStrings()
    {
        this.dbConnectionMain = "";
        this.dbConnectionDEBUG = "";
    }
}

Then, throughout my controllers/services I have access to ConnectionStrings and 99% of the time I'm doing the following to make DB calls:

using (var conn = new SqlConnection(_connectionStrings.dbConnectionMain))
{
    conn.Open();
    ...

This would amount to a lot of code changes if I were to want to switch over to the 'DEBUG' db.

How do I easily switch between the connection strings in my code depending on what version of the system I'm working on. If I could somehow do this dynamically that'd be great. The obvious determining factor would be the URL the site is operating on.

Alternatively, (as a single change) do I just manually change the connection string at the source (e.g keystore/appsettings). I'm not keen on this as it leaves room for human error.

---

Update (2)

Based on what @Nkosi mentioned I am pursuing this path:

• Have one connection string 'Id' (i.e. dbConnection) used throughout
• Differentiate the connection string value within this based on the environment the app is running/deployed in

I have another question:

If I have the following...

"MYAPPNAME": {
  "commandName": "Project",
  "launchBrowser": true,
  "environmentVariables": {
    "ASPNETCORE_ENVIRONMENT": "Development"
  },
  "applicationUrl": "http://localhost:12345/;https://myapptestdomain.com/"
}

and:

WebHost.CreateDefaultBuilder(args)
    .ConfigureAppConfiguration((context, config) =>
    {
        IHostingEnvironment env = context.HostingEnvironment;

        config.AddJsonFile($"appsettings.{env.EnvironmentName.ToLower()}.json", optional: true);
        
    })
    .UseStartup<Startup>();

...will this automatically pick up my site is in the Development mode based on the applicationUrl values OR will I have to manually add ASPNETCORE_ENVIRONMENT with a value Development on the server I deploy the app to?

Additional: My app is running in an Azure App Service.

---

Update (3) - Mission Complete

Just to finalise this question (in case anyone needs to know this), I have the following setup based on recommendations made by @Nkosi.

• Connection String - I have one connection string Id/name dbConnection which is used in all appSettings (see below)
• App Settings
  • I have a default appSettings.json with dbConnection that looks at the live database
  • I have an additional appSettings.Playground.json file with dbConnection that looks at my testing database
• Azure - App Service - On my playground development slot I have added an App Setting for ASPNETCORE_ENVIRONMENT with the value 'Playground'
• In my Program.cs file I have:

config.AddJsonFile($"appsettings.json", optional: true,reloadOnChange: true);

and

config.AddJsonFile($"appsettings.{env.EnvironmentName.ToLower()}.json", optional: true,reloadOnChange: true);

Just to note, I do also initialise a Vault on Azure which stores all my Keys and Secrets for the Azure based apps. Locally User Secrets is used.

Answer 1

ASP.NET Core reads the environment variable ASPNETCORE_ENVIRONMENT at app startup and stores the value in IHostingEnvironment.EnvironmentName.

Since the environment is being loaded, then it should be available from the hosting environment via the builder context

WebHost.CreateDefaultBuilder(args)
    .ConfigureAppConfiguration((context, config) => {
        string environment = context.HostingEnvironment.EnvironmentName; //get current environment

        //load config based on environment.
        config.AddJsonFile($"appsettings.{environment}.json", optional: true);
        //...
    })
    //...

Reference Use multiple environments in ASP.NET Core