Cloud Tip #1–How to set a connection string in Web.config programmatically at runtime in Windows Azure

  • Article

The scenario is I’m migrating an application to the cloud. I’ve got a database connection defined in my web.config file which uses an on-premise SQL Server database and what I’d like to do is to move it to a Web Role on Windows Azure and use a SQL Azure database. The basic process is to add a Windows Azure Deployment Project to the solution that contains my web application. Next I move my database to SQL Azure (using the SQL Azure Migration Wizard), and then I change the connection string to point to the cloud database. Except that after I’ve deployed the project I may need to change where the database lives.

Try Azure for free - Activate a 90 day trial at https://aka.ms/AzureTrialMB today!

Since that information is stored in Web.config all I need to do is redeploy a new web.config file to the Web Role and we’re good to go. Unfortunately that probably means an upgrade to the application or a VIP swap, which isn’t all bad, but I know that I can make changes to the ServiceConfiguration file and propagate the changes to the running instances…no down time. But how do I get a change in the Service Configuration into the web.config?

I have posted before how you can encrypt information stored in the web.config file during a Session_Start event by adding code to the Global.asax file to examine a section and then call protect. If I can add some code to determine whether I’m running in Azure, and if I am to read the setting from the ServiceConfiguration file then we should be good.  Like the example of encrypting settings an approach that works well is to add code to the Session_Start event. For my example I’ve created a setting in the Windows Azure Role for dbConnectionString and set it to the value I’d like to use.

image

Next I make sure I add a reference to the Microsoft.WindowsAzure.ServiceRuntime namespace added to the web project so I can access information about the role. If I don’t have one already I add a global.asax file to my web project. This would be where I can add code for the events that fire periodically throughout the lifecycle of my app. I choose to use the Session_Start because if I’ve made changes to my ServiceDefinition file they will get applied the next time someone browses to my site. 

         public string dbConnectionString { get; set; }

  

         void Session_Start(object sender, EventArgs e)

         {

             // Are we running in Azure?

             if (RoleEnvironment.IsAvailable == true)

             {

  

                 dbConnectionString = RoleEnvironment.GetConfigurationSettingValue("dbConnectionString");

  

                 // Do we have a value for the alternate dbConnectionString in the ServiceConfiguraiton file?

                 if (dbConnectionString != null)

                 {

                     Configuration myConfig = WebConfigurationManager.OpenWebConfiguration("~");

                     ConnectionStringsSection mySection = myConfig.GetSection("connectionStrings") as ConnectionStringsSection;

                     if (mySection != null)

                     {

                         if (mySection.ConnectionStrings["myDBConnectionString"].ConnectionString != dbConnectionString)

                         {

                             mySection.ConnectionStrings["myDBConnectionString"].ConnectionString = dbConnectionString;

                             myConfig.Save(ConfigurationSaveMode.Modified, true);

                         }

                     }

                 }

             }

         }

I check to see whether the value in the Service Configuration file is different than the value in my web.config, and if it is then make the change and save it. Initially when I tested the code I got a “the configuration is read only” error, but by adding the option for the Save method it works. Now I am able to update the database connection string from the Service Configuration File and have it propagate to my web.config of the running application.

Credit goes where credit is due, and Bing pointed me to a few posts on the subject, including StackOverflow and DotNetCurry.

Enjoy!

Digg This