Editing Connection String in ASP web.config include using ampersand

Question

I have a configuration manager for setting up a web application before use. Included in this configuration manager is the setup for the connection string to the database. I have working code that will modify the connection string and save, but I have run into an issue that i cannot seem to correct.

The connection string contains an '&' character so when the string is saved to the web.config file, the string changes from "&" to "&". I have tried a few different code variations to get past this, but nothing is working.

Here is the code to save the connection string:

System.Configuration.Configuration config = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~/");
ConnectionStringsSection section = config.GetSection("connectionStrings") as ConnectionStringsSection;

section.ConnectionStrings["DALEntities"].ConnectionString = string.Format("metadata=res://*/DALModel.csdl|res://*/DALModel.ssdl|res://*/DALModel.msl;provider=System.Data.SqlClient;provider connection string="data source={0};initial catalog={3};persist security info=True;user id={1};password={2};multipleactiveresultsets=True;application name=EntityFramework"", settings.SQLserverName, settings.SQLuserName, settings.SQLpassword, settings.SQLdatabaseName);

config.Save();

This works to save the connection string, but this is what is saved to the web.config file:

<add name="DALEntities" connectionString="metadata=res://*/DALModel.csdl|res://*/DALModel.ssdl|res://*/DALModel.msl;provider=System.Data.SqlClient;provider connection string=&amp;quot;data source=.\SQL2012;initial catalog=tooling_041718;persist security info=True;user id=mm;password=mm;multipleactiveresultsets=True;application name=EntityFramework&amp;quot;" providerName="System.Data.EntityClient" />

As you can see, the & was added to the connection string in two different spots.

I have tried escaping the '&' character by using " + Regex.Escape("&") + " instead of "&" in those two different spots in the string and I have tried taking out the string.Format() and have just a simple string.

Does anyone know if there is a way to escape the altering of the '&' when saving to the web.config?

Answer 1

You can use System.Web.HttpUtility.HtmlDecode before saving it, because when it saves, it encodes it using HTML encoding.

If you do this, it will convert the " to ", and when saving, it will convert the " back to ".

System.Configuration.Configuration config = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration("~/");
ConnectionStringsSection section = config.GetSection("connectionStrings") as ConnectionStringsSection;

section.ConnectionStrings["DALEntities"].ConnectionString = System.Web.HttpUtility.HtmlDecode(string.Format("metadata=res://*/DALModel.csdl|res://*/DALModel.ssdl|res://*/DALModel.msl;provider=System.Data.SqlClient;provider connection string="data source={0};initial catalog={3};persist security info=True;user id={1};password={2};multipleactiveresultsets=True;application name=EntityFramework"", settings.SQLserverName, settings.SQLuserName, settings.SQLpassword, settings.SQLdatabaseName));

config.Save();

Or you could write the " (with escape: \") instead of " directly in your connection string:

section.ConnectionStrings["DALEntities"].ConnectionString = string.Format("metadata=res://*/DALModel.csdl|res://*/DALModel.ssdl|res://*/DALModel.msl;provider=System.Data.SqlClient;provider connection string=\"data source={0};initial catalog={3};persist security info=True;user id={1};password={2};multipleactiveresultsets=True;application name=EntityFramework\"", settings.SQLserverName, settings.SQLuserName, settings.SQLpassword, settings.SQLdatabaseName);