Asp.net 5 Handling multi-databases structure

Question

I'm trying to implement multi-database structure with automatic migrations. databases uncountable and i can't set the a fixed connection strings. i tried many ways to handle it, some ways worked but could not handle automatic migrations. I have two different DbContexts and different connection strings the question is: Is this a good way to handle it or there is a better one ?

    public class CategoriesController : Controller
    {
        private readonly UserDbContext _context;

        public CategoriesController(UserDbContext context, ApplicationDbContext _Maincontext)
        {
            var conn = _Maincontext.Users.FirstOrDefault().DbId;
            context.Database.SetConnectionString($"Data Source=.\\SQLEXPRESS;Initial Catalog={conn};Integrated Security=False; uid=sa;password=123;");
            context.Database.Migrate();
            _context = context;
        }

        // GET: Categories
        public async Task<IActionResult> Index()
        {
            return Ok(await _context.Categories.ToListAsync());
        }

    
    }

That is a terrible way to solve your problem. Are you different databases different DbContext or the same DbContext but with different connection strings? — Neil, Aug 19 '21 at 05:23
Connection strings are added in the `AddDbContext` call in Startup. If you know the connection string at startup, why hard-code it in the controller? — Panagiotis Kanavos, Aug 24 '21 at 15:40
@PanagiotisKanavos, How can i use dynamic connection strings in startup file? — KHAL, Aug 24 '21 at 22:06

score 0 · Accepted Answer · answered Sep 03 '21 at 21:07

I've used the Users Claims to handle this problem

Startup

        public void ConfigureServices(IServiceCollection services)
        {
            // First Context which has a static connection string
            services.AddDbContext<ApplicationDbContext>(options =>
                options.UseSqlServer(
                    Configuration.GetConnectionString("mainDb")));

            // To inject HttpContext for each request
            services.TryAddSingleton<IHttpContextAccessor, HttpContextAccessor>();

            // Second Context which has a dynamic connection strings
            services.AddDbContext<UserDbContext>();

            ).AddEntityFrameworkStores<ApplicationDbContext>();
            services.AddControllersWithViews();
        }

UserDbContext class

 public class UserDbContext : DbContext
    {
        private readonly HttpContext _httpContext;

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

        //..

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                //First get user claims    
                var claims = _httpContext?.User.Claims.ToList();

                //Filter specific claim    
                string dbName = claims?.FirstOrDefault(x => x.Type.Equals("db", StringComparison.OrdinalIgnoreCase))?.Value;


                if (dbName == null) dbName = "TempDebugDb";
                optionsBuilder.UseSqlServer(GetConnectionString(dbName));
             
            }
        }

        private static string GetConnectionString(string dbName)
        {
            return $"Data Source=.\\SQLEXPRESS;Initial Catalog={dbName};Integrated Security=False; uid=sa;password=*****;";
        }


        public DbSet<Category> Categories { get; set; }


        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
        }
        
    }

Asp.net 5 Handling multi-databases structure

1 Answers1