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.

You could try using environment value to set the connection string learn.microsoft.com/en-us/aspnet/core/fundamentals/environments — Nkosi
– Nkosi, Commented Nov 28, 2019 at 12:15
@Nkosi I've added an update above in the OP. Just one more question :) — scgough
– scgough, Commented Nov 28, 2019 at 13:52
@Nkosi as in add ASPNETCORE_ENVIRONMENT as Development to my azure app config? — scgough
– scgough, Commented Nov 28, 2019 at 14:17

Nkosi · Accepted Answer · 2019-11-28 15:00:57Z

3

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

edited Nov 28, 2019 at 15:00

answered Nov 28, 2019 at 14:22

Nkosi

249k38 gold badges472 silver badges506 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

scgough Over a year ago

Thanks,I did actually have that available but simplified it for my answer. So, the only thing I'm not sure is - will this pick up based on the URL or do I need to add in ASPNETCORE_ENVIRONMENT to my Azure App Service Settings?

Nkosi Over a year ago

@scgough based on ASPNETCORE_ENVIRONMENT

Rüdiger · Accepted Answer · 2019-11-28 13:25:29Z

For simple apps and to keep the connection strings away from my repository I use preprocessor statements plus PATH/System Variables and for release I provide a connection string within the settings.json.

#define USE_FEATURE_X
using System;

namespace MyNamespace {
    internal static class StaticConnectionStringFactory {
        public static string GetConnectionString() {
#if DEBUG && !USE_FEATURE_X
            var connectionString = Environment.GetEnvironmentVariable("CNNSTR_SQL_XYZ", EnvironmentVariableTarget.User);
#elif DEBUG && USE_FEATURE_X
            var connectionString = Environment.GetEnvironmentVariable("CNNSTR_SQL_ABC", EnvironmentVariableTarget.User);
#else
            var connectionString = Environment.GetEnvironmentVariable("SqlConnectionString", EnvironmentVariableTarget.Process);
#endif
            return connectionString;
        }
    }
}

Neo Vijay · Accepted Answer · 2019-11-28 13:41:03Z

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>

Collectives™ on Stack Overflow

Easily switching between connection strings in .NET Core

Update (2)

Update (3) - Mission Complete

3 Answers 3

Reference Use multiple environments in ASP.NET Core

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Update (2)

Update (3) - Mission Complete

3 Answers 3

Reference Use multiple environments in ASP.NET Core

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related