Proper way to set connection strings in ci during msbuild/deploy

Question

I have a CI server (Bamboo, but I don't think that it matters) building and auto-deploying my application. During local development I use localdb (<connectionStrings> node in my web.config)

    <add name="MyApp" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDB)\v11.0;Integrated Security=True;Connect Timeout=30;Initial Catalog=MyApp" />
    <add name="MyApp_Patients" providerName="System.Data.SqlClient" connectionString="Data Source=(LocalDB)\v11.0;Integrated Security=True;Connect Timeout=30;Initial Catalog=AppleHms_MyApp" />
    ...

When deployed obviously it shouldn't use this. It should use my deployment sql server connection string.

I know that I can write a web.config transform for this and I even know that I can encrypt the web.config but I'm not sure how the db connection-string is supposed to get in there properly. What makes sense to me is for the CI server to overwrite each connectionString - so my config transform should maybe look something like this

<?xml version="1.0" encoding="utf-8"?>
<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
  <connectionStrings>
    <add name="MyApp" providerName="System.Data.SqlClient" connectionString="${main-db-connectionstring}"  xdt:Transform="SetAttributes" xdt:Locator="Match(name)" />
    <add name="MyApp_Patients" providerName="System.Data.SqlClient" connectionString="${patients-db-connectionstring}"  xdt:Transform="SetAttributes" xdt:Locator="Match(name)" />
    ...
  </connectionStrings>
  <system.web>
    <compilation xdt:Transform="RemoveAttributes(debug)" />
  </system.web>
</configuration>

With the overrides coming from variables in my ci server (presumably some part of msbuild or deploy)?

Is that correct that this is the "right" way of doing this? What exactly do I have to do with msbuild/deploy to make this happen?

Answer 1

We are also using bamboo for CI and deployment, and what we are doing is:

• Packages project containing a default Parameters.xml as the msdeploy parameter declaration file.
• Maintaining SetParameters.xml files for our various environement.
• Packaging projects on nightly build.
• Releasing nightly packages as bamboo artifacts.
• Using msdeploy to apply parametrization over these packages when deploying for a particular nightly build.

Basically the deployment as it concern msdeploy is like as following :

msdeploy
  -verb:sync  
  -source:package="NightlyPackage.zip"  
  -dest:iisApp="YourIISHost/YourIISSite"  
  -declareParamFile="YourEnvironementSetParameters.xml"

Your Parameters.xml per project package would look like as following:

<parameters>
    <parameter
       name="ConnectionString1-Web.config Connection String"  
       description=""  
       defaultValue="localhost">
          <parameterEntry  
             kind="XmlFile"  
             scope="\\web.config$"  
             match="/configuration/connectionStrings/add[@name='ConnectionString1']/@connectionString"  
           />
    </parameter>
 </parameters>

Where ConnectionString1 is the name of a connectionString.

And your YourEnvironementSetParameters.xml would look like as following:

<parameters>
  <setParameter name="ConnectionString1-Web.config Connection String" value="Your parametrized connection string value " />
</parameters> 

There are conventions when using parametrization on some fields with webdeploy. Connection strings are concerned, so it's good practice to respect the following parameter naming when it comes to refer to web.config connection strings :

%NameOfYourConnectionStringAsInWebConfig%-Web.config Connection String

Answer 2

Using the XMLPoke task would be the proper way to do so via MSBuild script

XMLPoke on MSDN

Also to get a really good reference, check Sayed Ibrahim Hashimi blog, always helpful