CODEWITHSUNDEEP
asp.net-coreasp.net-web-apientity-framework-core
Maurice Chocoswiss
Maurice Chocoswiss

Reputation: 177

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

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

Upvotes: 3

Views: 5017

Answers (1)

LouraQ
LouraQ

Reputation: 6891

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

Upvotes: 2

Related Questions