.NET Core: How can I set the connection string?

Question

I am trying to use Blazor's CRUD functions and following some article to do this. In the article, there is a part that I should put my connection in context file, but it doesn't say how to set the connection string.

I put this code line in launchSettings.json:

{
  "ConnectionStrings": {
    "UserDatabase": "Server=DESKTOP-2K2A6GN;Database=Assignment4;Trusted_Connection=True;"
  },
  "iisSettings": {
    "windowsAuthentication": false,
    "anonymousAuthentication": true,
    "iisExpress": {
      "applicationUrl": "http://localhost:56244/",
      "sslPort": 0
    }
  },
  "profiles": {
    "IIS Express": {
      "commandName": "IISExpress",
      "launchBrowser": true,
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      }
    },
    "Assignment4.Server": {
      "commandName": "Project",
      "launchBrowser": true,
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development"
      },
      "applicationUrl": "http://localhost:56248/"
    }
  }
}

And I tried to add the connection string into the context file, but it didn't work.

public class UserContext : DbContext
    {
        public virtual DbSet<User> tblUser { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer(@"UserDatabase");
            }
        }
    }

Vagabond

you are suppose to add the connection string settings in the appsetting.json – Menjivar

but there is no appsettings.json file on the project. that's why I put in launchsettings.json. So where can I create the appsettings.json file and connect with the project? – Vagabond

Answer 1

Connection string settings are suppose to be in the appsetting.json file. Add the file to the project

appsetting.json

{
  "ConnectionStrings": {
    "UserDatabase": "Server=DESKTOP-2K2A6GN;Database=Assignment4;Trusted_Connection=True;"
  }
}

Update the DbContext so that it can be configured.

public class UserContext : DbContext {

    public UserContext(DbContextOptions<UserContext> options): base(options) {

    }

    public virtual DbSet<User> tblUser { get; set; }

}

You would configure the DbContext in the Startupclass in the ConfigureServices method.

public class Startup {

    public Startup(IHostingEnvironment env) {

        var builder = new ConfigurationBuilder()
            .SetBasePath(env.ContentRootPath)
            .AddJsonFile("appsettings.json");

        Configuration = builder.Build();
    }

    static IConfiguration Configuration { get; set; }

    public void ConfigureServices(IServiceCollection services) {    

        services.AddDbContext<UserContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("UserDatabase"));
        );

        //...
    }

    // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
    public void Configure(IApplicationBuilder app, IHostingEnvironment env) {
        if (env.IsDevelopment()) {
            app.UseDeveloperExceptionPage();
        }

        app.UseBlazor<Client.Program>();
    }
}

Menjivar

I get so many errors on the ConfigureService part. It doesn't recognize the UserContext and the Configuration even I connected with the client and server and have a EF library. – Vagabond

when I add your UserContext constructor, I can't use this class in access layer because there is no parameter – Vagabond

The way I did it was so that the context can be used with dependency injection. – Menjivar

If you have a time, can you look at the project? I did what you said but it doesn't work. Here is the project: uploadfiles.io – Vagabond

please consider to use .SetBasePath(environment.ContentRootPath) in the Startup class constructor Startup(IHostingEnvironment environment). My idea is to use environments ContentRootPath property not Directory.GetCurrentDirectory(). Current directory works like a global variable and may be changed unexpectedly. – Clinkerbuilt

You want the local dev to be able to override the connection strings or ports in launchSettings. "environmentVariables": { "ConnectionStrings.UserDatabase": "Server..." is better than relying on app settings. This way it always points to dev, qa and the developer overrides it in launch settings. Counting on a dev to remember to undo appsettings changes is just not a good idea – Nobe

Answer 2

I know I'm late to the party here, but if you do want to set the connection string in launchSettings.json, you can create an environment variable called ConnectionStrings:YourConnectionStringName, then it will get picked up as a connection string.

In your specific example:

{
  "iisSettings": {
    "windowsAuthentication": false,
    "anonymousAuthentication": true,
    "iisExpress": {
      "applicationUrl": "http://localhost:56244/",
      "sslPort": 0
    }
  },
  "profiles": {
    "IIS Express": {
      "commandName": "IISExpress",
      "launchBrowser": true,
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development",
        "ConnectionStrings:UserDatabase": "Server=DESKTOP-2K2A6GN;Database=Assignment4;Trusted_Connection=True;"
      }
    },
    "Assignment4.Server": {
      "commandName": "Project",
      "launchBrowser": true,
      "environmentVariables": {
        "ASPNETCORE_ENVIRONMENT": "Development",
        "ConnectionStrings:UserDatabase": "Server=DESKTOP-2K2A6GN;Database=Assignment4;Trusted_Connection=True;"
      },
      "applicationUrl": "http://localhost:56248/"
    }
  }
}

Amine

Answer 3

You can change in file UserContext.cs

public class DesignTimeDbContextFactory : IDesignTimeDbContextFactory<AppDbContext>
    {
        public UserContext CreateDbContext(string[] args)
        {
            IConfiguration configuration = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("appsettings.json").Build();
            var builder = new DbContextOptionsBuilder<AppDbContext>();
            var connectionString = configuration.GetConnectionString("UserDatabase");
            builder.UseSqlServer(connectionString);
            return new AppDbContext(builder.Options);
        }
    }

And in file Startup.cs

public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<UserContext>(options =>
               options.UseSqlServer(Configuration.GetConnectionString("UserDatabase"),
                   b => b.MigrationsAssembly("xxx.Data")));
    }

Duodecimo

I feel sorry for the guy who works a year later and see code like that. You need to refactor for whatever reason to another place in code or change your app settings and this code goes boom. App is down for days till they figure out where the file has to be located. – Nobe