Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 666
George Mauer Avatar asked Aug 17 '15 20:08

George Mauer


People also ask

How do I change the connection string after deployment?

If you save your connection string in the udl file, the user can change the connection via an interface by simply double clicking that file. You can set your connection string in the app to point to the udl file. You can also launch the udl interface programmatically if you want.

Where do I put connection string?

Connection strings can be stored as key/value pairs in the connectionStrings section of the configuration element of an application configuration file. Child elements include add, clear, and remove.

How do I change my connection string?

To edit a connection string stored in application settingsLocate the connection you want to edit and select the text in the Value field. Edit the connection string in the Value field, or click the ellipsis (...) button in the Value field to edit your connection with the Connection Properties dialog box.

How does connection string work?

In computing, a connection string is a string that specifies information about a data source and the means of connecting to it. It is passed in code to an underlying driver or provider in order to initiate the connection.


2 Answers

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
like image 199
John-Philip Avatar answered Sep 21 '22 11:09

John-Philip


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

like image 20
Totem Avatar answered Sep 21 '22 11:09

Totem