C# .net core: How to use .Net core built-in DI which will change db connection string on the fly when multiple connection string there in app.config file

Question

I am using sql helper class and when calling ExecuteDatasetAsync() then passing connection string by calling a function ConnectionManager.GetConnectionString() which read connection string from app.config file.

now i have one db connection string in app.config file but in future i could have many. so how could i use .net core built-in DI which inject or return connection string dynamically?

if possible please share a small project where .net core built-IN di will be used.

DataSet dataset = await SqlHelper.ExecuteDatasetAsync(ConnectionManager.GetConnectionString(), CommandType.Text, SQL);


public static string GetConnectionString()
{
 string sConnectionString = "";

 try
 {
 sConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MY_DB"].ConnectionString;
 //if sConnectionString is empty then throwing custom exception
 if (sConnectionString == "")
 throw new ConnectionStringException("Connection string is empty or invalid");
 }
 catch (Exception? ex)
 {
 //throwing exception if any occured to calling environment
 throw ex;
 }

 //return connection string to calling environment
 return sConnectionString;
}

app.config
<connectionStrings>
<add name="MY_DB" connectionString="Data Source=192.168.11.15;Initial Catalog=my_db;Persist Security Info=True;User ID=sa;Password=test" />
</connectionStrings>

Thanks

Answer 1

For .NET Core use appsettings.json. In the code below, connection data is from a class project used in a windows form project. This allows any project to use the connection logic.

{  
  "ConnectionsConfiguration": {  
    "ActiveEnvironment": "Production",  
    "Development": "Dev connection string goes here",  
    "Stage": "Stage connection string goes here",  
    "Production": "Prod connection string goes here"  
  }  
}  

The following comes from the following class project and the form project.

Environments

public enum ConnectionsConfiguration  
{  
    Development,  
    Stage,  
    Production  
}  

Base container

public class ConfigurationMap  
{  
    public string ActiveEnvironment { get; set; }  
    public string Development { get; set; }  
    public string Stage { get; set; }  
    public string Production { get; set; }  
}  

Class to get settings from appsettings.json

public class ConfigurationHelper  
{  
  
    public static ConnectionsConfiguration CurrentEnvironment { get; private set; }  
    /// <summary>  
    /// Current connection string by 'ActiveEnvironment'  
    /// </summary>  
    /// <returns>Connection string</returns>  
    public static string ConnectionString()  
    {  
        var configuration = Builder();  
        ConfigurationMap map = configuration.GetSection("ConnectionsConfiguration").Get<ConfigurationMap>();  
        ConnectionsConfiguration environment = map.ActiveEnvironment  
            .ToEnum(ConnectionsConfiguration.Development);  
  
        CurrentEnvironment = environment;  
  
        return environment switch  
        {  
            ConnectionsConfiguration.Development => map.Development,  
            ConnectionsConfiguration.Stage => map.Stage,  
            ConnectionsConfiguration.Production => map.Production,  
            _ => map.Development  
        };  
  
    }  
    /// <summary>  
    /// Get all environment connection strings  
    /// </summary>  
    /// <returns><see cref="ConfigurationMap"/></returns>  
    public static ConfigurationMap Connections()  
    {  
        var configuration = Builder();  
        return configuration.GetSection("ConnectionsConfiguration").Get<ConfigurationMap>();  
    }  
  
    private static IConfigurationRoot Builder()  
    {  
        var builder = new ConfigurationBuilder().AddJsonFile("appsettings.json");  
        var configuration = builder.Build();  
        return configuration;  
    }  
}  

Get connection in windows form project.

using System;  
using System.Data.SqlClient;  
using static ConfigurationLibrary.Classes.ConfigurationHelper;  
  
namespace MultiplesForm.Classes  
{  
    public class Operations  
    {  
        public static bool TestConnection()  
        {  
            try  
            {  
                using var cn = new SqlConnection() { ConnectionString = ConnectionString() };  
                cn.Open();  
                return true;  
            }  
            catch (Exception)  
            {  
                return false;  
            }  
        }  
    }  
}  
  

using System.Text;  
using System.Windows.Forms;  
using ConfigurationLibrary.Classes;  
using static ConfigurationLibrary.Classes.ConfigurationHelper;  
  
namespace MultiplesForm  
{  
    public partial class Form1 : Form  
    {  
        public Form1()  
        {  
            InitializeComponent();  
  
            ConfigurationMap map = Connections();  
  
            StringBuilder builder = new();  
            builder.AppendLine($"Current connection: {ConnectionString()}");  
            builder.AppendLine($"Current environment: {CurrentEnvironment}");  
            builder.AppendLine("");  
            builder.AppendLine($"Development: {map.Development}");  
            builder.AppendLine($"Stage: {map.Stage}");  
            builder.AppendLine($"Prod: {map.Production}");  
  
            ResultsTextBox.Text = builder.ToString();  
            ResultsTextBox.Select(0, 0);  
        }  
  
    }  
}