Karen Payne

Posted on Mar 24 • Edited on Apr 27

Multiple connections for appsettings.json (C#)

#csharp #dotnetcore #productivity #codenewbie

Introduction

Learn how to setup multiple connection strings for an application which is environmental independent. For example, there is a need to communicate to different database servers or two different catalogs on the same server without the need to modify a connection string.

As presented there are three connections, MainConnection, SecondaryConnection and OtherConnection. If the connection string names are not suitable, clone the repository, modify the code and use as a local NuGet package.

NuGet package GitHub repository

How to use in a project

Add ConsoleConfigurationLibrary NuGet package to your project. If this is your first time adding a package see Install and manage packages in Visual Studio using the NuGet Package Manager.

Add the following to startup code in the project.

await RegisterConnectionServices.Configure();

Add appsettings.json file to a project, set copy to output directory to copy if newer.

{
  "ConnectionStrings": {
    "MainConnection": "",
    "SecondaryConnection": "",
    "OtherConnection": ""
  }
}

Add your connection strings to each property above. See the sample here.

Setup a connection string.

Add the following using statement to the class or form to interact with databases.

using static ConsoleConfigurationLibrary.Classes.AppConnections;

Next create a connection object, here its SQL-Server but works with all data providers.

using SqlConnection cn = new(Instance.MainConnection);

Full example using conventional connection and command objects.

public static List<Track> TrackList(int albumId)
{

    using SqlConnection cn = new(Instance.MainConnection);
    using var cmd = new SqlCommand { Connection = cn, CommandText = SqlStatements.TracksByAlbumId };
    cmd.Parameters.AddWithValue("@AlbumId", albumId);

    List<Track> list = [];

    cn.Open();
    var reader = cmd.ExecuteReader();
    while (reader.Read())
    {
        Track track = new()
        {
            TrackId = reader.GetInt32(0),
            Name = reader.GetString(1),
            Milliseconds = reader.GetInt32(2)
        };
        list.Add(track);
    }


    return list;

}

Full example use Dapper to read data.

public static List<Track> TrackListUsingDapper(int albumId)
{
    using SqlConnection cn = new(Instance.MainConnection);
    return cn.Query<Track>(SqlStatements.TracksByAlbumId, 
        new { AlbumId = albumId }).ToList();
}

That is it to use the package.

Under the covers

The following class is responsible for configuration.

using ConsoleConfigurationLibrary.Models;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;

namespace ConsoleConfigurationLibrary.Classes;
public class ApplicationConfiguration
{
    /// <summary>
    /// Read sections from appsettings.json
    /// </summary>
    public static IConfigurationRoot ConfigurationRoot() =>
        new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json", optional: false)
            .AddEnvironmentVariables()
            .Build();

    public static ServiceCollection ConfigureServices()
    {
        static void ConfigureService(IServiceCollection services)
        {
            services.Configure<ConnectionStrings>(ConfigurationRoot().GetSection(nameof(ConnectionStrings)));
            services.AddTransient<SetupServices>();
        }

        var services = new ServiceCollection();
        ConfigureService(services);

        return services;

    }
}

Code to get the connection strings

public static class RegisterConnectionServices
{
    public static async Task Configure()
    {
        var services = ApplicationConfiguration.ConfigureServices();
        await using var serviceProvider = services.BuildServiceProvider();
        serviceProvider.GetService<SetupServices>()!.GetConnectionStrings();
    }
}

The model for holding the three connections.

public sealed class AppConnections
{
    private static readonly Lazy<AppConnections> Lazy = new(() => new AppConnections());
    public static AppConnections Instance => Lazy.Value;
    public string MainConnection { get; set; }
    public string SecondaryConnection { get; set; }
    public string OtherConnection { get; set; }
}

Entity Framework Core example

Add ConsoleConfigurationLibrary NuGet package to your project. If this is your first time adding a package see Install and manage packages in Visual Studio using the NuGet Package Manager.

Add the following to startup code in the project.

await RegisterConnectionServices.Configure();

Add appsettings.json file to a project, set copy to output directory to copy if newer.

{
  "ConnectionStrings": {
    "MainConnection": "",
    "SecondaryConnection": "",
    "OtherConnection": ""
  }
}

Set a connection string, in this sample we will use SecondaryConnection.

{
  "ConnectionStrings": {
    "MainConnection": "",
    "SecondaryConnection": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=NorthWind2024;Integrated Security=True;Encrypt=False",
    "OtherConnection": ""
  }
}

Add the following using to the DbContext class.

using static ConsoleConfigurationLibrary.Classes.AppConnections;

Set the connection string in OnConfiguring.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) 
    => optionsBuilder.UseSqlServer(Instance.SecondaryConnection);

