2

I am working on a project and I am asked to program application in a way that it will automatically create new database for every new user register into the application.

Armaan Labib
  • 125
  • 1
  • 2
  • 16
  • Not sure if code first will help you here. It's easier to just execute a simple SQL CREATE DATABASE script every time a user registers. – Teodor Kurtev Apr 26 '17 at 07:41
  • Is it acceptable to use two `DbContext` classes, one as a "master" which will keep the users and another one which will be created with the connection string based on the logged in user? You can create the databases in the `Register` action in the controller by using a generated connection string. – granit Apr 26 '17 at 09:18
  • @ArmaanLabib You don't need 100 `DbContext` classes but you can instantiate the same class with a different connection string. See the answer I added. – granit Apr 26 '17 at 14:37

2 Answers2

0

You can define two DbContext classes, one would be the database which will store users ( call it ApplicationDbContext which extends IdentityDbContext) and the other can be a custom db context (call it MyContext).

Appart from the DefaultConnection which will be used by ApplicationDbContext add a base connection string in the Web.config which could be something like this (notice the {0} which you can then use in string.Format()):

<add name="BaseConnectionString" connectionString="Data Source=(local)\SQLEXPRESS;Initial Catalog={0};Integrated Security=True"
  providerName="System.Data.SqlClient" />

In the Register action in the AccountController after the user is created successfully, you can create a new instance of the db context with the connection string generated from BaseConnectionString and the Id of the created user.

string baseConnectionString = ConfigurationManager.ConnectionStrings["BaseConnectionString"].ConnectionString;
MyDbContext newContext = new MyDbContext(string.Format(baseConnectionString, "MyApplicationDB_" + user.Id));

then use something to trigger the database creation (if it does not exist) e.g. 

var user = newContext.Posts.FirstOrDefault(); // Assuming you have a DbSet<Posts> there

With the constructor defined like the one below, it will create the database if does not exist already and the database name will be unique as the user id is e.g. MyApplicationDB_9b8cd172-7a3a-4a09-965a-2204e97bc850. Here you could also do any other logic like insert some data which would serve as context data for your newly created database.

The constructor I used for MyDbContext takes the connection string as an argument and calls the base constructor and it must be initialized to create the database if it does not exist.

public MyDbContext(string connectionString)
    : base(connectionString)
{
    Database.SetInitializer<MyDbContext>(new CreateDatabaseIfNotExists<MyDbContext>());    
}

Now, in your controllers, you can always use an instance of MyContext to query the database entities and when you instantiate it, give the connection string based on the logged in user id.

string baseConnectionString = ConfigurationManager.ConnectionStrings["BaseConnectionString"].ConnectionString;
MyDbContext context = new MyDbContext(string.Format(baseConnectionString, "MyApplicationDB_" + User.Identity.GetUserId())); // make sure you are using Microsoft.AspNet.Identity for this
var entitiesOfThisUserDb = context.Posts.ToList(); // Fetches the posts in the database we created for the logged in user
granit
  • 570
  • 4
  • 9
  • @ArmaanLabib in the `EmployeeController` (assuming you named it that way), check out how you instantiate the context. It should be like I mentioned in the post: `MyDbContext context = new MyDbContext(string.Format(baseConnectionString, "MyApplicationDB_" + User.Identity.GetUserId())); ` and if you add entities in `context.Employees` it inserts them in the correct database. – granit Apr 27 '17 at 13:12
  • @ArmaanLabib Make sure you call the variable `baseConnectionString` like I assigned in my answer (from `ConfigurationManager`) and not the string `"baseConnectionString"`. What is throwing the null reference exception? – granit Apr 27 '17 at 16:59
  • That might be because you are calling it immediately after the login. This question might help you: http://stackoverflow.com/q/33951881/2685912. Basically it says that you can retreive user id in that manner in the succeeding requests but not the same request you are logging the user in which is what you are doing. – granit Apr 27 '17 at 20:58
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/142920/discussion-between-granit-and-armaan-labib). – granit Apr 28 '17 at 13:13
0

you just need to delete your connection string

1: if you don't have any migration then add one

1: Enable-migrations

2: add-migration InitialMigration

3: Update-database

if you have already migrations available the just use

1: Update-database

Bilal Ch
  • 1
  • 2