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.
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.
Next create a connection object, here its SQL-Server but works with all data providers.
Full example using conventional connection and command objects.
{
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.
{
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 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 async Task Configure()
{
var services = ApplicationConfiguration.ConfigureServices();
await using var serviceProvider = services.BuildServiceProvider();
serviceProvider.GetService<SetupServices>()!.GetConnectionStrings();
}
}
The model for holding the three connections.
{
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.
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.
Set the connection string in OnConfiguring.
=> optionsBuilder.UseSqlServer(Instance.SecondaryConnection);
Now lets read data.
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.
