If you choose to use ADO.NET designer, you create DataSets by specifying the connection strings of the data sources. These connection strings are stored in application config settings. How do we change them dynamically? You could alter the connection string in InitConnection() in the <DataSet>.Designer.cs files. But this is not a good idea. This is because that then can be overwritten if the designer is updated. Also there could be to many such files to be modified.To do it properly, we need to replace the connection string programmatically. First we need to remove the old connection string and then add the new connection string to the connectionstrings.
Code: /// </summary>
/// <param name="csName">The name of the property / connection string.</param>
/// <param name="connectionString">The connectionstring value.</param>
public static void UpdateConnectionStrings(string csName, string connectionString)
{
// Get the configuration file
System.Configuration.Configuration config =
ConfigurationManager.OpenExeConfiguration(
ConfigurationUserLevel.None);
// Remove the existing connectionstring.
config.ConnectionStrings.ConnectionStrings.Remove(csName);
// Add the connectionstring
ConnectionStringsSection csSection = config.ConnectionStrings;
csSection.ConnectionStrings.Add(
new ConnectionStringSettings(csName,
connectionString, "Microsoft.SqlServerCe.Client.3.5"));
// Save the configuration file
config.Save(ConfigurationSaveMode.Full);
// Force a reload of the changed section.
ConfigurationManager.RefreshSection("connectionStrings");
}
Please note that RefreshSection() is critical. It refreshes the named section so the next time that it is retrieved it will be re-read from disk. Otherwise it will read from the memory retrieving the old connection string setting.