How To Change/Switch The Database Dynamically In An ASP.NET MVC Application

Introduction

This is an example with code snippets to show how to switch or change the database (Connection string) dynamically while running a program in ASP.NET MVC 5 applications.

Let’s get straight to the task. First off, you need to have more than one databases so as to switch or change between them.

Code snippets

Let’s create a database with name “Database1”.


Let’s create a table as "AuditTable" with the columns as mentioned.



Note

Here, I’m switching my databases between PersonDatabase and EmployeeDatabase. Hence I have two columns named PersonID and EmployeeID. (Make sure your PersonID and EmployeeID are different, if not it will create a conflict between the databases.)

You should also need to make sure that the credentials Servername, DatabaseName, UserID and Password of the database to be inserted as and when an entry is made to either PersonTable or EmloyeeTable.

Let’s create tables in each database (PersonDatabase and EmployeeDatabase) with basic information as shown.

1. Person Table
   
   
   
   
2. Employee Table
   
   
   

So, now we are done with the database creation let’s just move on for some coding. Let’s create a new Empty MVC project from Visual Studio.



Now, create the .edmx files for the Database1, PersonDatabase, and EmployeeDatabase individually. Since we have created three different .edmx files, we will be having three different connection strings in web.config file.

Replace your datasource with {0}, initialcatalog with {1}, userid with {3}, and password with {4} in your PersonDatabaseEntities and EmployeeDatabaseEntities as shown. So that we can replace them by the one which we need to change or switch.

We now need to configure our connection string so as to get the values of datasource, initialcatalog, userid, and password for our connection strings. For that, we will write a class and fetch the properties from the table of Database1.