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();
    }
}

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.