Database Connection Strings with Azure Worker Roles

I love web.config transforms in ASP.NET.

Using the XML Transformations engine built into Visual Studio, they allow me to easily change settings between environments whenever I publish the web.config file of my Azure Web Role. A plugin called SlowCheetah allows me to perform the same transformations on app.config files as well.

In my Azure Worker Role, I was using SlowCheetah to handle configuration changes in my app.config file. However, it wasn't publishing correctly when I deployed the Release configuration to Azure. The behavior I observed indicated that the configuration transformation file (app.release.config) wasn't being applied whenever I published. This was disappointing, since I find SlowCheetah so useful!

Here's how I was able to use custom configuration settings in the Cloud Service itself to change settings between environments.

I started with a app.release.config file containing the Entity Framework connection string for the production environment:

<add name="EFConn" connectionString="metadata=res://*/Model.csdl|res://*/Model.ssdl|res://*/Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=SERVERNAME;initial catalog=DBNAME;persist security info=True;user id=MYUSERNAME;password=MYPASSWORD;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" xdt:Transform="SetAttributes" xdt:Locator="Match(name)"/>

This is how you'd normally set it up, but that won't work here. So I moved this setting from the app.release.config file to the regular app.config file and gave it a different name to distinguish it.

<add name="TestEFConn" connectionString="[...]" providerName="System.Data.EntityClient" />
<add name="ProdEFConn" connectionString="[...]" providerName="System.Data.EntityClient" />

Note: Be sure to remove the XDT attributes from your settings.

Next, I want to automatically reference the Test setting whenever my appliation is running in the test Cloud Service, and the Prod setting when it is run in the production Cloud Service.

To do this, I added a partial class (since my DbContext was set up as a Database First model) to allow me to pass in a connection string name. This takes advantage of how Entity Framework looks for what connection string to use.

public partial class EFConn : DbContext
    public EFConn(string connectionStringName)
        : base("name=" + connectionStringName)
    { }

The final step is to add the actual configuration values to the ServiceConfiguration.Cloud.cscfg and ServiceDefinition.csdef files. You can use different ServiceConfiguration files in different publish settings, so use your Test and Prod values appropriately.


<WorkerRole name="YOURWORKERROLENAME" vmsize="Small">
    <Setting name="EFConnStringName" />


  <Instances count="1" />
    <Setting name="EFConnStringName" value="TestEFConn" />

And of course, be sure to use this setting in your code.

using (var context = new EFConn(RoleEnvironment.GetConfigurationSettingValue("EFConnStringName")))

Good luck!