CODEWITHSUNDEEP
c#asp.net.netentity-framework
Kemal Tezer Dilsiz
Kemal Tezer Dilsiz

Reputation: 4009

How can I implement DbContext Connection String in .NET Core?

My situation is quite similar to this link or at least my code is similar and I am trying to find a way to apply this same method in .NET Core syntax.

Pass connection string to code-first DbContext

My specific code is as follows:

public partial class CompanyFormsContext : DbContext
{
    public CompanyFormsContext()
        : base("name=CompanyFormsContext")
    {
    }

    public CompanyFormsContext(string connName)
        : base("name=" + connName)
    {
    }
    ...
}

I get an error saying:

Error CS1503 Argument 1: cannot convert from 'string' to 'Microsoft.EntityFrameworkCore.DbContextOptions' CompanyForms..NETCoreApp,Version=v1.0

when I go over the parenthesis in base("name=CompanyFormsContext") or base("name=" = connName).

What is the correct way of implementing this functionality in .NET Core?

Edit:

I wanted to share that I have the following information for database connection in my appsettings.json file: (However, I do not have settings in the startup.cs)

  "Data": {
    "CompanyFormsContext": {
      "ConnectionString": "Server=(localdb)\\projectsv13;Database=companyforms;Trusted_Connection=True;"
    },
    "CompanyFormsContextQA": {
      "ConnectionString": "Server=(localdb)\\projectsv13;Database=companyforms;Trusted_Connection=True;"
    }
  }

and I have found the following link Adding DbContextOptions in Startup.cs not registering data store in the website and I am wondering if a simple protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) will be enough to fix my connection or not?

From the link: 

services.AddEntityFramework(Configuration)
    .AddSqlServer()
    .AddDbContext<MyDbContext>(
        options =>
        options.UseSqlServer(Configuration.Get("Data:CompanyFormsContext:ConnectionString"))
    );

Do I need this kind of a service in my Startup.cs?

Upvotes: 59

Views: 131760

Answers (8)

Terry Gilman
Terry Gilman

Reputation: 51

So I searched all over for a solution to my issue which was I needed to dynamically connect to a database based on data that I do not have until time to do the connecting. Basically, dynamic context. I did not have the data being passed on the URL, and I did not have a short list of possible databases to attach to). So, here is my solution to the issue posed. This code will allow you to use the appsettings.json file to define the connection string with a placeholder to be replaced at run time by the code. This can be done within a controller or some other class as you see fit.

I am using both a static context and a dynamic context but you could use dynamic only.

Hopefully someone will stumble upon this and say thank god... although probably just as likely someone will say, this guy is crazy. Either way, enjoy.

using System;
using System.Globalization;
using System.Linq;
using Microsoft.Extensions.Configuration;

namespace CallCenter.Repositories
{
    public class TestRepository : ITestRepository 
    {
        private readonly InsuranceContext _context;
        public TestRepository(InsuranceContext context)
        {
            _context = context;
        }

        public void Add(string passedInStringWhichTellsWhatDatabaseToUse)
        {
            var builder = new ConfigurationBuilder().AddJsonFile("appsettings.json");

            var configuration = builder.Build();
            var connectionString = configuration.GetConnectionString("DefaultConnection");
            

                var agencyContext = new AgencyContext(connectionString.Replace("Database=replacethisstring", "Database=" + passedInStringWhichTellsWhatDatabaseToUse));

                var company = agencyContext.Companys.FirstOrDefault(x => x.ColumnNameInDb == "xyz");
                if (company != null)
                {
                    companyId = company.CompanyId.ToString();
                }

... your other code here which could include the using the passed in _context from the injected code (or you could not have any context passed in and just use dynamic context
            }

        }
    }
}

//The AgencyContext class would look like this:

using Microsoft.EntityFrameworkCore;

namespace CallCenter.Entities
{
    public class AgencyContext : DbContext
    {
        public AgencyContext(string connectionString) : base(GetOptions(connectionString))
        {
            
        }

        private static DbContextOptions GetOptions(string connectionString)
        {
            return SqlServerDbContextOptionsExtensions.UseSqlServer(new DbContextOptionsBuilder(), connectionString).Options;
        }

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

//The startup.c IServiceProvider module has this:

        public IServiceProvider ConfigureServices(IServiceCollection services)
        {
            services.AddOptions();
           
            services.AddDbContext<InsuranceContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"), b => b.UseRowNumberForPaging()));
            services.AddScoped<ITestRepository , TestRepository >();
....
}

And finally the appsettings.json file would have this in it:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=yourservername;Database=replacethisstring;User ID=youruserid;Password=yourpassword;TrustServerCertificate=True;Trusted_Connection=False;Connection Timeout=30;Integrated Security=False;Persist Security Info=False;Encrypt=True;MultipleActiveResultSets=True;",
}
}

Upvotes: 3

Dawood Awan
Dawood Awan

Reputation: 7318

The accepted answers are good when the connection string is in the appsettings.json file, but in some cases we need to build the connection string dynamically. e.g. in a multi-tenant system. Where each tenant has their own database.

In such kinds of systems the storage location of the connection strings can change over time, e.g. in initial days it may be the DB, config files, then later on moved to a more secure storage.

In such cases, you can follow the following steps.

1 - Introduce an Interface e.g. IConnectionStringBuilder. Then implement the ConnectionStringBuilder.

public interface IConnectionStringBuilder
{
    string TenantConnectionString(string tenantIdentifier);
}

