How do I set a connection string config programmatically in .net?

I was looking for the answer to the same qustion about allowing the user to amend the connection string in a click once application by selecting a local SQL Server.

The code below displays a user form which contacts all the locally available SQL Servers and allows them to select one. It then constructs a connection string for that sever and returns it from a variable on the form. The code then amends the config files AND SAVES IT.

string NewConnection = "";
// get the user to supply connection details
frmSetSQLConnection frm = new frmSetSQLConnection();
frm.ShowDialog();
if (frm.DialogResult == DialogResult.OK)
{
    // here we set the users connection string for the database
    // Get the application configuration file.
    System.Configuration.Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
    // Get the connection strings section.
    ConnectionStringsSection csSection = config.ConnectionStrings;
    foreach (ConnectionStringSettings connection3 in csSection.ConnectionStrings)
    {
        // Here we check for the preset string - this could be done by item no as well
        if (connection3.ConnectionString == "Data Source=SQL204\\SQL2008;Initial Catalog=Transition;Integrated Security=True")
        {
             // amend the details and save
             connection3.ConnectionString = frm.Connection;
             NewConnection = frm.Connection;
             break;
        }
    }
    config.Save(ConfigurationSaveMode.Modified);
    // reload the config file so the new values are available

    ConfigurationManager.RefreshSection(csSection.SectionInformation.Name);

    return clsDBMaintenance.UpdateDatabase(NewConnection))
}

Another way to approach this would be to operate on the collection directly:

var settings = ConfigurationManager.ConnectionStrings;
var element = typeof(ConfigurationElement).GetField("_bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic);
var collection = typeof(ConfigurationElementCollection).GetField("bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic);

element.SetValue(settings, false);
collection.SetValue(settings, false);

settings.Add(new ConnectionStringSettings("ConnectionStringName", connectionString));

// Repeat above line as necessary

collection.SetValue(settings, true);
element.SetValue(settings, true);

I've written about this in a post on my blog. The trick is to use reflection to poke values in as a way to get access to the non-public fields (and methods).

eg.

var settings = ConfigurationManager.ConnectionStrings[ 0 ];

var fi = typeof( ConfigurationElement ).GetField( "_bReadOnly", BindingFlags.Instance | BindingFlags.NonPublic );

fi.SetValue(settings, false);

settings.ConnectionString = "Data Source=Something";