Dynamically change connection string in Asp.Net Core

Question

I want to change sql connection string in controller, not in ApplicationDbContext. I'm using Asp.Net Core and Entity Framework Core.

For example:

public class MyController : Controller {
    private readonly ApplicationDbContext _dbContext
    public MyController(ApplicationDbContext dbContext)
    {
        _dbContext = dbContext;
    }
    private void ChangeConnectionString()
    {
    // So, what should be here?
    } }

How can I do this?

Answer 1

This is enough if you want to choose a connection string per http request, based on the active http request's parameters.

    using Microsoft.AspNetCore.Http;

    //..

    services.TryAddSingleton<IHttpContextAccessor, HttpContextAccessor>();

    services.AddDbContext<ERPContext>((serviceProvider, options) =>
        {
            var httpContext = serviceProvider.GetService<IHttpContextAccessor>().HttpContext;
            var httpRequest = httpContext.Request;
            var connection = GetConnection(httpRequest);
            options.UseSqlServer(connection);
        });

Update

A year or so later, my solution looks like bits and pieces from other answers here, so allow me to wrap it up for you.

You could add a singleton of the HttpContextAccessor on your startup file:

services.TryAddSingleton<IHttpContextAccessor, HttpContextAccessor>();
services.AddDbContext<ERPContext>();

This will resolve the injection on your context constructor:

public class ERPContext : DbContext
{
    private readonly HttpContext _httpContext;

    public ERPContext(DbContextOptions<ERPContext> options, IHttpContextAccessor httpContextAccessor = null)
        : base(options)
    {
        _httpContext = httpContextAccessor?.HttpContext;
    }

    //..

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            var clientClaim = _httpContext?.User.Claims.Where(c => c.Type == ClaimTypes.GroupSid).Select(c => c.Value).SingleOrDefault();
            if (clientClaim == null) clientClaim = "DEBUG"; // Let's say there is no http context, like when you update-database from PMC
            optionsBuilder.UseSqlServer(RetrieveYourBeautifulClientConnection(clientClaim));
        }
    }

    //..
}

And this will give you a clean way to access and extract a claim and decide your connection.

As @JamesWilkins stated on the comments, OnConfiguring() will be called for each instance of the context that is created.

Notice the optional accessor and the !optionsBuilder.IsConfigured. You will need them to ease your tests where you would be overriding your context configuration.

Answer 2

We have a case similar to you. What we've done is use the implementationfactory overload of the IServiceCollection in the ConfigureServices method of the Startup class, like so:

//First register a custom made db context provider
services.AddTransient<ApplicationDbContextFactory>();
//Then use implementation factory to get the one you need
services.AddTransient(provider => provider.GetService<ApplicationDbContextFactory>().CreateApplicationDbContext());

It is very difficult for me right now to implement CreateApplicationDbContext for you, because it totally depends on what you want exactly. But once you've figured that part out how you want to do it exactly, the basics of the method should look like this anyway:

public ApplicationDbContext CreateApplicationDbContext(){
  //TODO Something clever to create correct ApplicationDbContext with ConnectionString you need.
} 

Once this is implemented you can inject the correct ApplicationDbContext in your controller like you did in the constructor:

public MyController(ApplicationDbContext dbContext)
{
    _dbContext = dbContext;
}

Or an action method in the controller:

public IActionResult([FromServices] ApplicationDbContext dbContext){
}

However you implement the details, the trick is that the implementation factory will build your ApplicationDbContext everytime you inject it.

Tell me if you need more help implementing this solution.

Update #1 Yuriy N. asked what's the difference between AddTransient and AddDbContext, which is a valid question... And it isn't. Let me explain.

This is not relevant for the original question.

BUT... Having said that, implementing your own 'implementation factory' (which is the most important thing to note about my answer) can in this case with entity framework be a bit more tricky than what we needed.

However, with questions like these we can nowadays luckily look at the sourcecode in GitHub, so I looked up what AddDbContext does exactly. And well... That is not really difficult. These 'add' (and 'use') extension methods are nothing more than convenience methods, remember that. So you need to add all the services that AddDbContext does, plus the options. Maybe you can even reuse AddDbContext extension method, just add your own overload with an implementation factory.

So, to come back to your question. AddDbContext does some EF specific stuff. As you can see they are going to allow you to pass a lifetime in a later release (transient, singleton). AddTransient is Asp.Net Core which allows you to add any service you need. And you need an implementation factory.

Does this make it more clear?

Answer 3

I was able to change the connection string for each request by moving the connection string logic into the OnConfiguring method of the DbContext.

