Connect multiple Databases to .NET core API project via Entity Framework Core

Question

today i'm learning the new ASP.net core API 3.1 and i want to transfert my old websites from MVC4 to web API. All work good except one thing. The database connection. In my old website, i've a database for each clients (10/15 DB's) and i use main database to get the client databse after connection.

Here is my code for my Old DBContext (for local test here)

public DBContext(string database)
 : base("Data Source=***SQLServer***;Initial Catalog=" + database + ";Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False"){}

I get the database name string from the AdminDatabase in the DAL and pass it to DBContext.

But now with the services for connections, i don't understand how to do that. If i place the connection string in appsettings json, i can't pass database name parameter.

I try to specify the connection string directly in the startup.cs file but i've seen somewhere it's not secure to do that, the appsetting.json keep connection strings secret...

If you have idea, let me know friends ;)

Do you want to connect to multiple database in your current core project? — LouraQ
– LouraQ, Commented Sep 22, 2020 at 8:52
yes sir, like i did in my old mvc project i want to be able to connect to multiple database dynamicaly — Maurice Chocoswiss
– Maurice Chocoswiss, Commented Sep 22, 2020 at 12:18
What's your mean about dynamicaly ? If you want to connect to multiple database, you can create multiple dbcontext to connect to corresepond connection string in appsetting.json, you can refer to :learn.microsoft.com/en-us/aspnet/core/data/ef-mvc/… — LouraQ
– LouraQ, Commented Sep 23, 2020 at 5:33
Yeah i know this option. But database connection depend on user connected. For exemple Jhon connect to DB1 because in his company profile the DB is DB1, and for Jack it's DB2. it's more clear ? and also, i want to be able to create DB, set the name in company parameter in the admindatabase and when the user connected, it use the DB set in admindatabase, and not need to modify the appsettings each time — Maurice Chocoswiss
– Maurice Chocoswiss, Commented Sep 23, 2020 at 9:31

LouraQ · Accepted Answer · 2020-09-24 05:25:15Z

2

For exemple Jhon connect to DB1 because in his company profile the DB is DB1, and for Jack it's DB2. it's more clear ? and also, i want to be able to create DB, set the name in company parameter in the admindatabase and when the user connected, it use the DB set in admindatabase, and not need to modify the appsettings each time

First, you can store all connection strings in appsetting.json file:

{
  "ConnectionStrings": {
    "DefaultConnection": "...",
    "DB1Connection": " ...",
    "DB2Connection": " ..."
    //...
  } 
}

Then ,in the dbcontext, inject HttpContext to get the information of the logged in user, by judging the information of the logged-in user, get correspond connection string name dynamically in OnConfiguring method of DbContext.

   public class ApplicationDbContext : IdentityDbContext<IdentityUser>
        {
            private readonly HttpContext httpContext;
            public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options, IHttpContextAccessor httpContextAccessor)
                : base(options)
            {
                httpContext = httpContextAccessor.HttpContext;
            }
            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                IConfigurationRoot configuration = new ConfigurationBuilder()
                    .SetBasePath(AppDomain.CurrentDomain.BaseDirectory)
                    .AddJsonFile("appsettings.json")
                    .Build();
                var connectionName = "DefaultConnection";// default connection string
                var userName = httpContext.User?.Identity?.Name;
               //If you distinguish according to the company you belong to, 
               //you can also determine to obtain different connection strings by obtaining the company to which the logged-in user belongs
                if(userName == "Jhon")
                {
                    connectionName = "DB1Connection";
                }
               else if (userName == "Jack"){
                    connectionName = "DB2Connection";
                } 
                optionsBuilder.UseSqlServer(configuration.GetConnectionString(connectionName));
          
            } 
        }

answered Sep 24, 2020 at 5:25

LouraQ

6,9312 gold badges9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Maurice Chocoswiss Over a year ago

I don't have time to spent on this for the moment, i'll came back here later to test your solution ;) thank you for your time ;)

Maurice Chocoswiss Over a year ago

Hi LouraQ, i've time now to test your solution and it's not exactly what i wan't to do. I want to get the DB name for the user and just change the DB in the connection string. For example John DB is "abc" and Jack one is "klm". But this value is stored in an other database where all user are. These database is accessible with an AdminDal (an her Interface IAdminDal). The value is get by accessing the entreprise attached to the user.

Collectives™ on Stack Overflow

Connect multiple Databases to .NET core API project via Entity Framework Core

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related