How to read connection string from appsettings.json in DotNetCore 2.2?

Question

Lots of posts here on how to do this, but no matter what configuration I try, I can't seem to get my database connection string. The startup.cs is configured from the Microsoft project template for Core 2.2 automatically, and as far as I can tell there is nothing wrong with it. I'm not using EF nor do I wish to load some 3rd party black box to get this to work.
Here's the Startup.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;

namespace TestWebApplication1
{
    public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.Configure<CookiePolicyOptions>(options =>
            {
                // This lambda determines whether user consent for non-essential cookies is needed for a given request.
                options.CheckConsentNeeded = context => true;
                options.MinimumSameSitePolicy = SameSiteMode.None;
            });


            services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IHostingEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
            else
            {
                app.UseExceptionHandler("/Home/Error");
            }

            app.UseStaticFiles();
            app.UseCookiePolicy();

            app.UseMvc(routes =>
            {
                routes.MapRoute(
                    name: "default",
                    template: "{controller=Home}/{action=Index}/{id?}");
            });
        }
    }
}

Here's the appsettings.json file:

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=mydbserver;Initial Catalog=mydatabase;Integrated Security=True;Persist Security Info=False;"
  }
}

From another post, the following SHOULD work, but it does not:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace TestAppWithService
{
    public class TestDB
    {
        string conString = Microsoft.Extensions.Configuration.ConfigurationExtensions.GetConnectionString(this.Configuration, "DefaultConnection");
    }
}