In Startup.cs#ConfigureServices method: services.AddDbContext<MyDbContext>();

In MyDbContext.cs, I added the services I needed injected to the constructor.

    private IConfigurationRoot _config;
    private HttpContext _httpContext;

    public MyDbContext(DbContextOptions options, IConfigurationRoot config, IHttpContextAccessor httpContextAccessor) 
          : base(options)
    {
        _config = config;
        _httpContext = httpContextAccessor.HttpContext;
    }

Then override OnConfiguring:

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        var connString = BuildConnectionString(); // Your connection string logic here

        optionsBuilder.UseSqlServer(connString);
    }

Answer 4

The answers of @ginalx and @jcmordan fit my use case perfectly. The thing I like about these answers is that I can do it all in Startup.cs and keep all other classes clean of construction code. I want to supply an optional querystring parameter to a Web Api request and have this substituted into the base connection string which creates the DbContext. I keep the base string in the appsettings.json, and format it based on the passed in parameter or a default if none supplied, i.e:

"IbmDb2Formatted": "DATABASE={0};SERVER=servername;UID=userId;PWD=password"

Final ConfigureServices method for me looks like (obvs. I am connecting to DB2 not SQL, but that's incidental):

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddTransient<IHttpContextAccessor, HttpContextAccessor>();

        services.AddDbContext<Db2Context>(((serviceProvider, options) =>
        {
            var httpContext = serviceProvider.GetService<IHttpContextAccessor>().HttpContext;
            var httpRequest = httpContext.Request;

            // Get the 'database' querystring parameter from the request (if supplied - default is empty).
           // TODO: Swap this out for an enum.
            var databaseQuerystringParameter = httpRequest.Query["database"].ToString();

            // Get the base, formatted connection string with the 'DATABASE' paramter missing.
            var db2ConnectionString = Configuration.GetConnectionString("IbmDb2Formatted");

            if (!databaseQuerystringParameter.IsNullOrEmpty())
            {
                // We have a 'database' param, stick it in.
                db2ConnectionString = string.Format(db2ConnectionString, databaseQuerystringParameter);
            }
            else
            {
                // We havent been given a 'database' param, use the default.
                var db2DefaultDatabaseValue = Configuration.GetConnectionString("IbmDb2DefaultDatabaseValue");
                db2ConnectionString = string.Format(db2ConnectionString, db2DefaultDatabaseValue);
            }

            // Build the EF DbContext using the built conn string.
            options.UseDb2(db2ConnectionString, p => p.SetServerInfo(IBMDBServerType.OS390));
        }));

        services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);

        services.AddSwaggerGen(c =>
        {
            c.SwaggerDoc("v1", new Info
            {
                Title = "DB2 API",
                Version = "v1"
            });
        });
    }

Answer 5

Although late, but the simplest trick in EF Core is using nuget Microsoft.EntityFrameworkCore.Relational:

_dbContext.Database.GetDbConnection().ConnectionString = "NEW_CONN_STRING";

This is useful when a connection string is not present in your application config/settings for any reason or you want to deal with multiple databases with same structure using one instance of DbContext (again, for any reason).

Being Permanently or Temporarily depends on type the injection life-cycle you choose for DbContext. It will be permanent if you inject it as Singleton service, which is not recommended.

Answer 6

All other answers did not worked for me. so I would like to share my approach for the people who work to change DB connection string at runtime.

My application was built with asp.net core 2.2 with Entity Framework and MySql.

StartUp.cs

public void ConfigureServices(IServiceCollection services)
{
    ...

    services.AddDbContext<MyDbContext>();

    ...

MyDbContext Class

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

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

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (DbManager.DbName != null && !optionsBuilder.IsConfigured)
        {
            var dbName = DbManager.DbName;
            var dbConnectionString = DbManager.GetDbConnectionString(dbName);
            optionsBuilder.UseMySql(dbConnectionString);
        }
    }

    ...

Json - File that has a Connection Info

[
  {
    "name": "DB1",
    "dbconnection": "server=localhost;port=3306;user=username;password=password;database=dbname1"
  },
  {
    "name": "DB2",
    "dbconnection": "server=localhost;port=3306;user=username;password=password;database=dbname2"
  }
]

DbConnection Class

using System.Collections.Generic;
using System.Globalization;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;


public class DbConnection
{
    [JsonProperty("name")]
    public string Name { get; set; }

    [JsonProperty("dbconnection")]
    public string Dbconnection { get; set; }

