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>

Củ Su Hào · Accepted Answer · 2020-03-25 12:19:27Z

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.

Rena · Accepted Answer · 2020-03-26 02:30:00Z

3

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:

answered Mar 26, 2020 at 2:30

Rena

37.2k7 gold badges50 silver badges93 bronze badges

2 Comments

MC9000 Over a year ago

I believe there is a newer way to do this without a cshtml or a code-behind

cdub Over a year ago

What does Startup.cs look like?

Useme Alehosaini · Accepted Answer · 2020-03-25 13:10:09Z

-1

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");

edited Mar 25, 2020 at 13:10

answered Mar 25, 2020 at 13:04

Useme Alehosaini

3,1266 gold badges22 silver badges28 bronze badges

2 Comments

MC9000 Over a year ago

where did you put the class?

MC9000 Over a year ago

This answer is incompatible with DotNet5 and DotNet6 Blazor.

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related