How to access DB connectionstring in DAL from presentation layer

Question

I have .net core 2.0 Web API project with multiple layers i.e Presentation, BLL, DAL... I have my connection string in appsettings.json file which is present in Presentation layer. My DAL is responsible to get data from DB based on that connection string. How do I read that json file or pass connection string to DAL.
P.S. Presentation layer depends on BLL and BLL depends on DAL.

appsettings.json

{
  "Logging": {
    "IncludeScopes": false,
    "Debug": {
      "LogLevel": {
        "Default": "Warning"
      }
    },
    "Console": {
      "LogLevel": {
        "Default": "Warning"
      }
    }
  },
  "ConnectionStrings": {
    "MsSqlConnectionString": "Data Source=myServerName;Database=myDB;User Id=myUserID;Password=myPWd;"
  }
}

DAL class

protected readonly string _connectionString;
protected IDbConnection _connection { get { return new SqlConnection(_connectionString); } }

public BaseDal()
{
    _connectionString = "<<How to get connectionstring from appsetting.json>>";
}

ChildDAL

public class MyDAL : BaseDal, IMyDAL
{
    ILogger _log;
    public MyDAL(ILoggerFactory loggerFactory)
    {
        _log = loggerFactory.CreateLogger("ChildDAL");
    }
    public async Task<IEnumerable<MyModel>> MyMethod(Dto criteria)
    {
        StringBuilder sql = new StringBuilder();

        sql.Append("SELECT * FROM table");
        string query = sql.ToString();
        // custom mapping
        DapperCustomMapping<MyModel>();

        using (IDbConnection dbConnection = _connection)
        {
            return await dbConnection.QueryAsync<MyModel>(query);
        }
    }
}

Startup.cs

public class Startup
{
    public IConfigurationRoot Configuration { get; }

    public Startup(IHostingEnvironment env)
    {
        var builder = new ConfigurationBuilder()
             .SetBasePath(env.ContentRootPath)
             .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true);

        Configuration = builder.Build();
    }

    // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
    public void Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory)
    {
        //loggerFactory.AddConsole(Configuration.GetSection("Logging"));
        loggerFactory.AddDebug();
        loggerFactory.AddLog4Net();

        app.UseErrorWrappingMiddleware();
        app.UseStatusCodePagesWithReExecute("/error/{0}");
        app.UseExceptionHandler("/error/500");

        // CORS: UseCors with CorsPolicyBuilder.
        app.UseCors("AllowSpecificOrigin");

        // MVC
        app.UseMvc();

        // Enable middleware to serve generated Swagger as a JSON endpoint.
        app.UseSwagger();

        //Enable middleware to serve swagger - ui
        app.UseSwaggerUI(c =>
        {
            c.SwaggerEndpoint("../swagger/v1/swagger.json", "My API v1");
        });
    }

    // This method gets called by the runtime. Use this method to add services to the container.
    // For more information on how to configure your application, visit https://go.microsoft.com/fwlink/?LinkID=398940
    public IServiceProvider ConfigureServices(IServiceCollection services)
    {
        // Read appsettings.json to get allowed origins
        var whiteList = new List<string>();
        var myArraySection = Configuration["AllowedOrigin"];
        if (!String.IsNullOrEmpty(myArraySection))
        {
            foreach (var d in myArraySection.Split(','))
            {
                whiteList.Add(d.Trim());
            }
        }

        // CORS
        services.AddCors(options =>
        {
            options.AddPolicy("AllowSpecificOrigin",
                policy => policy.WithOrigins(whiteList.ToArray()));
        });

        // Add framework services.
        services.AddMvc(options =>
        {
            // install global fitler on all controllers and actions.
            options.Filters.Add(new CorsAuthorizationFilterFactory("AllowSpecificOrigin"));
            options.Filters.Add(new ValidateModelAttribute());
        })
        // tell how to find the fluent validations
        .AddFluentValidation(fv => fv.RegisterValidatorsFromAssemblyContaining<ContactQueryDtoValidator>());

        // Register the Swagger generator
        services.AddSwaggerGen(c =>
        {
            c.SwaggerDoc("v1", new Info { Title = "API", Version = "v1" });
        });

        return ConfigureIoC(services);
    }

    public IServiceProvider ConfigureIoC(IServiceCollection services)
    {
        var container = new Container();
        container.Configure(config =>
        {
            config.Scan(_ =>
            {
                _.AssemblyContainingType(typeof(Startup)); // web api
                _.AssemblyContainingType(typeof(HelloBLL)); // Unused BLL
                _.AssemblyContainingType(typeof(HelloDAL)); // Unused DAL
                _.TheCallingAssembly();
                _.WithDefaultConventions();

            });

            config.Populate(services);
        });
        return container.GetInstance<IServiceProvider>();
    }
}

Answer 1

Another method to access the Configuration File, referenced from https://docs.microsoft.com/en-us/aspnet/core/fundamentals/configuration/?view=aspnetcore-2.1&tabs=basicconfiguration

Make sure you import the libraries below

using Microsoft.Extensions.Configuration;
using System.IO;

    public BaseDal()
    {
        var builder = new ConfigurationBuilder()
        .SetBasePath(Directory.GetCurrentDirectory())
        .AddJsonFile("appsettings.json");

        Configuration = builder.Build();
        var _connectionString = Configuration.GetConnectionString("CONNECTION_STRING")
    }

Answer 2

Assuming you have registered the section in startup.cs file of presentation layer.

  public class AppSettings
    {
        public string MsSqlConnectionString{ get; set; }
    }

   //registering in ConfigureServices method of Startup.cs
   services.Configure<AppSettings>(Configuration.GetSection("ConnectionStrings"));

To access in DAL layer simillarly you would have to create AppSettings class there also and then simply use

public BaseDal(IOptions<AppSettings> app)
{
    _connectionString = app.Value.MsSqlConnectionString;;
}

Edit

When you receive

Error: There is no argument given that corresponds to the required formal parameter 'app' of 'BaseDal.BaseDal(IOptions)'

that means you are not giving it required parameter. You can call the base class and give required parameter by calling :base(parameters...) constructor.

public class MyDAL : BaseDal, IMyDAL
{
    ILogger _log;
    public MyDAL(ILoggerFactory loggerFactory,IOptions<AppSettings> app):base(app)
    {
        _log = loggerFactory.CreateLogger("ChildDAL");
    }
}