    public static List<DbConnection> FromJson(string json) => JsonConvert.DeserializeObject<List<DbConnection>>(json, Converter.Settings);
}

    internal static class Converter
    {
        public static readonly JsonSerializerSettings Settings = new JsonSerializerSettings
        {
            MetadataPropertyHandling = MetadataPropertyHandling.Ignore,
            DateParseHandling = DateParseHandling.None,
            Converters =
            {
                new IsoDateTimeConverter { DateTimeStyles = DateTimeStyles.AssumeUniversal }
            },
        };
    }
}

DbConnectionManager Class

public static class DbConnectionManager
{
    public static List<DbConnection> GetAllConnections()
    {
        List<DbConnection> result;
        using (StreamReader r = new StreamReader("myjsonfile.json"))
        {
            string json = r.ReadToEnd();
            result = DbConnection.FromJson(json);
        }
        return result;
    }

    public static string GetConnectionString(string dbName)
    {
        return GetAllConnections().FirstOrDefault(c => c.Name == dbName)?.Dbconnection;
    }
}

DbManager Class

public static class DbManager
{
    public static string DbName;

    public static string GetDbConnectionString(string dbName)
    {
        return DbConnectionManager.GetConnectionString(dbName);
    }
}

Then, you would need some controller that set dbName up.

Controller Class

[Route("dbselect/{dbName}")]
public IActionResult DbSelect(string dbName)
{
    // Set DbName for DbManager.
    DbManager.DbName = dbName;

    dynamic myDynamic = new System.Dynamic.ExpandoObject();
    myDynamic.DbName = dbName;
    var json = JsonConvert.SerializeObject(myDynamic);
    return Content(json, "application/json");
}

You might have to do some trick something here and there. but you will get the Idea. At the beginning of the app, It doesn't have connection detail. so you have to set it up explicitly using Controller. Hope this will help someone.

Answer 7

That work for me:

public void ConfigureServices(IServiceCollection services)
{
    // .....
    services.TryAddSingleton<IHttpContextAccessor, HttpContextAccessor>();
    services.AddTransient<School360DbContext>(provider =>
    {
        return ResolveDbContext(provider, hostingEnv);
    });
    // ..
}

private MyDbContext ResolveDbContext(IServiceProvider provider, IHostingEnvironment hostingEnv)
{
    string connectionString = Configuration.GetConnectionString("DefaultConnection");

    string SOME_DB_IDENTIFYER = httpContextAccessor.HttpContext.User.Claims
        .Where(c => c.Type == "[SOME_DB_IDENTIFYER]").Select(c => c.Value).FirstOrDefault();
    if (!string.IsNullOrWhiteSpace(SOME_DB_IDENTIFYER))
    {
        connectionString = connectionString.Replace("[DB_NAME]", $"{SOME_DB_IDENTIFYER}Db");
    }

    var dbContext = new DefaultDbContextFactory().CreateDbContext(connectionString);

    // ....
    return dbContext;
}

Answer 8

I went for this solution:

Instead of

services.AddScoped<IMyDbContext, MyDbContext>();

I went for

services.AddTransient<IMyDbContext, MyDbContext>(resolver =>
{
    var context= resolver.GetService<MyDbContext>();
    var config = resolver.GetService<IConfiguration>();
    var connectionString = config.GetConnectionString("MyDb");
    context.GetDbConnection().ConnectionString = connectionString;
    return context;
});

Overwrite setting at runtime:

Configuration["ConnectionStrings:MyDb"] = newConnectionString;

Answer 9

I created a .net6 console app and loop 1 to 10 for inserting to test1 database and test2 database: Program.cs :

Console.WriteLine("Hello, World!");

for (int i = 1; i <= 10; i++)
{

    if (i % 2 == 0)
    {
        var _context = new AppDbContext("Data Source=.\\SQLEXPRESS;Initial Catalog=test2;Integrated Security=True"); // test2
        _context.Tbls.Add(new Tbl { Title = i.ToString() });
        _context.SaveChanges();
    }
    else
    {
        var _context = new AppDbContext("Data Source=.\\SQLEXPRESS;Initial Catalog=test1;Integrated Security=True"); // test1
        _context.Tbls.Add(new Tbl { Title = i.ToString() });
        _context.SaveChanges();
    }
}

AppDbContext.cs :

public partial class AppDbContext : DbContext
    {
        public AppDbContext(string connectionString) : base(GetOptions(connectionString))
        {
        }

        public virtual DbSet<Tbl> Tbls { get; set; }

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

Answer 10

Startup.cs for static connection

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

Repository.cs for dynamic connection

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

Table1MyContext.cs

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

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