11

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

Improve this question
5
  • 1
    Just use "ConnectionStrings": {...} and ConfigurationExtensions.GetConnectionString - this hasn't changed and follows an obvious convention (opinion-based, of course). Commented May 24, 2018 at 11:03
  • Thanks - I don't really follow, any chance of making this into a more detailed answer? Commented May 24, 2018 at 11:29
  • Nothing has changed about this since Core was first introduced. Though, in actual practice, you'd most likely use environment variables or something like Azure Key Vault, rather than appsettings.json, so you don't commit actual credentials to your source code. It's fine for local development connection strings though. Commented May 24, 2018 at 13:20
  • Interesting - even if the connection string doesn't contain login credentials (which it doesn't in my case) - just database name? Commented May 24, 2018 at 13:21
  • I found this to be the more straight forward- stackoverflow.com/a/51780754/1042288 Commented Mar 11, 2019 at 19:21

2 Answers 2

Reset to default
16

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.
Improve this answer
Sign up to request clarification or add additional context in comments.

21 Comments

Thanks. Any chance of pasting the relevant bits in here for completeness?
I need to read this value in a data access class - which is currently a POCO. All this DI stuff is great but right now I just need to access the connection string in a POCO - as before. How can I do that?
and I guess throw 3 into a base class?
In 1 what do you mean by 'exactly matches the connection strings structure'?
I can't do any detailed explanation here in the comment section. You need to read learn.microsoft.com/en-us/aspnet/core/fundamentals/….
|
3

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");
    }
}
Improve this answer

6 Comments

My Startup constructor already has (IConfiguration configuration) in it. What effect will changing it to (IHostingEnvironment env) have? Any downsides?
Or should I keep the other constructor too?
Also do you really need to use IConfigurationRoot - rather than just IConfiguration? If so why?
I also need to access the connectionstring from a Data class - not from a controller. I've tried making a POCO class for this with the same constructor as MyController but it doesn't work (connectionString is null) - what am I missing? How is the Controller getting the configuration passed to its constructor?
Controller is getting it using dependency injection, if you use IConfiguration and initialize it in constructor like shown in above code, you will not get that error.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.