Using external config file for connection strings in multiple projects

Question

I want to know if there is a better way to use connection strings in external files in my solution, since my solution has 8 separate projects, all using the same connection strings. According to this:

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/connection-strings-and-configuration-files#using-external-configuration-files

using an external file (for connection strings) is trivial, and can be easily done (using something like configSource="connections.config). However, in order to make this work, We have to follow these guidelines:

1. The external file has to exist within the project folder
2. The external file has to have the property "CopyToOutputFolder" set to true

In my case, this can be done, but it's problematic to manage this throughout all 8 projects, especially if I need to add a new connection string, change one of the settings (target database, username, password), or when I need to add a new project.

--FYI: each of these projects are web jobs that are deployed to an Azure server. Adding a new web job for a new feature, new utility, etc., or removing an old web job for any number of reasons is not uncommon. This solution is constantly changing, which adds to the difficulty of managing these settings within each project separately. Also, connection string management on the production server is not problematic, since each web job inherits these connection strings from the parent app service.

I would like to know if any of the following is possible;

1. Can I use a separate file that is NOT within the project folder for the connection strings?
2. Can I load connection strings dynamically (at run-time) into the configuration manager? (one note on this - I know this is possible, but I need to be able to do it without affecting the underlying app.config file)
3. Is there another type of ConfigurationManager that is commonly used to load these connection strings into the solution, that will meet my requirements?

Answer 1

Although this question is over a year old, I thought an up-to-date answer would be useful for any Windows desktop coders wanting to reference connection strings outside the project folder's app.config etc. This would be useful for sharing 1 config file for multiple projects, having to only change connection string in 1 centralised file.

The basics of telling App.Config to use a different file for connection strings, is straightforward; however, .NET appears NOT to be able to parse this external file, if it is either NOT in the project's root folder, or a subfolder(s) within that project. Let's see this by looking at the most basic example:

in the project's App.config, within the <configuration> tag, use the code below ('configSource' points to another file to read the connection strings):

<configuration>
  <connectionStrings configSource="ExternalConnectionStrings.config"/>
</configuration>

Create ExternalConnectionStrings.config, with the following code:

<connectionStrings>
<clear/>
    <add name = "ConnString1"
         connectionString = "Server=myServer;Trusted_Connection=True;Database=myDB;Persist Security Info=false"/>
    <add name = "ConnString2"
         connectionString = "Server=tcp:azureserver.database.windows.net,1433;Database=AzureDB;User ID=**;Password=**;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;Persist Security Info=false"/>
</connectionStrings>

Save this config file in the same folder as your project's App.config and mark this file's property to 'Copy to Output Directory' or 'Copy if newer'.

This is the 'textbook' way of referring to connection strings from another file other than App.config. However, this may not be that useful: how to have this external file in a completely different folder from the project folder?

To do this, copy that same ExternalConnectionStrings.config to a folder OUTSIDE the project or solution folder, eg. C:\ConnectionStringsFolder\. REMOVE this config file from the project folder where we previously copied/created it (otherwise the following instructions will not work).

Keep App.config the same (making sure the ExternalConnectionStrings.config is not present in the project folder). Use the Windows mklink command to associate ExternalConnectionStrings.config to an external folder, with the following command prompt command:

mklink ExternalConnectionStrings.config C:\ConnectionStringsFolder\ExternalConnectionStrings.config

Windows should return with a 'symbolic link created for....'; make sure you did NOT have that particular config file present in the project folder where app.config sits.

You should see the ExternalConnectionStrings.config listed within eg. Visual Studio; make sure you mark this to COPY to the output folder (I use 'Copy if newer', which will pick up any changes to the external config file ONLY after a rebuild of the project).

This answers Matt Spinks' question 1; for question 2, the following method will return all connection strings found within the external file (as pointed to by App.config):

using System.Diagnostics;
using System.Configuration;

    static void GetConnectionStrings()
            {
                ConnectionStringSettingsCollection cStringsSettings = ConfigurationManager.ConnectionStrings;

                if (cStringsSettings != null)
                {
                    foreach (ConnectionStringSettings cstrings in cStringsSettings)
                    {
                        Debug.WriteLine(cstrings.Name);
                        Debug.WriteLine(cstrings.ConnectionString);
                    }
                }
            }

This is currently working in Visual Studio 2019, with .NET Core 3.1, a WPF application, on Windows 10.

Answer 2

You don't mention which version of dotnet you are using, however if you are using dotnet core the application configuration can be sourced from environment variables.

I think this could solve your problem because you don't need to track any additional files between your projects and in my opinion makes a lot of sense because any secure data would not be visible to any developer or be stored in any repository. Only devops would know about it when they set it initially. Also this gets your closer to the 12 factor app https://12factor.net/.

To use this make sure that your Startup.cs file includes a section to load the environment variables such as

var config = new ConfigurationBuilder()
    .AddJsonFile("appsettings.json", true, true)
    .AddJsonFile($"appsettings.{env.EnvironmentName}.json", true)
    .AddEnvironmentVariables()
    .Build();

dotnet core Environment Variable cnfiguration provider

You can probably create a bat file to set everything initially

setx connection_string="Server=xxxxxxxxx" /M

This will create your environment variables and set the values you specity.

Hope you find this useful.