Switching connection strings depending on Environment in ASP.NET CORE

Question

I have created a development and a production environment as well as two appsettings[environment].json files. Both appsettings have different connection strings for a dev and live database. I am able to switch between environments easily enough but in doing so I was hoping that the environment would be pointing at the database relevant to my environment.

When creating the database using Entity Framework Core and any migrations, I am looking for a way to switch between connection strings depending on my envinronment. Below are my appsettings.json and launchSettings.json files. I am currently using ASP.NET Core 3.1...


{
  "ConnectionStrings": {
    "DevDb": "dev connection string here"
  },

  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

{
  "ConnectionStrings": {
    "Db": "Live Conn string here"
  },

  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

Launch Settings


  "iisSettings": {
    "windowsAuthentication": false,
    "anonymousAuthentication": false,
    "iisExpress": {
      "applicationUrl": "http://localhost:50301",
      "sslPort": 44314
    }
  },
  "profiles": {
    "DOLS (UAT)": {
      "commandName": "Project",
      "launchBrowser": true,
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development",
        "ASPNETCORE_HOSTINGSTARTUPASSEMBLIES": "Microsoft.AspNetCore.Mvc.Razor.RuntimeCompilation"
      }
    },
    "DOLS (LIV)": {
      "commandName": "Project",
      "launchBrowser": true,
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Production",
        "ASPNETCORE_HOSTINGSTARTUPASSEMBLIES": "Microsoft.AspNetCore.Mvc.Razor.RuntimeCompilation"
      },
      "applicationUrl": ""
    }
  }
}

Startup class

 public void ConfigureServices(IServiceCollection services)
            {
            services.AddRazorPages();

            services.AddDbContext<ApplicationDbContext>(option => option.UseSqlServer(Configuration.GetConnectionString("Db")));
           

            }

Program Files

  public class Program
        {
        public static void Main(string[] args)
            {
            CreateHostBuilder(args).Build().Run();
            }

        public static IHostBuilder CreateHostBuilder(string[] args) =>
            Host.CreateDefaultBuilder(args)
                .ConfigureWebHostDefaults(webBuilder =>
                {
                    webBuilder.UseStartup<Startup>();
                });
        }
    }

Any help is appreciated

Use Manage User Secrets instead of two appsettings.json learn.microsoft.com/en-us/aspnet/core/security/… — Arsen
– Arsen, Commented Apr 21, 2021 at 11:19
Use the same name for the connection string in an environment-specific file. By default, you can use appsettings.Production.json. What you ask is how configuration and appsettings....json already work. You don't need anything else. No secrets, no factories. In .NET Core Configuration multiple providers can provide the same setting and the last one wins. appsettins.production.json is loaded after appsettings.json, s the ` — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 21, 2021 at 12:58
@Arsen using secrets solves a completely different problem. What if both connection strings use Windows Authentication but point to different servers and databases? — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Apr 21, 2021 at 12:59

Sabir Hossain · Accepted Answer · 2021-04-21 12:08:00Z

2

For dynamic ConnectionString you can use your own DbContextFactory to create the Database with new ConnectionString in runtime and this way you don't need 2 different appsettings.json,

#The DbContextFactory

public static MyDbContext Create(string conString, IConfiguration configuration)
{  
    try
    { 
        var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();

        optionsBuilder.UseSqlServer(conString);
        return new MyDbContext(optionsBuilder);
    }
    catch(Exception ex)
    {
        // ignored
        Log.Error(ex?.ToString());
    }  
}

the above code will create new Instance of MyDbContext in runtime from the given ConnectionString

#Uses

myDbContext = DbContextFactory.Create(conStr, Configuration);// you pass the ConnectionString based on the Envirionment

If you want MyDbContext to be available for all the Controller by default then you can simply create a base Controller and initialize the MyDbContext there so it will be available to all other Controller that inherit from the base Controller.

#OnActionExecuting base Controller

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

    if(env.IsDevelopment())
    {
        conStr = Configuration.GetConnectionString("DevConnection");
    } 
    else
    {
        conStr = Configuration.GetConnectionString("LiveConnection");
    }

    myDbContext = DbContextFactory.Create(conStr, Configuration); 
}

This is the simplest way to create any DbConnection anytime you want in runtime if the ConnectionString is valid.

answered Apr 21, 2021 at 12:08

Sabir Hossain

1,2151 gold badge27 silver badges50 bronze badges

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

6 Comments

Panagiotis Kanavos Over a year ago

There's no need for this. ASP.NET Core allows overriding settings already. That's why appsettings.Production.json is used to override the settings in appsettings.json

Rowandinho Over a year ago

Hey panagiotis-kanavos, thank you for your response. Building on my question then, is it possible to point to a particular appettings[environment].json when in package manager console. Lets say I wanted to create a database but only on the dev box?

Sabir Hossain Over a year ago

@PanagiotisKanavos I think for this you will need to set the Environment variable in the server to use different appsettings.[env].json

Panagiotis Kanavos Over a year ago

Which is the whole point. With your code you still need to detect the environment, but now the options are hard-coded, in a class that has nothing to do with settings - the controller. The DbContextFactory itself plays no role at all

Sabir Hossain Over a year ago

Understand your point, My goal was to change the ConnectionString on runtime.

|

Fernando Flores · Accepted Answer · 2022-12-28 13:45:40Z

0

Actually you don't need to change anything as long as you have the same name for the ConnectionString in both configuration files.

The correct connection string will be set on ConfigureServices based on the environment.

answered Dec 28, 2022 at 13:45

Fernando Flores

532 silver badges7 bronze badges

Comments

Muhammad Hunain · Accepted Answer · 2022-12-28 14:41:44Z

I prefer operating system environment variables for this kind of problem. then in the dev server, I put the dev database connection string and in the production, I put the prd database connection string. and not just the connection string i put all the non-constant values the base URL of any third-party service. because they can have a sandbox env for your dev env and live env for your prd env. you can simply extract the value with this:

using System;
services.AddDbContext<ApplicationDbContext>(option => option.UseSqlServer(Environment.GetEnvironmentVariable("Db")));

you can add a new environment variable like this. click here after that you need to restart your IDE in local. or on server whatever you're using for hosting for iis server you need to run this command on cmd iisreset/restart.

Collectives™ on Stack Overflow

Switching connection strings depending on Environment in ASP.NET CORE

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related