The file, called TestDB.cs is set to compile and, for kicks, I put it in the root folder (doesn't matter where I put the class: model, controller, etc) I get the Keyword 'this' is not available in the current context. (with a squiggly line under it). I have no idea how to proceed or what to look for and the answers here are numerous with all sorts of tweaks, but as per MS, this should work fine. I'm new to dotnetcore and thought I had this dependency injection stuff figured out, but am still stuck.

Answer 1

This wont compile

public class TestDB
{
    string conString = Microsoft.Extensions.Configuration.ConfigurationExtensions.GetConnectionString(this.Configuration, "DefaultConnection");
}

given the context under which it is trying to be used.

Access to IConfiguration should be restricted to the composition root, which in this case is Startup

Having to inject IConfiguration outside of the composition root can be seen as a code smell and the current self answer has some design issues that should be refactored.

Firstly, addressing the connection string issue, the following supporting abstractions and implementation should be introduced.

public class ConnectionStrings {
    public string DefaultConnection { get; set; }
}

public interface IDbConnectionFactory {
    IDbConnection Create(string connectionString);
}

public class SqlConnectionFactory : IDbConnectionFactory {
    public IDbConnection Create(string connectionString) {
        return new SqlConnection(connectionString);
    }
}

public interface IDataProvider {
    List<DropDownOption> CalcSelectDDSizeAndTilesPerBoxAll();
}

and the data class refactored to follow a more SOLID design approach

public class MyDataProvider : IDataProvider {
    static string LastErrorMsg = string.Empty;
    private readonly string connectionString;
    private readonly IDbConnectionFactory connectionFactory;

    public MyDataProvider(ConnectionStrings connections, IDbConnectionFactory connectionFactory) {
        this.connectionString = connections.DefaultConnection;
        this.connectionFactory = connectionFactory;
    }

    public List<DropDownOption> CalcSelectDDSizeAndTilesPerBoxAll() {
        var options = new List<DropDownOption>();
        try {
            using (IDbConnection connection = connectionFactory.Create(connectionString)) {
                using (IDbCommand command = connection.CreateCommand()) {
                    command.CommandText = "CalcSelectDDSizeAndTilesPerBoxAll";
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandTimeout = 30;

                    connection.Open();
                    using (IDataReader r = command.ExecuteReader(CommandBehavior.CloseConnection)) {
                        while (r.Read()) {
                            DropDownOption option = new DropDownOption {
                                value = r["SizeAndNumInBox"].ToString(),
                                text = r["Descr"].ToString()
                            };
                            options.Add(option);
                        }
                    }
                    LastErrorMsg = string.Empty;
                }
            }
        } catch (Exception ex) {
            LastErrorMsg = ex.Message;
            //consider logging error
            options = new List<DropDownOption>();
        }
        return options;
    }
}

Note the explicit injection of the supporting ConnectionStrings and IDbConnectionFactory and how they affect the implementation of the target CalcSelectDDSizeAndTilesPerBoxAll function.

With that, all the supporting abstractions and implementations should be registered at startup

public void ConfigureServices(IServiceCollection services) {
    services.Configure<CookiePolicyOptions>(options => {
        // This lambda determines whether user consent for non-essential cookies is needed for a given request.
        options.CheckConsentNeeded = context => true;
        options.MinimumSameSitePolicy = SameSiteMode.None;
    });

    //bind object model
    ConnectionStrings connections = Configuration.Get<ConnectionStrings>();
    //add it to the service collection so that is accessible for injection
    services.AddSingleton(connections);

    //register connection factory
    services.AddSingleton<IDbConnectionFactory, SqlConnectionFactory>();

    //register data provider
    services.AddSingleton<IDataProvider, MyDataProvider>();

    services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
}

Finally the controller can depend only on what it actually needs to perform its function instead of having to act as a messenger and pass injected members along

public class ServicesController : Controller {
    private readonly IDataProvider myData;

    public ServicesController(IDataProvider myData) {
        this.myData = myData;
    }

    public IActionResult Index() {
        return View();
    }

    // service returning json for dropdown options fill for tile calculator
    public IActionResult GetCalcDDOptions() {
        var calcOptions = myData.CalcSelectDDSizeAndTilesPerBoxAll(); 
        return Ok(calcOptions);
    }
}

Answer 2

The default template from VS2019 (dotnetcore 2.2), the Startup.cs doesn't need any changes. In a controller I added a couple of things:

using Microsoft.Extensions.Configuration;

In my controller class, I added:

private readonly IConfiguration configuration;
public ServicesController(IConfiguration config)
{
    this.configuration = config;
}

I changed the method in the model class to accept the configuration as a parameter. Here's what it looks like as called from the controller:

  var calcOptions = MyData.CalcSelectDDSizeAndTilesPerBoxAll(this.configuration);

The complete controller code (for reference):

using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using TestAppWithService.Models;

namespace TestAppWithService.Controllers
{
    public class ServicesController : Controller
    {
        private readonly IConfiguration configuration;
        public ServicesController(IConfiguration config)
        {
            this.configuration = config;
        }

        public IActionResult Index()
        {
            return View();
        }

        // service returning json for dropdown options fill for tile calculator
        public IActionResult GetCalcDDOptions()
        {
            var calcOptions = MyData.CalcSelectDDSizeAndTilesPerBoxAll(this.configuration); //note: pass the config to the model
            return new ObjectResult(calcOptions);
        }
    }
}

in the model, I added:

using Microsoft.Extensions.Configuration;

then to the method I added the connection info parameter:

public static List<DropDownOption> CalcSelectDDSizeAndTilesPerBoxAll(IConfiguration config)

and inside the method, to get the db connection string is simply:

string dbconn = config.GetConnectionString("DefaultConnection");

The complete code for the model:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using Microsoft.Extensions.Configuration;

namespace TestAppWithService.Models
{
    // This is for custom database functions for services

    public class MyData
    {
        static string LastErrorMsg = string.Empty;
        public static List<DropDownOption> CalcSelectDDSizeAndTilesPerBoxAll(IConfiguration config)
        {
            Boolean HasErrors = false;
            var retval = new List<DropDownOption>();

            string dbconn = config.GetConnectionString("DefaultConnection");

            using (SqlConnection conn = new SqlConnection(dbconn))
            {
                using (SqlCommand cmd = new SqlCommand("CalcSelectDDSizeAndTilesPerBoxAll", conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandTimeout = 30;
                    try
                    {
                        conn.Open();
                        using (SqlDataReader r = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                        {
                            if (r.HasRows)
                            {
                                while (r.Read())
                                {
                                    DropDownOption ddo = new DropDownOption();
                                    ddo.value = r["SizeAndNumInBox"].ToString();
                                    ddo.text = r["Descr"].ToString();
                                    retval.Add(ddo);
                                }
                            }
                        }
                        LastErrorMsg = string.Empty;
                    }
                    catch (Exception ex)
                    {
                        LastErrorMsg = ex.Message;
                        HasErrors = true;
                    }
                }
                if (!HasErrors)
                {
                    return retval;
                }
                else
                {
                    return new List<DropDownOption>(); //just an empty list returned
                }
            }
        }
    }
}

And for kicks, here is the View w/javascript (Test Page) to consume the service:

@{
    ViewData["Title"] = "Test";
}
<script type="text/javascript">
    $(function () {
        $("#btnFillDD").click(function () {
            RetrieveCalcOptionsDD();
        });

        function RetrieveCalcOptionsDD() {
            var ddl = $("#TilesInCartonBySize");
            var oldEvent = ddl.attr("onchange");
            ddl.attr("onchange", ""); //remove change event
            $.ajax({
                url: '../Services/GetCalcDDOptions',
                dataType: 'json',
                method: 'get',
                success: function (retdata) {
                    ddl.empty();
                    $.each(retdata, function () {
                        ddl.append($("<option></option>").val(this['value']).html(this['text']));
                    });
                },
                error: function (err) {
                    console.log('Error (RetrieveCalcOptionsDD): ' + JSON.stringify(err, null, 2));
                }
            });
            ddl.attr("onchange", oldEvent); //add change event back
        };
    });
</script>
<h1>Test</h1>
<p><button id="btnFillDD">Click Me</button></p>
<p>
    <select id="TilesInCartonBySize" class="calcText" onchange="calculate(this.form);">
    </select>
</p>

Note that this "service" is just a view that returns json (so you can use this for anything).

All is good.