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.

I have updated my question, I am trying to implement Configuration["Data:CompanyFormsContext:ConnectionString"] into the optionsBuilder in my Model class, CompanyFormsContext : DbContext — Kemal Tezer Dilsiz
– Kemal Tezer Dilsiz, Commented Aug 15, 2016 at 14:22

João Lourenço · Accepted Answer · 2016-08-15 03:52:32Z

4

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.

edited Aug 15, 2016 at 3:52

answered Aug 15, 2016 at 3:38

João Lourenço

7365 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Kemal Tezer Dilsiz Over a year ago

I was looking for this but solution 2 precisely! How can I call connectionStringA and connectionStringB from the model class?

Kemal Tezer Dilsiz Over a year ago

Oh actually I am looking for Solution 2 but I want to use the same Context class with diffirent connectionStrings thats why I want to be able to do this Configuration["Data:DefaultConnection:ConnectionStringA"]; in my model class

Kemal Tezer Dilsiz Over a year ago

I have updated my question maybe it is more clear now

João Lourenço Over a year ago

Hmmm... You could achieve this by using dependency injection to inject a custom connection string provider into your contexts' constructors. Note that the lifetime of your context is scoped (valid for the lifetime of the request), so this will only work if you are not changing connections in the middle of a request. One question that begs asking here - is there a reason that you do not simply have 2 instances of the application in IIS - one for each environment?

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related