Change connection string from development to production when publishing

Question

I want to know how to change automatically the connection string of my app so when I'm working on it in my pc, it uses my local SQL Server and once I publish it then uses the SQL Server I have hosted on azure.

Right now I'm just commenting out the connection string depending of what I want, I saw that the Web.Config file has 2 dependencies, Web.Debug.config and Web.Release.config which is where I guess I have to do something but I don't know what.

This is my web.config file so far

<connectionStrings>
    <add name="MyApp" connectionString="Data Source=mydb.database.windows.net;Initial Catalog=MyDb;User [email protected];Password=MyPwd;Encrypt=true;Trusted_Connection=false;" providerName="System.Data.SqlClient" />
  <!--<add name="MyApp" providerName="System.Data.SqlClient" connectionString="Server=.\SQLEXPRESS;Database=MyDb;Integrated Security=True;" />-->
  </connectionStrings

As you can see I have 2 connection strings that I have comment out but doing this is really annoying.

Grimaldi

Answer 1

The two configs are built for their respective build setting, so you can put your dev connection string in Web.Debug.config and your prod connection string in Web.Release.config. Then, when you deploy to production, run a Release build and your resulting Web.Config will have the production connection string.

Supererogation

But do I need to have the connections string also specified in the web.config file or just in the Web.Debug.config and Web.Release.config? – Grimaldi

@JGEstevez each config have it's own connection string. You have to choose appropriate config file at deployment. – Soria

Answer 2

you can split your web.config like

• web.dev.config
• web.live.config

At deployment time choose appropriate config file. You can visit this link to learn how to manage multiple web.config file in single project.

OR

If you don't want to create multiple web.config files refer Single web.config file across all environments (dev, test, prod) from codeproject

Soria

The use of the multiple config files is the way to go. While not a requirement of the OP, if someone uses the codeproject approach for multiple customer installations, i.e. same code base for multiple deployments, this would not pass a security audit, as confidential configuration information for other installations can be observed in the deployed code. – Inquisitorial

@Inquisitorial appriciated – Soria

Answer 3

You can now use Azure to handle this exact problem through their connection strings within app settings. You just need to name the connections exactly the same and then it will plug and play.

https://learn.microsoft.com/en-us/azure/app-service-web/web-sites-configure

Dinahdinan

Answer 4

I decided to bypass the whole web.config and instead integrate a function that determines the connection string based off of the current machine it is running from. To set this up I had to set up my ApplicationDbContext() to gather the connection string from the function like so:

public ApplicationDbContext() : base(CFFunctions.GetCFConnection())
{
}

Note that my function is Static:

    public static string GetCFConnection()
    {
        string Connection = "";

        string Machine = System.Environment.MachineName.ToLower();

        switch(Machine)
        {
            case "development":
                Connection = @"Data Source=DEV_SRV;Initial Catalog=CeaseFire;Integrated Security=True"; 
                break;
            case "production":
                Connection = @"Data Source=PRO_SRV;Initial Catalog=CeaseFire;Integrated Security=True";
                break;
        }

        return Connection;
    }

Kilby