How to set different connection strings based on build configurations for debug sessions

I am trying to have different sets of connection strings based on the build configuration. (Debug, Release, Beta, LocalTraining, etc...) The use of Config Transform files, such as web.beta.config can easily acheive the goal during deployment, but I want the changes to also take effect when pressing F5 too, to help local debugging and testing.

I tried to update the connection strings during Application_Start. The Idea is to have an environment variable and update the connection strings based on it. The variable can be set in the deployment servers easily, but I can't figure out how to set environment variables based on $(configuration) for the sake of running locally. Further more, the way I find to update the config as shown below causes the web.config file to be modified, which I'm afraid of causing problems.

if (System.Web.HttpContext.Current != null)
{
    config = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~");
}
else
{
    config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
}
var connectionStringsSection = (ConnectionStringsSection)config.GetSection("connectionStrings");
connectionStringsSection.ConnectionStrings["DefaultConnection"].ConnectionString = "my connection string";
config.Save();
ConfigurationManager.RefreshSection("connectionStrings");

Am I trying to acheive some unusual goal, or I just didn't find the correct way to do it? What is the correct way to acheive such goal?

There are methods that uses a template web.config file and uses TransformXml after build. However, the risk of needing to manually update the base.config after every Nuget package update is too large. And SlowCheetah just doesn't work for debugging for web projects.

Note: the application is on .NET Framework 4.8, not Core.

You can use ASP.NET Configuration Builders to accomplish this (also have a look if one of pre-built config builders mentioned in this documentation fits your needs!).

It allows you to create a class that will hook into the loading of web.config and modify configuration sections. I have a sample that demonstrates how to use a ASP.NET Core style appsettings.*.json configuration files to populate ASP.NET 4 AppSettings and ConnectionStrings at https://github.com/dasMulli/ConfigSampleWebApp

For your approach, you can create a custom class inheriting from ConfigurationBuilder that processes the configuration section:

public class MyConnectionStringConfigurationBuilder : ConfigurationBuilder
{
    // you can use #if / #elif etc to rely on build configuration or read enviornment variables to switch environments

#if DEBUG
    private const string DefaultEnvironmentName = "Development";
#else
    private const string DefaultEnvironmentName = "Production";
#endif
    public override ConfigurationSection ProcessConfigurationSection(ConfigurationSection configSection)
    {
        switch (configSection)
        {
            case ConnectionStringsSection connectionStringsSection:
                // modify connectionStringsSection as needed
                // e.g. by reading the connection string from a different file or using one of the provided non-default connection strings with a "default" name that is used later on in your application
                return connectionStringsSection;
            default:
                return base.ProcessConfigurationSection(configSection);
        }
    }
}

This class can then be registered in web.config:

<configuration>
  <configSections>
    <section name="configBuilders" type="System.Configuration.ConfigurationBuildersSection, System.Configuration, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" restartOnExternalChanges="false" requirePermission="false"/>
  </configSections>

  <configBuilders>
    <builders>
      <add name="MyConnectionStringConfigurationBuilder" type="MyApp.MyConnectionStringConfigurationBuilder , MyApp"/>
    </builders>
  </configBuilders>

  <connectionStrings configBuilders="MyConnectionStringConfigurationBuilder">
    <clear/>
    ... <!-- e.g. add "Database_Development" and "Database_Production" and set "Database" from one of these in the config builder. -->
  </connectionStrings>

  ...
</configuration>