Reading connection string placed in ASP.NET Core from class library. Database First

Question

The structure of projects looks like this:

• Cars (ASP.NET Core MVC. Here we have a connection string)

• Cars.Persistence (ASP.NET Core Class library. Here we have Repository, Database First)

I've created a Model by the following command from this msdn docs:

Scaffold-DbContext "Server=PC\SQL2014XP;Database=Cars;Trusted_Connection=True;" 
    Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

So far so good. However, now carsContext has hard coded connection string in Cars.Persistence - ASP.NET Core Class library:

public partial class carsContext: DbContext
{
    public carsContext()
    {
    }

    public carsContext(DbContextOptions<carsContext> options)
        : base(options)
    {
    }

    public virtual DbSet<Cars> Cars { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer("Server=PC\SQL2014XP...");// hard coded 
                                                                  // connection string
        }
    }
}

At first, I thought to create own appsettings.json in my class library Cars.Persistence. However, according to this post it is not advisable to have appsettings.json file in Class Library..

I've read this approach, however, the hard coded string will appear again, if I will run this command again:

Scaffold-DbContext "Server=PC\SQL2014XP;Database=Cars;Trusted_Connection=True;" 
        Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

So my question is how can I use connection string(located in Cars project) in my class library Cars.Persistence?

UPDATE:

I've commented out the following code:

public partial class eshopContext : DbContext
{

     public eshopContext(DbContextOptions<eshopContext> options): base(options)
     {} 

     // public eshopContext(){}        

      /*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. See http://go.microsoft.com/fwlink/?LinkId=723263 for guidance on storing connection strings.
             optionsBuilder.UseSqlServer("Server=...");
         }

      }*/

 }

Answer 1

From this issue , the connection string added in the DbContext when scaffolding DbContext is default settings .If you don't want to have such a long and ugly connection string to display, you could use Named connection strings instead .

From this discussion , you could use -Connection name in the Scaffold-DbContext command to get the connection string which is set in appsetting.json of web application

Scaffold-DbContext -Connection name=DefaultConnection  Microsoft.EntityFrameworkCore.SqlServer -OutputDir DbModels

"ConnectionStrings": {
  "DefaultConnection": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=MVC2_2Db;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
},

The above code will generate the following code in DbContext

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer("name=DefaultConnection");
        }
    }

Or ,if you set the external connection string , you could remove the hard coded connection strings due to it will only be called when you forget to set the connection to the database .You could refer to the code added in Startup.cs show as below:

 var connection = @"Server=(localdb)\mssqllocaldb;Database=MVC2_2Db;Trusted_Connection=True;ConnectRetryCount=0";

 services.AddDbContext<Cars.Persistence.DbModels.MVC2_2DbContext>(options => options.UseSqlServer(connection));

Answer 2

You can take the advantage of .Net Core Dependency Injection and out of box features. Your connection string will remain in web project but you can use DB context without declaring any connection string in Class Library Project. I am sharing code sample from my existing project.

Set Connection String

You have referenced connection string in your start up and added to services. You don't need to define the connection string again and use the db context using Built in DI. The code could look like this !

Start up class

Set up your SQL config. Look closely at MigrationsAssembly this is where you would reference your class library project.

public static IServiceCollection AddCustomDbContext(this IServiceCollection services, IConfiguration configuration)
{

    // Add DbContext using SQL Server Provider
    services.AddDbContext<PaymentDbContext>(options =>
        options.UseSqlServer(configuration.GetConnectionString("myconnectionstring"), x => x.MigrationsAssembly("Payment.Persistence")));

    return services;
}

Context Class

This class is in your class library project.

public class PaymentDbContext : DbContext
    {
        public PaymentDbContext(DbContextOptions<PaymentDbContext> options)
            : base(options)
        {

        }

        public DbSet<Payments> Payments { get; set; }    


    }    

Use DI to access Context

    private readonly PaymentDbContext _context;


     public PaymentsRepository(PaymentDbContext dbContext)
     {
     _context = dbContext;
    }

Answer 3

Below is how I read connetion string from netcoreapp2.2. You can see how I config at here

I create 1 file name appsetting.json have structure like this

"WebJobSettings": {
        "DBConnectionString": "Data Source=.;Initial Catalog=CMSCore;Integrated Security=True"
    },

Then in my Program.cs

 public static class Program
        {
            public static IConfigurationRoot Configuration;

            public static void Main()
            {
                var serviceCollection = new ServiceCollection();
                ConfigureServices(serviceCollection);

                // create service provider
                var serviceProvider = serviceCollection.BuildServiceProvider();

                // entry to run app
                //serviceProvider.GetService<WebJob>().Run();
                serviceProvider.GetService<WebJob>().RunImageProcessQueue();
            }

            private static void ConfigureServices(IServiceCollection serviceCollection)
            {
                var currentDir = Directory.GetCurrentDirectory();

                // build configuration
                var configuration = new ConfigurationBuilder()
                    .SetBasePath(currentDir)
                    .AddJsonFile("appsettings.json", false)
                    .Build();
                serviceCollection.AddOptions();

                serviceCollection.Configure<WebJobSettings>(configuration.GetSection("WebJobSettings"));
                serviceCollection.Configure<QueueSettings>(configuration.GetSection("QueueSettings"));
                serviceCollection.Configure<AssetSettings>(configuration.GetSection("AssetSettings"));

                // add app
                serviceCollection.AddTransient<WebJob>();
            }

Then simply Configuration pattern in my WebJob.cs file like this

public class WebJob
{
    private readonly IOptions<WebJobSettings> _webJobSettings;
    private readonly IOptions<QueueSettings> _queueSettings;
    private readonly IOptions<AssetSettings> _assetSettings;

    public WebJob(
        IOptions<WebJobSettings> webJobSettings,
        IOptions<QueueSettings> queueSettings,
        IOptions<AssetSettings> assetSettings)
    {
        _webJobSettings = webJobSettings;
        _queueSettings = queueSettings;
        _assetSettings = assetSettings;
    }

Answer 4

You could use an Environment Variable in your Cars MVC project located in launchSettings.json. Something like "MSSQL_CONN_STR": "Server=PC\2014XP.."

Then in the Cars.Persistence class library you can access the Environment Variable like this

 protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
 {
    if (!optionsBuilder.IsConfigured)
    {         
     optionsBuilder.UseSqlServer(Environment.GetEnvironmentVariable("MSSQL_CONN_STR");
    }
}