Connecting to multiple databases in MVC web application

Question

Hey Guys,

How to connect to different databases for a .NET MVC web application written in C#?

Here the stucture of the database table remains the same. All that changes is the database name. So how to I manually connect to a database or use modify the connection string?

The trick is to use the same code. Depending on the URL I want the data to be displayed from different database. So the Views and Controller (pretty much) has single code w.r.t. the Model.

-Datte

Answer 1

Here's a very simple way to do what I think you're asking. In your web.config file, you could define your two connection strings:

<connectionStrings>
    <add name="DevelopmentDB" providerName="System.Data.SqlClient"
        connectionString="Data Source=sql-dev.example.com;Initial Catalog=MyDB;User Id=MyUser;Password=MyPassword" />
    <add name="ProductionDB" providerName="System.Data.SqlClient"
        connectionString="Data Source=sql-prod.example.com;Initial Catalog=MyDB;User Id=MyUser;Password=MyPassword" />
</connectionStrings>

Then, in your (base) controller, you could create a method that returns the appropriate connection string based upon the request, such as:

internal string ConnectionString
{
    get
    {
        return getConnectionStringByServerName(this.HttpContext.Request.ServerVariables["SERVER_NAME"]);
    }
}

internal string getConnectionStringByServerName(string serverName)
{
    if (serverName.Equals("localhost"))
    {
        return WebConfigurationManager.ConnectionStrings["DevelopmentDB"].ConnectionString;
    }
    else
    {
        return WebConfigurationManager.ConnectionStrings["ProductionDB"].ConnectionString;
    }
}

You could of course change the selection criteria for whatever makes the most sense.

Good luck!