Best Practice to use connection string in Asp.Net Core app

Question

I can't use EF because it doesn't work with Oracle (or the tutorials I have found are outdated and not usable).

I am planning on using ODP.Net or devart's driver.

What is the best way to use a connection string from appsettings.json?

Ive seen so many tutorials that are incomplete or don't work with Asp.Net Core 3.1

My controller

public class ReportController : Controller
{
    private readonly ConnectionString myString;
    public ReportController(IOptions<ConnectionString> connectionString)
    {
        myString = connectionString.Value;
    }

    public ActionResult Reporting_Totals()
    {
        string connectionString = myString.ToString();
        DataTable dt = new DataTable();
        using (OracleConnection oracleConnection = new OracleConnection())
        return View();
    }

ConnectionString class

public class ConnectionString
{        
    private string connectionString { get; set; }
    // enable set in singleton property instance to work correctly.
    //public static ConnectionString Instance { get; set; } = new ConnectionString();

    //public string DatabaseConnection { get; set; }
}

My Startup class

    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
        //configuration.GetSection("ConnectionString").Bind(ConnectionString.Instance);
    }

    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<ConnectionString>(Configuration.GetSection("ConnectionStrings"));
        services.AddControllersWithViews();
    }

My appsettings.json

  "ConnectionStrings": {
    "DEV": "Server=myServer;Database=myDb1;Trusted_Connection=True;",
    "PROD": "Server=myServer;Database=myDb2;Trusted_Connection=True;"

Answer 1

Is there any particular reason you need a special ConnectionString class? If you just want to get the connection string, you could inject IConfiguration directly and just use the GetConnectionString extension method.

public class ReportController
{
   private readonly string myString;

   public ReportController(IConfiguration configuration)
   {
      myString = configuration.GetConnectionString("DEV");
   }
}

/*
appsettings.json
{
   "ConnectionStrings": {
      "DEV": "<Your connection string>",
      "PROD": "<Your connection string>"
   }
}
*/

The nice thing about this is that as long as you can load the configuration through .NET Core's configuration system you can use this method regardless of where the connection strings are stored. So, if you decide to use Azure Key Vault or AWS as other people have suggested, or appsettings.json to store your connection strings you can inject the IConfiguration and use GetConnectionString so long as there is a configuration provider for where you're storing your secrets. Such a provider already exists for .json files and Azure Key Vault, I don't know about AWS.

On one other note, you might take a look at Use multiple environments in ASP.NET Core and use different configuration settings for different environments. This way, instead of "DEV" and "PROD" being the keys in your "ConnectionStrings" configuration, you could use "Reports", or some other name that is descriptive of the data source you're connecting to, and have a different connection string for your given environment.

For example, using appsettings.{Environment}.json files...

public class ReportController
{
   private readonly string myString;

   public ReportController(IConfiguration configuration)
   {
      myString = configuration.GetConnectionString("Reports"); //Will give you the correct connection string based on your environment.
   }
}

/*
appsettings.Production.json
{
   "ConnectionStrings": {
      "Reports": "<Your production connection string>",
   }
}

appsettings.Development.json
{
   "ConnectionStrings": {
      "Reports": "<Your development connection string>"
   }
}
*/