Best approach for linking MVC application with multiple and switch databases?
I am working on a new project and a client asked me to create separate SQL databases for their three different branches. The main problem is switching connections and creating a relationship between them in an Asp.Net MVC project. To develop the application, I will use the Entity Framework Code First Approach. I am thinking of creating three different connections in the application's Web.Config file.
SQL connections
<add name="Con1" connectionString="Data Source=.\SqlExpress;Initial Catalog=dbBranch1;
Integrated Security=True" providerName="System.Data.SqlClient" />
<add name="Con2" connectionString="Data Source=.\SqlExpress;Initial Catalog=dbBranch2;
Integrated Security=True" providerName="System.Data.SqlClient" />
<add name="Con3" connectionString="Data Source=.\SqlExpress;Initial Catalog=dbBranch3;
Integrated Security=True" providerName="System.Data.SqlClient" />
Now the problem is that I don't know how to switch connections based on the user's selection of the branch from the dropdown. If the user selects Branch1 from the dropdown when inserting a record, the record must be inserted into the Branch1 database . How do I set up three connections in a Db application context file?
Db application context
public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
public ApplicationDbContext()
: base("Con1", throwIfV1Schema: false)
{
}
public static ApplicationDbContext Create()
{
return new ApplicationDbContext();
}
}
Likewise, joining three database records and getting the result will also be a headache. I may need to write a stored procedure at seeding time to get the records.
I suggested to the client to create one database, have a branch table and normalize. But it needs three separate databases. What's the best architecture or approach to achieve this?
source to share
Instead of passing it hardcoded connectionString
to your class context
. pass a variable and pass that variable to the bass class.
public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
public ApplicationDbContext(string connString)
: base(connString, throwIfV1Schema: false)
{
}
}
Then initialize your connection string as shown below
ApplicationDbContext dbcontext = new ApplicationDbContext("con1");
OR
ApplicationDbContext dbcontext = new ApplicationDbContext("Con2");
source to share