Store / Retrieve ConnectionString from appSettings

Question

I'm looking for the best practice way to store a connection string in appsettings.json in a .net Core 2 MVC app (like you do in web.config in MVC 5).

I want to use Dapper not EF (I found many EF examples).

Something like this:

{
  "ConnectionStrings": {
    "myDatabase": "Server=.;Database=myDatabase;Trusted_Connection=true;"
  },

  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Warning"
    }
  }
}

Surely there are many examples online? Nothing I can find that is for .net core 2.0.

Several things have changed between 1 and 2 and I want to ensure I'm using version 2 best practices.

I've found this - but it seems to be .net core 1: Visual Studio 2017 - MVC Core - Part 05 - Connection String from appsettings.json

This uses key value pair appsettings - not the connectionstrings: Read AppSettings in ASP.NET Core 2.0

Again it's unclear if this is .net Core 1 or 2: Net Core Connection String Dapper visual studio 2017

Answer 1

Just put like shown below in appsettings.json.

"ConnectionStrings": {
    "DefaultConnection": "Data Source=;Initial Catalog=;Persist Security Info=True;User ID=; Password=;"
}

In Startup.cs fetch it as mentioned below:

public class Startup
{
    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);

        builder.AddEnvironmentVariables();
        Configuration = builder.Build();
    }

    public IConfigurationRoot Configuration { get; }
}

Use dependency injection to inject configuration in controller like mentioned below:

public class MyController : Controller
{
    private readonly IConfiguration _configuration;
    private string connectionString;

    public MyController(IConfiguration configuration) 
    {
        _configuration = configuration;

        connectionString = _configuration.GetConnectionString("DefaultConnection");
    }
}

Answer 2

Define your connection string(s) in appsettings.json

{
    "connectionStrings": {
        "appDbConnection": "..."
    }
}

Read its value on Startup

If you follow the convention and define your connection string(s) under connectionStrings, you can use the extension method GetConnectionString() to read its value.

public class Startup
{
    public IConfiguration Configuration { get; private set; }

    public Startup(IConfiguration configuration)
    {
        Configuration = configuration;
    }

    public void ConfigureServices(IServiceCollection services)
    {
        // Since you said you're using Dapper, I guess you might want to
        // inject IDbConnection?
        services.AddTransient<IDbConnection>((sp) => 
            new SqlConnection(this.Configuration.GetConnectionString("appDbConnection"))
        );

        // ...
    }
}

Use IDbConnection within the repository?

public interface ISpecificationRepository
{
    Specification GetById(int specificationId);
}

public SpecificationRepository : ISpecificationRepository
{
    private readonly IDbConnection _dbConnection;

    public SpecificationRepository(IDbConnection dbConnection)
    {
        _dbConnection = dbConnection;
    }

    public Specification GetById(int specificationId)
    {
        const string sql = @"SELECT * FROM [YOUR_TABLE]
                             WHERE Id = @specId;";

        return _dbConnection
            .QuerySingleOrDefault<Specification>(sql,
                new { specId = specificationId });
    }
}

Just need the connection string in a POCO?

You might use the Options Pattern.

1. Define a class that exactly matches the JSON object structure in appsettings.json

   public class ConnectionStringConfig
   {
       public string AppDbConnection { get; set; }
   }
   

2. Register that configuration on Startup

   public void ConfigureServices(IServiceCollection services)
   {
      // ...
   
      services.Configure<ConnectionStringConfig>(
          this.Configuration.GetSection("connectionStrings")
      );
   
      // ...
   }
   

3. Receive the accessor in your POCO

   public class YourPoco
   {
       private readonly ConnectionStringConfig _connectionStringConfig;
   
       public YourPoco(IOptions<ConnectionStringConfig> configAccessor)
       {
           _connectionStringConfig = configAccessor.Value;
   
           // Your connection string value is here:
           // _connectionStringConfig.AppDbConnection;
       }
   }
   

Notes:

1. See my sample codes on how to read values from appsettings.json both on Core 1.x and 2.0.
2. See how I setup if you have more than 1 connection string.