Set the application ConnectionString in the Service Configuration instead of web.config in Azure

Question

I have an application currently in Azure and whenever we push it into the Staging segment, we cannot truly test since the connection string is pointing to the prod database.

Someone mentioned to me that you should be able to set the connection string in the ServiceConfiguration.cscfg file instead (or with) the web.config file. That way you can change the connection string in the Azure portal instead of republishing a who app.

Does anyone know how to do this?

Unbar

Answer 1

In your ServiceConfiguration.cscfg file add:

<ServiceConfiguration ... />
  <Role ... />
    <ConfigurationSettings>
      <Setting name="DatabaseConnectionString" value="put your connection string here" />
    </ConfigurationSettings>
  </Role>
</ServiceConfiguration>

Now you have a connection string you can change by editing the configuration inside the azure portal.

Then anytime you need to retrieve the connection string you can do it using:

using Microsoft.WindowsAzure.ServiceRuntime;

...

String connString = RoleEnvironment.GetConfigurationSettingValue("DatabaseConnectionString")

You may need to add Microsoft.WindowsAzure.ServiceRuntime.dll to your references.

RoleEnviroment.IsAvailable can be used to test if your are running in Azure, and if not to fall back to your web.config settings.

using System.Configuration;
using Microsoft.WindowsAzure.ServiceRuntime;

...

if (RoleEnvironment.IsAvailable)
{
    return RoleEnvironment.GetConfigurationSettingValue("DatabaseConnectionString");
}
else
{
    return ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString; 
}

This article has a more verbose explanation of the above.

Applause

This doesn't allow the specification of a connection provider (required by Entity Framework), such as System.Data.EntityClient. Any thoughts? – Halette

@DaleAnderson, put them in web.config. Can't think of a find a better place that works with EF :( – Babcock

Yeh. I actually find web.config is best for that sort of stuff anyway - doesn't require a redeploy to change it in a testing scenario and can be managed easily using config transforms. – Halette

I added Microsoft.WindowsAzure.ServiceRuntime, but I get an exception Could not load file or assembly 'msshrtmi, Version=1.7.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35' or one of its dependencies. Apparently, Microsoft.WindowsAzure.ServiceRuntime depends on that file. Any ideas? – Wun

You cannot use RoleEnvironment.IsAvailable apparently in non-Azure environments. On MSDN it says that the issue may be fixed at some point in the future. – Virulent

There is a Microsoft nuget package for this named "Microsoft.WindowsAzure.ConfigurationManager" that solves this problem. Read more here: msdn.microsoft.com/en-us/library/… – Wolfy

Answer 2

For Entity Framework, you do not need to provide a providerName, it's already inside in the connectionstring. The reason why it does not work when it's in azure settings is, it contains &quot symbol which needs to be transalated back to " before creating a new EntityConnection. You can do it using HttpUtility.HtmlDecode in System.Web.

Unmuzzle

Answer 3

Basically you need to define these setting in Azure service configuration file. Check here. Once defined these can be changed from Azure portal.

Etheleneethelin