Dynamically change connection string in Asp.Net Core

Question

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

For instance:

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

We have a case similar to you. What we did is use the implementationfactory overload of the IServiceCollection in the ConfigureServices method of the launch class, for example, like this:

//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());

CreateApplicationDbContext , , . , , , :

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

, ApplicationDbContext , :

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

:

public IActionResult([FromServices] ApplicationDbContext dbContext){
}

, , ApplicationDbContext , .

, .

№ 1 . , AddTransient AddDbContext, ... . .

.

... , " " ( , ) , .

, , , GitHub, , AddDbContext. ... . "" ( "") - , . , , AddDbContext, . , AddDbContext, .

, . AddDbContext EF . , ( , ). AddTransient - Asp.Net Core, . .

?

Answer 2

, HTTP- HTTP-.

    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);
        });

, . .

?

Answer 3

, OnConfiguring DbContext.

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

MyDbContext.cs , .

    private IConfigurationRoot _config;
    private HttpContext _httpContext;

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

OnConfiguring:

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

        optionsBuilder.UseSqlServer(connString);
    }

Answer 4

@ginalx @jcmordan . , , Startup.cs . querystring Web Api , DbContext. appsettings.json , , ..

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

ConfigureServices (obvs. DB2 SQL, ):

    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

:

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 6

, . , .

asp.net core 2.2 Entity Framework 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 -

[
  {
    "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

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

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

public static class DbManager
{
    public static string DbName;

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

, dbName.

[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");
}

, , - . . , . . , - .

Answer 7

I have an answer to this in another thread.

Dynamically change connection string in Asp.Net Core

Hope this helps

Answer 8

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;
}