Now lets read data.

using ConsoleConfigurationLibrary.Classes;
using EntityFrameworkCoreSampleApp.Data;

namespace EntityFrameworkCoreSampleApp;

internal partial class Program
{
    static async Task Main(string[] args)
    {
        await RegisterConnectionServices.Configure();
        await using var context = new Context();
        var list = context.Countries.ToList();
        foreach (var country in list)
        {
            Console.WriteLine($"{country.CountryIdentifier,-4}{country.Name}");
        }

        AnsiConsole.MarkupLine("[yellow]Press ENTER to quit[/]");
        Console.ReadLine();
    }
}

Reading setting from app.config

Today its unlikely a developer will need to read settings from app.config as the default is to read from appsettings.json but there may be a case were a third party library requires an XML file for its configuration and since the code presented is not tied to a specific named file the code works, not tied to old ways of reading XML.

This is done with NuGet package Microsoft.Extensions.Configuration.Xml which allows reading settings using the same methods as used with json.

Here are the two base methods for reading XML and Json.

public class Configuration
{
    /// <summary>
    /// Setup to read appsettings.json
    /// </summary>
    public static IConfigurationRoot JsonRoot() =>
        new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json", optional: false)
            .AddEnvironmentVariables()
            .Build();

    /// <summary>
    /// Setup to read app.config
    /// </summary>
    public static IConfigurationRoot XmlRoot() =>
        new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddXmlFile("app.config", optional: true, reloadOnChange: true)
            .Build();

}

An example to read settings.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>

   <AppKey>XAS-1023-00-156</AppKey>

   <Position>
      <Title>Owner</Title>
      <Name>Jack Gibson</Name>
   </Position>

   <ConnectionString>
      <Production>Data Source=.\SQLEXPRESS;Initial Catalog=NW024;...</Production>
      <Development>Data Source=.\SQLEXPRESS;Initial Catalog=NW2023;...</Development>
   </ConnectionString>

   <Logging>
      <LogLevel>
         <Default>Information</Default>
         <Microsoft>Warning</Microsoft>
       </LogLevel>
   </Logging>

</configuration>

Define models for reading the XML above. In each model, Position points to the location in app.config to read from.

Note on Logging which is multi level we use a colon to traverse to the settings.

public class ConnectionStringOptions
{
    public const string Position = "ConnectionString";
    public string Production { get; set; } = string.Empty;
    public string Development { get; set; } = string.Empty;
}
public class PositionOptions
{
    public const string Position = "Position";

    public string Title { get; set; } = string.Empty;
    public string Name { get; set; } = string.Empty;
}

public class Logging
{
    public const string Position = "Logging:LogLevel";

    public string Default { get; set; } = string.Empty;
    public string Microsoft { get; set; } = string.Empty;
}

AppConfigService class has three methods to read settings.

public static class AppConfigService
{
    public static ConnectionStringOptions ConnectionStrings() =>
        Configuration.XmlRoot()
            .GetSection(ConnectionStringOptions.Position)
            .Get<ConnectionStringOptions>();

    public static PositionOptions GeneralSettings() =>
        Configuration.XmlRoot()
            .GetSection(PositionOptions.Position)
            .Get<PositionOptions>();

    public static string ApplicationKey() 
        => Configuration.XmlRoot()["AppKey"];

    public static Logging ApplicationLogging()
        => Configuration.XmlRoot().GetSection(Logging.Position)
            .Get<Logging>();
}

The code above then allows access using the following.

ConnectionStringOptions connections = 
    AppConfigService.ConnectionStrings();

PositionOptions generalSettings = 
    AppConfigService.GeneralSettings();

var key = AppConfigService.ApplicationKey();

Logging appLogging = 
    AppConfigService.ApplicationLogging();

Summary

With the provided package an application can have three distinct connections to different servers or three distinct connections to different catalogs on the same server or a mixture of both.