How to pass appsettings.json text as a SQL connection string in Model class? (Specific example)

Question

I am migrating a ASP.NET project into ASP.NET Core and I was trying to implement DbContext functionality in .NET Core. I am trying to pass different SQL Server information to the optionsBuilder that I am passing as a parameter to the base constructor. I have the following context model class:

public partial class CompanyFormsContext : DbContext
{
        public CompanyFormsContext() : base (CreateOptions(null))
        {}

        public CompanyFormsContext(string connName) : base (CreateOptions(connName))
        {}

        //NEED TO CONNECT CONFIGURATION HERE
        private static DbContextOptions<CompanyFormsContext> CreateOptions(string connName)
        {
            var optionsBuilder = new DbContextOptionsBuilder<CompanyFormsContext>();

            if (connName == null)
            {
                optionsBuilder.UseSqlServer("Server=(localdb)\\projectsv13;Database=coforms;Trusted_Connection=True;");
            }
            else
            {
                //THIS IS HOW IM TRYING TO MAKE IT
                //similar to --> Configuration["Data:CompanyFormsContext:ConnectionString"]"
                optionsBuilder.UseSqlServer(Configuration["Data:" + connName + ":ConnectionString"]);
            }

            return optionsBuilder.Options;
        }
...

I want to pass in my SQL Server information through the information in appsettings.json which is the following:

  "Data": {
    "CompanyFormsContext": {
      "ConnectionString": "Server=(localdb)\\projectsv13;Database=coforms;Trusted_Connection=True;"
    },
    "CompanyFormsContextQA": {
      "ConnectionString": "Server=(localdb)\\projectsv13;Database=coforms;Trusted_Connection=True;"
    }
  },

I have found the following question but I believe the syntax has changed a bit since the RC2 update because I cannot use the solutions or I'm doing it wrong.

Setting the SQL connection string for ASP.NET 5 web app in Azure

How could I use a syntax similar to Configuration["Data:CompanyFormsContext:ConnectionString"] for the SQL Server input? Or is there a better way of doing this? What should be in the Startup file specifically? I have the following:

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

public IConfigurationRoot Configuration { get; set; }

public void ConfigureServices(IServiceCollection services)
{
    // Add framework services.
    services.AddMvc();
    services.AddOptions();
}
...

If requested, I can simplify the question to specifically ask about using appsettings.json in the model class. I realize that I have extra information here for clarifying the specific situation.

Answer 1

Solution 1

Are you trying to load a different environment for QA? If so, you should use an environment variable in your project properties called:

ASPNETCORE_ENVIRONMENT : Development

or

ASPNETCORE_ENVIRONMENT : QA

Then modify launchSettings.json:

{
  ...
  "profiles": {
    "IIS Express": {
      "commandName": "IISExpress",
      "launchBrowser": true,
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      }
    },
    "IIS Express (QA)": {
      "commandName": "IISExpress",
      "launchBrowser": true,
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "QA"
      }
    }
  }
}

You can then create a separate Startup.cs file for each environment called StartupDevelopment.cs and StartupQA.cs. This will allow different start-up profiles based on the environment.

Solution 2

Are you trying to load separate connection strings based on the context (different contexts in your application load different connections)? If so:

The best way to do this is not to use the Context to initialise the connection. This should be done in the ConfigureServices method in Startup.cs. You can set up your connection strings based on the key in your json files:

// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
    // Load the first connection string
    var connectionStringA = Configuration["Data:DefaultConnection:ConnectionStringA"];
    // Load the second connection string
    var connectionStringB = Configuration["Data:DefaultConnection:ConnectionStringB"];
    // Set up Entity Framework
    services.AddEntityFramework()
        .AddSqlServer()
        .AddDbContext<ContextA>(options => options.UseSqlServer(connectionStringA))
        .AddDbContext<ContextB>(options => options.UseSqlServer(connectionStringB));
}

Now your contexts will load the connection strings from the JSON files correctly.