How can I get connectionstring from appsetting using ConfigurationManager?

Question

I created a asp.net core web api project that has Microsoft.EntityFrameworkCore.SqlServer, Microsoft.EntityFrameworkCore.Tools, and System.Configuration.ConfigurationManager. I ran the Scaffolding command Scaffold-DbContext "Data Source=DEX-LEP3LOXH0M5\\SQLEXPRESS;Initial Catalog=LibraryStore;Integrated Security=True" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models.

In Context.cs file that was created from the scaffolding cmd created this method:

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                //#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
                 optionsBuilder.UseSqlServer("Data Source=DEX-LEP3LOXH0M5\\SQLEXPRESS;Initial Catalog=LibraryStore;Integrated Security=True");
               
            }
        }

when I ran the route path to the controller it worked perfectly I was able to see the records from the DB. But on that #warning message, it provided a link which I followed. In my appsettings.json file I added ConnectionStrings by doing:

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "Default": "Data Source=DEX-LEP3LOXH0M5\\SQLEXPRESS;Initial Catalog=LibraryStore;Integrated Security=True"
  }
}

then my next step was to add the code in the ConfigureServices in the startup.cs:

public void ConfigureServices(IServiceCollection services)
        {

            services.AddControllers();
            services.AddSwaggerGen(c =>
            {
                c.SwaggerDoc("v1", new OpenApiInfo { Title = "LibraryWebAPI", Version = "v1" });
            });
            services.AddDbContext<LibraryStoreContext>(options =>
                  options.UseSqlServer(Configuration.GetConnectionString("Default")));
        }

then it said to change the OnConfiguring() in the Context.cs file that was created.

I did the following:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                
                optionsBuilder.UseSqlServer(ConfigurationManager.ConnectionStrings["Default"].ConnectionString);
            }
        }

but then it generates the error message on the line optionsBuilder:

System.Configuration.ConnectionStringSettingsCollection.this[string].get returned null.

Is there a reason why it is returning null if the same connection string worked before following the link to add connection to connectionstring in appsettings?

including my controlller:

        [HttpGet]
        [Route("GetAllDetails")]
        public IEnumerable<LibraryInfo> GetDetails()
        {
            using (var context = new LibraryStoreContext())
            {
                // get all library details
                return context.LibraryDetails.ToList();
            }
        }

what happens if you don't override OnConfiguring in your 'Context' class? You've passed the connectionstring in the startup.cs 'ConfigureServices' so you don't need to pass it again. I dont think i've ever overridden onconfiguring in any of my DbContext sub-classes to set the connection string. — newky2k
– newky2k, Commented Sep 29, 2021 at 17:09
When I do that it tells me: System.InvalidOperationException: 'No database provider has been configured for this DbContext. A provider can be configured by overriding the 'DbContext.OnConfiguring' method or by using 'AddDbContext' on the application service provider. If 'AddDbContext' is used, then also ensure that your DbContext type accepts a DbContextOptions<TContext> object in its constructor and passes it to the base constructor for DbContext.' — MarkCo
– MarkCo, Commented Sep 29, 2021 at 17:20
Do you have a constructor like this in your context class? public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { } — newky2k
– newky2k, Commented Sep 29, 2021 at 17:25
Yes I have a default constructor and I have a overloaded constructor public LibraryStoreContext(DbContextOptions<LibraryStoreContext> options) : base(options) { } — MarkCo
– MarkCo, Commented Sep 29, 2021 at 17:41
try removing the default contstructor. this is the class that VS creates when you create a new site with build in identity -> pastebin.com/eirxvS9b. It not based on DbContext directly, but i don't think that matters. I also normally have the AddDbContext above AddControllers. — newky2k
– newky2k, Commented Sep 29, 2021 at 17:58

Pieterjan · Accepted Answer · 2021-09-29 19:41:16Z

I did just find a more comfortable way to maintain generating migrations, where you don't have to run the command from the web project.

DbContext

internal class ContosoContext : DbContext
{
    private readonly IConfiguration configuration;
    public ContosoContext(IConfiguration configuration)
    {
        this.configuration = configuration;
    }
    public ContosoContext()
    {
        this.configuration = null;
    }
    
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        base.OnConfiguring(optionsBuilder);
        if (configuration == null)
        {
            // Only used when generating migrations
            // Contoso.Data = name of the project where your migrations should reside
            var migrationsConnectionString = @"Server=(localdb)\mssqllocaldb;Database=Contoso;Trusted_Connection=True;ConnectRetryCount=0";
            optionsBuilder.UseSqlServer(migrationsConnectionString, options => options.MigrationsAssembly("Contoso.Data"));
        }
        else
        {
            optionsBuilder.UseSqlServer(configuration.GetConnectionString("Contoso"));
        }
    }
}

Registration of the DbContext

services.AddDbContext<ContosoContext>(options => {
   options.UseSqlServer(Configuration.GetConnectionString("Contoso"));
});

When running the application (the web project), the constructor with parameters will be called.
When generating migrations from the Contoso.Data folder (dotnet ef migrations add myawesomemigration), the parameterless constructor will be called.

This drops the need of excessively specifying paramters during the generation of database migrations:

dotnet ef migrations add AddIdentity

Gal I. · Accepted Answer · 2021-09-29 18:13:37Z

2

The .Net Core application uses appsettings.json instead of web.config file.

Because .Net Core applications are self hosted and can almost run on any platform, they are no longer have to be hosted on IIS. The .Net Core application settings are stored in a Json format (appsettings.json) by default while .Net Framework application configurations are stored in a web.config file in XML format This is why it shouldn't work with your appsettings.json when using ConfigurationManager

From the guid you sent they say to do the change in protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)

But this is said not on .net core applications

answered Sep 29, 2021 at 18:13

Gal I.

2512 gold badges4 silver badges14 bronze badges

5 Comments

MarkCo Over a year ago

So ConfigurationManager will not work with a asp.net core web api project? Is there a proper way to read the connection string from appsettings.json in a asp.net core web api project with the context file?

Pieterjan Over a year ago

Inject IConfiguration configuration in your DbContext. Then you can call configuration.GetConnectionString("somename") in the DbContext.Configure method, to get the connectionStrings:somename value from your appsettings file/environment variables/command-line parameters.

Pieterjan Over a year ago

I do it like this. The only drawback is that you need to generate your migrations from the web project...

MarkCo Over a year ago

aspsnippets.com/Articles/… I used this and it worked :)

MarkCo Over a year ago

Thank you for the help guys!

Collectives™ on Stack Overflow

How can I get connectionstring from appsetting using ConfigurationManager?

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related