Multiple connections for appsettings.json (C#)
Tags: csharp, dotnetcore, productivity, codenewbie
Published: 2024-03-24 23:43:45

## 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.

{% cta https://www.nuget.org/packages/ConsoleConfigurationLibrary/#readme-body-tab %} NuGet package {% endcta %}  {% cta https://github.com/karenpayneoregon/configuration-inject-helpers/tree/master %} GitHub repository {% endcta %}

## How to use in a project

Add [ConsoleConfigurationLibrary](https://www.nuget.org/packages/ConsoleConfigurationLibrary/#readme-body-tab) 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](https://learn.microsoft.com/en-us/nuget/consume-packages/install-use-packages-visual-studio).

Add the following to startup code in the project.

```csharp
await RegisterConnectionServices.Configure();
```

Add appsettings.json file to a project, [set copy to output directory to copy if newer](https://dev.to/karenpayneoregon/visual-studio-copying-files-to-debug-or-release-folder-3onj).


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

Add your connection strings to each property above. See the sample [here](https://github.com/karenpayneoregon/configuration-inject-helpers/blob/master/ConsoleConfigurationApp/appsettings.json).

### Setup a connection string.

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

```csharp
using static ConsoleConfigurationLibrary.Classes.AppConnections;
```

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

```csharp
using SqlConnection cn = new(Instance.MainConnection);
```

Full example using conventional connection and command objects.

```csharp
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.

```csharp
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.

```csharp
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

```csharp
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.

```csharp
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](https://www.nuget.org/packages/ConsoleConfigurationLibrary/#readme-body-tab) 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](https://learn.microsoft.com/en-us/nuget/consume-packages/install-use-packages-visual-studio).

Add the following to startup code in the project.

```csharp
await RegisterConnectionServices.Configure();
```

Add appsettings.json file to a project, [set copy to output directory to copy if newer](https://dev.to/karenpayneoregon/visual-studio-copying-files-to-debug-or-release-folder-3onj).


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

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

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

Add the following using to the DbContext class.

```csharp
using static ConsoleConfigurationLibrary.Classes.AppConnections;
```

Set the connection string in OnConfiguring.

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

Now lets read data.

```csharp
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](https://www.nuget.org/packages/Microsoft.Extensions.Configuration.Xml/8.0.0?_src=template) which allows reading settings using the same methods as used with json.

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

```csharp
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
<?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.

```csharp
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.

```csharp
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.

```csharp
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.