C# Tips - C# Tip Article

EF Core 3 Use appsettings.json instead of hardcoded connection string

Question

How do I avoid hardcoded connection string in my DbContext class?

using Microsoft.EntityFrameworkCore;

public class TestDbContext : DbContext
{
  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
    // hardcoded
    optionsBuilder.UseSqlServer("Data Source=.;Initial Catalog=TestDb;Integrated Security=True;");
  }

  //...
}

Tip

In .NET Core application, especially non-Web application such Console App or Desktop UI App, connection string can either be passed to DbContext constructor (TestDbContext constructor in the example) or use .NET Core Configuration API (ConfigurationBuilder). If you are going to use EF migration, the latter seems to be a good choice. Passing connection string to DbContext constructor is not enough for EF migration.

Answer

ConfigurationBuilder in .NET Core is used to build various configuration settings such as .JSON settings, .ini files, environment variables, etc. We can add connection string to appsettings.json file and read the file by using ConfigurationBuilder. To use AddJsonFile, you will need to install Microsoft.Extensions.Configuration.Json through Nuget Package Manager. Here is the code snippet.

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace ConsoleApp1
{
    public class TestDbContext : DbContext
    { 
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            // Get ConnectionString from appsettings.json
            IConfigurationRoot config = new ConfigurationBuilder()
                .AddJsonFile("appsettings.json")
                .Build();

            optionsBuilder.UseSqlServer(config.GetConnectionString("TestDb"));
        }

        public DbSet<Person> People { get; set; }
    }

    public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }
}

And here is the sample connection string in appsettings.json.

{
  "ConnectionStrings": {
    "TestDb": "Data Source=.;Initial Catalog=TestDb;Integrated Security=True"
  }
}

One thing to remember is that we are loading appsettings.json from current directory (for example, bin\Debug or bin\Release). So don't forget to copy the file to output directory. To do so, here is what you need to set.