How to read connection string in ASP .NET Core Razor Pages

Question

I can't wrap my head around how to retrieve connection strings in ASP .NET Core. I've spent two days trying all the different things and can't get it to work.

I have tried the following methods:

Using ConfigurationManager.AppSettings a Count returns 0
Using ConfigurationManager.ConnectionStrings[].ConnectionString - the only connection string that exists is to .\SQLEXPRESS (have to use index rather than string in the ConnectionStrings[]), specifying a name of the connection results in object reference not set error
Trying to use WebConfigurationManager can't find how to add it. I've tried System.Web.Configuration, NuGet packages, references.
Using IConfiguration and dependency injection - I've got no idea how to read the connection string (how to reference the controller in the model and read the value of "xxx" connection string)

ConfigurationManager.ConnectionStrings["SupplierDB"].ConnectionString
// NullReferenceException: Object reference not set to an instance of an object.

ConfigurationManager.ConnectionStrings[0].ConnectionString
// data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true

ConfigurationManager.AppSettings.Count.ToString();
// Returns 0

I've got same connection strings specified in both appsettings.json and web.config. Can someone please point me in the right direction as I'm currently completely lost as nothing works.

appsettings.json

{
  "ConnectionStrings": {
    "CustomerDB": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=Customer_Db;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False",
    "SupplierDB": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=Supplier_Db;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

web.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <connectionStrings>
    <add name="CustomerDB" providerName="System.Data.SqlClient" connectionString="Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Customer_Db;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"/>
    <add name="SupplierDB" providerName="System.Data.SqlClient" connectionString="Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Supplier_Db;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"/>
  </connectionStrings>
</configuration>

score 4 · Accepted Answer · answered Mar 25 '20 at 12:19

You can create an AppSettings class like this, by copying your appsettings.json to QuickType:

public class AppSettings
{
    public static ConnectionStrings ConnectionStrings { get; set; }
    public static Logging Logging { get; set; }
    public static string AllowedHosts { get; set; }
}

public class ConnectionStrings
{
    public string CustomerDb { get; set; }
    public string SupplierDb { get; set; }
}

public class Logging
{
    public LogLevel LogLevel { get; set; }
}

public class LogLevel
{
    public string Default { get; set; }
    public string Microsoft { get; set; }
    public string MicrosoftHostingLifetime { get; set; }
}

Then in Startup:

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)
    {
        Configuration.Get<AppSettings>();
        services.AddControllersWithViews();
    }

I use static for easy access instead of injecting AppSettings through DI.

Or if you don't want to put them all in a class, check out the Options pattern from Microsoft.

score 2 · Answer 2 · answered Mar 26 '20 at 02:30

You could get the connection string value in appsettings.json like below:

For Mvc:

public class HomeController : Controller
{
    public readonly IConfiguration _configuration;
    public HomeController(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public IActionResult Index()
    {
        string CustomerDBConnectionString = _configuration.GetConnectionString("CustomerDB");
        string SupplierDBConnectionString = _configuration.GetConnectionString("SupplierDB");

        return View();
    }
}

For Razor Pages:

public class IndexModel : PageModel
{
    public readonly IConfiguration _configuration;
    public IndexModel(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public void OnGet()
    {
        string CustomerDBConnectionString = _configuration.GetConnectionString("CustomerDB");
        string SupplierDBConnectionString = _configuration.GetConnectionString("SupplierDB");
    }
}

Result:

I believe there is a newer way to do this without a cshtml or a code-behind — MC9000, Nov 20 '21 at 00:39

Useme Alehosaini · Answer 3 · 2020-03-25T13:10:09.310

Be sure you have the following NuGets installed:

Microsoft.Extensions.Configuration;
Microsoft.Extensions.Configuration.Json;
//Microsoft.Extensions.Configuration.UserSecrets; //Microsoft.Extensions.Configuration.EnvironmentVariables;

Generate the following class:

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

    public static class CustomConfiguration
        {
            public static IConfigurationRoot config;

            public static void BuildConfiguration()
            {
                var builder = new ConfigurationBuilder()
                    .SetBasePath(Directory.GetCurrentDirectory())
                    .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true);
                    //.AddUserSecrets(Assembly.GetExecutingAssembly(), optional: true, reloadOnChange: true)
                    //.AddEnvironmentVariables();

                config = builder.Build();
            }
}

Then, call the Subject method from anywhere you like

    CustomConfiguration.BuildConfiguration();
    string connectionString = CustomConfiguration.config.GetConnectionString("CustomerDB");

This answer is incompatible with DotNet5 and DotNet6 Blazor. — MC9000, Nov 20 '21 at 03:19

How to read connection string in ASP .NET Core Razor Pages

3 Answers3