Read multiple connection strings from appsettings.json from different projects

Question

I'm using .Net core v 2.1 to create web api. My solution contains difference project layers (BLL, DAL, Common etc.) and there is one appsettings.json file in main project. I have multiple connection strings in my appsettings.json file and I want select connection string based on the parameter passed in controller.

Here is project structure and code:

1) api layer

appsettings.json

"ConnectionStrings": {
    "CON1": "con1 connectionstring",
    "CON2": "con2 connectionstring",
    "CON3": "con3 connectionstring"
},

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    services.AddSingleton<IConfiguration>(Configuration);
}

for eg. I'm passing conn = "CON1"

AccountController.cs

[HttpPost]
[Route("CreateUser")]
public IActionResult CreateUser(string conn, string username)
{
    try
    {
        AccountDL objAccountDL = new AccountDL(); //call account data layer
        objAccountDL.CreateUser(conn, username); //conn = "CON1"
        return Ok();
    }
    catch(Exception ex)
    {
        throw ex;
    }
}

2) dal layer

AccountDL.cs

public class AccountDL
{
    IConfiguration _configuration;
    OracleConnection _oracleConnection;
    public string CreateUser(string conn, string username)
    {
        AppConfiguration appConfg = new AppConfiguration(_configuration);   
        _oracleConnection = appConfg.GetConnection(conn);
    }
}

3) common config layer

AppConfiguration.cs

public class AppConfiguration
{
    public IConfiguration _configuration { get; }

    public AppConfiguration(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public OracleConnection GetConnection(string conn)
    {
        try
        {
            string connectionString = _configuration.GetSection("ConnectionStrings").GetSection(conn).Value;
            OracleConnection dbConn = new OracleConnection(connectionString);
            return dbConn;
        }
        catch(Exception ex)
        {
            throw ex;
        }
    }
}

Now I'll use this dbConn connection object for further processing but I'm getting Exception as System.NullReferenceException: 'Object reference not set to an instance of an object.'

If i'm trying all this in same api layer then I'm getting the connection string based on the parameter passed but after restructuring my project I want use this connection in different project i.e. DAL, how can I get the connection string based on parameters in different projects? Thank you in advance.

Answer 1

So, you can access connection string value from appsettings.json file for GetConnection() method like this -

AppConfiguration.cs

public OracleConnection GetConnection(string conn)
{
    try
    {
        string connectionString = _configuration["ConnectionStrings:" + conn];
        OracleConnection dbConn = new OracleConnection(connectionString);
        return dbConn;
    }
    catch(Exception ex)
    {
        throw ex;
    }
}

In AccountController.cs controller, you need to inject the dependency of IConfiguration object in the constructor of that controller.

public class AccountController
{
    public IConfiguration _configuration { get; }

    public AccountController(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    [HttpPost]
    [Route("CreateUser")]
    public IActionResult CreateUser(string conn, string username)
    {
        try
        {
            AccountDL objAccountDL = new AccountDL(_configuration); //call account data layer
            objAccountDL.CreateUser(conn, username); //conn = "CON1"
            return Ok();
        }
        catch(Exception ex)
        {
            throw ex;
        }
    }
}

and therefor you also need to modify the AccountDL.cs class. You need to pass that object in the constructor or in the method parameter.

public class AccountDL
{
    IConfiguration _configuration;
    OracleConnection _oracleConnection;

    public AccountDL(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public string CreateUser(string conn, string username)
    {
        AppConfiguration appConfg = new AppConfiguration(_configuration);   
        _oracleConnection = appConfg.GetConnection(conn);
    }
}