How to create new database by new client with Net core 3 and Entity Framework?

Question

I try to use Net core to create an API for a CRM application.The problem is that i don't want to use only one big database for every client of my CRM.

• I Have Migration to create the database
• I Have A big ApplicationDBContext and many models

What i want is that, for every new client who subscribe, i generate a new database with the name of the company, so the APi use the connectionString with this new database for this new company. All Client use the same identity database to subscribe.

Is it possible with EntityFramework and .Net core 3 and how to do that please? Thank you :)

Answer 1

Yes, it is possible. Below, I have created a little sample in .NetCore(3.0) web api. The idea is you have to pass a company name in the HttpRequest header and later use it inside your DbContext class. In that way you will be able to get an instance of the DbContext with a connectionString that contains the new company name in your controller and create new database with the new company name.

1. In the appsettings.json create a section called sql that contains a connectionString. Do not specify a database name and leave it empty after = sign:

   "sql": { "connectionString": "Server=localhost;User ID=userId;Password=yourPass;Database=" }

2. Create a class SqlSettings that will be used to keep the connectionString:

   public class SqlSettings
   {
     public string ConnectionString { get; set; }
   
     public void SetDbName(string companyName)
     {
         int index = ConnectionString.LastIndexOf("=");
   
         if (index > 0)
             ConnectionString = ConnectionString.Substring(0, index);
   
         ConnectionString = $"{ConnectionString}={companyName}";
     }
   }
   

3. CustomContext class, it gets the necessary dependencies in its constructor using Dependency Injection and changes the connectionString:

   public class CustomContext : DbContext
   {
   
       public string CompanyName { get; private set; }
   
       private IOptions<SqlSettings> _sqlSettings;
   
       public CustomContext(DbContextOptions<CustomContext> options, IOptions<SqlSettings> sqlSettings, IHttpContextAccessor httpContextAccessor)
         : base(options)
       {
         GetDbName(httpContextAccessor.HttpContext.Request);
   
         if (string.IsNullOrEmpty(CompanyName))
             return;
   
         sqlSettings.Value.SetDbName(CompanyName);
   
         _sqlSettings = sqlSettings;
       }
   
       private void GetDbName(HttpRequest httpRequest)
       {
         try
         {
             CompanyName = httpRequest.Headers["CompanyName"];
         }
         catch (Exception)
         {
             CompanyName = "";
         }
       }
   
       public DbSet<User> Users { get; set; }
   
       protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
       {
         optionsBuilder.UseSqlServer(_sqlSettings.Value.ConnectionString);
       }
   } 
   

4. In our CustomContext class we have a table called Users, It has been created just as a test case:

   public class User
   {
       public Guid Id { get; set; }
       public string Email { get; set; }
       public string Password { get; set; }
   }
   

5. Here in Startup.cs class inside the ConfigureServices method we need to register our classes in order to be able to get them using DI.

   public void ConfigureServices(IServiceCollection services)
   {
       ......
   
       services.AddHttpContextAccessor();
       services.Configure<SqlSettings>(Configuration.GetSection("sql"));
       services.AddEntityFrameworkSqlServer().AddDbContext<CustomContext>();
   }
   

6. Now, we can use our CustomContext class inside any Controller, getting it as Action's parameter using Action Method Injection:

   [HttpGet()]
   public IActionResult Get([FromServices] CustomContext dbContext)
   {
       if (!string.IsNullOrEmpty(dbContext.CompanyName))
           dbContext.Database.EnsureCreated();
   
       return Ok();
   }
   

Thus, we can create a new database named as a company name that is extracting from every HttpRequest's header. Also, if the database with the same company name already exists, the Entity Framework will not create it again.

Answer 2

I would do in this way:

create a stored procedure which creates the new database for you, taking as input parameter the new database name and whatever other information is needed for the new client / tenant.

then at the first point when you need to create the new database, you get EF to call that sprocs and database is then created.

see also here as you are interested in how to do this with a parameter for the new database name:

SQL Server: use parameter in CREATE DATABASE