public class ConnectionStringBuilder : IConnectionStringBuilder
{
    private readonly DbConnectionStringsConfig _stringsConfig;
    public ConnectionStringBuilder(DbConnectionStringsConfig stringsConfig)
    {
        _stringsConfig = stringsConfig;
    }

    public string TenantConnectionString(string tenantIdentifier)
    {
        return @$"Server={_stringsConfig.Server};Database={_stringsConfig.Database};Trusted_Connection=True;MultipleActiveResultSets=true;Encrypt=False;";
    }
}

2 - The DbConnectionStringsConfig is just a class to map the appsettings.json config to in code.

  "DbConnectionStringsConfig": {
    "Server": "localhost",
    "Database": "MyDB",
    "UserId": "DEV_USER",
    "Password": "DEV_PASWORD",
  }

3 - Setup the new classes in Startup or Program.cs

    services.AddSingleton<DbConnectionStringsConfig>(configuration.GetSection(nameof(DbConnectionStringsConfig)).Get<DbConnectionStringsConfig>());

and then

    services.AddTransient<IConnectionStringBuilder, ConnectionStringBuilder>();

4 - Then to configure the DbContext do this:

services.AddDbContext<TenantDbContext>((s, o) =>
{
    var connectionStringBuilder = s.GetService<IConnectionStringBuilder>();

    // read the current tenant from HttpHeader in ITenantContext
    var tenant = s.GetService<ITenantContext>()?.Tenant;
    
    // build the connectionString for the current Tenant
    var connectionString = connectionStringBuilder.TenantConnectionString(tenant?.Identifier) 
                ?? DbConstants.DEV_DEFAULT_TENANT_CONNECTION_STRING;

    o.UseSqlServer(connectionString, builder => builder.MigrationsAssembly(typeof(TenantDbContext).Assembly.FullName));
});

The beauty of this solution is that, in the future whenever the location of the ConnectionStrings storage is changed, all you have to do is implement a version of IConnectionStringBuilder and swap the type, so something like this.

public class SuperSecretConnectionStringBuilder : IConnectionStringBuilder
{
...
}

   services.AddTransient<IConnectionStringBuilder, SuperSecretConnectionStringBuilder>();

You can even have different types of ConnectionStringBuilders for different environments so for DEV, STAGING and QA you can use the appsettings.config file, and for PROD use something else.

Upvotes: 1

kudoku
kudoku

Reputation: 329

If you are looking for simple less code solution, this should do, just add another constructor with static factory method calls as parameter for base class :

 public YourDbContext(string connectionString)
        : base(new DbContextOptionsBuilder().UseSqlServer(connectionString).Options)
    {   
    }

Upvotes: 5

Chris Berlin
Chris Berlin

Reputation: 1057

An easy way is, just to use an optionbuilder to get the Context:

    public static MyDbContext GetMyDbContext(string databaseName)
    {
        var optionsBuilder = new DbContextOptionsBuilder<MyDbContext>();

        optionsBuilder.UseSqlServer($@"Data Source=.\SqlExpress;Initial Catalog={databaseName};Integrated Security=True");

        return new MyDbContext(optionsBuilder.Options);

    }

Upvotes: 8

Lakmal
Lakmal

Reputation: 918

Startup.cs for static connection

services.AddScoped<MyContext>(_ => new MyContext(Configuration.GetConnectionString("myDB")));

Table1Repository.cs for dynamic connection

using (var _context = new MyContext(@"server=....){
context.Table1....
}

MyContext.cs

public MyContext(string connectionString) : base(GetOptions(connectionString))
{
}

private static DbContextOptions GetOptions(string connectionString)
{
    return SqlServerDbContextOptionsExtensions.UseSqlServer(new DbContextOptionsBuilder(), connectionString).Options;
}

Upvotes: 3

Ricardo Peres
Ricardo Peres

Reputation: 14525

Another option would be to call the base constructor that takes a DbContextOptions:

public BooksContext(string connectionString) : base(GetOptions(connectionString))
{
}

private static DbContextOptions GetOptions(string connectionString)
{
    return SqlServerDbContextOptionsExtensions.UseSqlServer(new DbContextOptionsBuilder(), connectionString).Options;
}

Upvotes: 88

Timur Lemeshko
Timur Lemeshko

Reputation: 2879

IMO best practice:

add to your configuration.json:

     "ConnectionStrings": {
    "BooksContext": "Server=MyServer;Database=MyDb;Trusted_Connection=True;"
  }

and to initialize section:

services.AddDbContext<BooksContext>(options => options.UseSqlServer(configuration.GetConnectionString(nameof(BooksContext))));

Upvotes: 12

Peter
Peter

Reputation: 6035

Generally you are going to want to read it from config at start-up, and then use the connection string to configure an Entity Framework DbContext service for your process.

1) Add a line to your appsettings.json:

"DbConnectionString": "Server=s;Database=db;Trusted_Connection=True;",

2) Read the line in you Startup.cs class (after the Startup method is called to build the Configuration - so usually in the ConfigureServices method) like this:

var connection = Configuration["DbConnectionString"];

3) If using Entity Framework add a database context service (MyDbContext is the context class generated by EF). You also want to tell the built-in dependency injection how to instantiate your database context:

services.AddDbContext<MyDbContext>(options => options.UseSqlServer(connection));
services.AddScoped<IMyDbContext, MyDbContext>();

Where IMyDbContext is (at it's simplist) just an interface you've extracted from your MyDbContext

4) Now you can define your controller to take a MyDbContext and the DI will take care of building it and passing it in when the controller is invoked:

public MyController(IMyDbContext context)
{
    _context = context  // store for later use
}

Upvotes: 23

Related Questions