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.

<connectionStrings>
    <addname="Database1Entities" connectionString="metadata=res://*/Models.TNEModel.csdl
|res://*/Models.TNEModel.ssdl|res://*/Models.TNEModel.msl; provider=System.Data.SqlClient;provider connection string="data source='Your Data Source';initial catalog=Database1; ;user id='Your user ID';password='Your Password';integrated security=True; MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
    <add name="PersonDatabaseEntities" connectionString="metadata=res://*/Models.PersonModel.csdl|res://*/Models.PersonModel.ssdl|
res://*/Models.PersonModel.msl;provider=System.Data.SqlClient;provider connection string="data source='Your Data Source';initial catalog='Your Initial Catalog';
user id='Your user ID';password='Your Password';MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
    <add name="EmployeeDatabaseEntities" connectionString="metadata=res://*/Models.EmployeeModel.csdl|res://*/Models.EmployeeModel.ssdl|
res://*/Models.EmployeeModel.msl;provider=System.Data.SqlClient;provider connection string="data source='Your Data Source';initial catalog='Your Initial Catalog';
user id='Your user ID';password='Your Password';MultipleActiveResultSets=True;App=EntityFramework"" providerName="System.Data.EntityClient" />
</connectionStrings>

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.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Web.Configuration;
namespace ConnectionString {
    public class Connection {
        public string ConfigurnewConnectionString(string server, string database, string userid, string password, string ConnectionString) {
            System.Configuration.Configuration Config1 = WebConfigurationManager.OpenWebConfiguration("~");
            ConnectionStringsSection conSetting = (ConnectionStringsSection) Config1.GetSection("connectionStrings");
            conSetting.ConnectionStrings[ConnectionString].ConnectionString = conSetting.ConnectionStrings[ConnectionString].ConnectionString.ToString().Replace("{0}", server);
            conSetting.ConnectionStrings[ConnectionString].ConnectionString = conSetting.ConnectionStrings[ConnectionString].ConnectionString.ToString().Replace("{1}", database);
            conSetting.ConnectionStrings[ConnectionString].ConnectionString = conSetting.ConnectionStrings[ConnectionString].ConnectionString.ToString().Replace("{2}", userid);
            conSetting.ConnectionStrings[ConnectionString].ConnectionString = conSetting.ConnectionStrings[ConnectionString].ConnectionString.ToString().Replace("{3}", password);
            return conSetting.ConnectionStrings[ConnectionString].ConnectionString;
        }
    }
}

Note: We need to provide the credentials of datasource, initialcatalog, userid, password and name of the connection string to the above class which will replace the {0}, {1}, {2}, {3} of the connection string in web.config file. Refer Controller code for details.

Since we are overwriting the default context.cs class of .edmx file, we are supposed to create a partial class which will overwrite the same as shown.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
namespace ConnectionString.Models {
    public partial class PersonDatabaseEntities: DbContext {
        public PersonDatabaseEntities(string Con): base("name=PersonDatabaseEntities") {}
    }
}

Let us now create a Controller with the action method which will access the data from the two different databases.

public ActionResult Index() {
    try {
        Database1Entities objDatabase1Entities = new Database1Entities();
        var obj = objDatabase1Entities.SELECTCONNECTION().FirstOrDefault();
        Connection objConnection = new Connection();
        if (obj.DatabaseName == "PersonDatabase") {
            ConStr = objConnection.ConfigurnewConnectionString(obj.Servername, obj.DatabaseName, obj.UserId, obj.Password, "PersonDatabaseEntities").ToString();
        } else if (obj.DatabaseName == "EmployeeDatabase") {
            ConStr = objConnection.ConfigurnewConnectionString(obj.Servername, obj.DatabaseName, obj.UserId, obj.Password, "EmployeeDatabaseEntities").ToString();
        }
        // Your Condition to display the data from the desired database
        return View(obj);
    } catch (Exception e) {
        return View("Error");
    }
}

Once you are done with the conditions, you will get the output something like this.


The above data is from Person database.

The above data is from Employee database.

Conclusion

Databases can be switched or changed dynamically while running an ASP.NET MVC application. And, we are done.