Easily switching between connection strings in .NET Core

Question

Easily switching between connection strings in .NET Core

asp.net-core asp.net-mvc dapper sql-server

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.

scgough

Popular Answer

I think if you add 2 connection for debug and main then you will have face some difficulty because more member working in you team. may be some own wrongly use release mode for code development.

you can try this webconfig method:

public class ConnectionStrings { public string dbConnection { get; set; }

    public ConnectionStrings()
    {
        bool Ismain = bool.Parse(System.Configuration.ConfigurationManager.AppSettings["HasLive"]);
        if (Ismain)
        {
            dbConnection = "";// get Main connectionstring
        }
        else
        {
            dbConnection = "";// get Debug connectionstring
        }

    }
}

web.config:

<connectionStrings>
  <add name="dbConnectionMain" connectionString="" providerName="System.Data.SqlClient" />
  <add name="dbConnectionDEBUG" connectionString="" roviderName="System.Data.SqlClient" />
</connectionStrings>
<appSettings>
  <add key="HasLive" value="false"/>  
</appSettings>
</connectionStrings>

Neo Vijay

View more on Stack Overflow

Nkosi · Accepted Answer · 11/28/2019 3:00:57 PM

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);
        //...
    })
    //...

Easily switching between connection strings in .NET Core

Question

Update (2)

Update (3) - Mission Complete

Fastest Dapper Plus Extensions

Accepted Answer

Reference Use multiple environments in ASP.NET Core

Popular Answer

Performance

Expression Evaluator