programmatically change mysql connection string using c#

Question

I want to change the app.setting connection string at the run time. I have some code to change it but i can't do this. there is no error appear during the run time. But there is no change during execution.

This is my partial code :

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="Punch_Uploader.Properties.Settings.testConnectionString"
            connectionString="server=localhost;User Id=root;password=test123;database=test"
            providerName="MySql.Data.MySqlClient" />
        <add name="Punch_Uploader.Properties.Settings.testConnectionString1"
            connectionString="server=172.23.2.52;User Id=root;password=test123;database=test"
            providerName="MySql.Data.MySqlClient" />
    </connectionStrings>
</configuration>

And:

Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
config.ConnectionStrings.ConnectionStrings.Add(
    new ConnectionStringSettings("Punch_Uploader.Properties.Settings.testConnectionString2", 
        String.Format(
            "server={0};Port={1}; database={2};User Id={3};password={4};providerName={5}", 
            "172.23.2.32", "3306", "test", "root", "test123", "MySql.Data.MySqlClient")
        )
);
config.Save(ConfigurationSaveMode.Modified, true);
ConfigurationManager.RefreshSection("connectionStrings"); 

MessageBox.Show(ConfigurationManager
    .ConnectionStrings["Punch_Uploader.Properties.Settings.testConnectionString2"]
    .ConnectionString);

The above code is not working for me....

Please help me to fix this.

You need to look at the [ConnectionStringBuilder](http://msdn.microsoft.com/en-us/library/ms254947.aspx) classes. — LeopardSkinPillBoxHat, Jun 08 '12 at 04:12

score 3 · Accepted Answer · answered Jun 08 '12 at 05:00

ConnectionStringSettings settings =
    ConfigurationManager.ConnectionStrings["Punch_Uploader.Properties.Settings.testConnectionString"];

string connectString = settings.ConnectionString;    

SqlConnectionStringBuilder builder =
        new SqlConnectionStringBuilder(connectString);

builder.DataSource = "172.23.2.52:3306";
builder.InitialCatalog = "test";
builder.UserID = "root";
builder.Password = "test123";

As mentioned in the comment to the previous answered I can confirm this method works as I've used it before to switch between different database environments.

score 0 · Answer 2 · edited May 23 '17 at 10:24

0

Connection string is read only. To change connection string you need to manually saves changes back to the config file which will cause the application to restart.

Edit: quick search and I found this (might be helpful): connection string at runtime

edited May 23 '17 at 10:24

Community

1
1

answered Jun 08 '12 at 04:46

astro boy

1,410
1
11
16

This is absolutely not true. No changes can be made to the actual config file at run time however using the ConnectionStringBuilder classes mentioned in comment on the original post you can easily modify the connection string in memory. I have used this method in several apps to switch between development and test databases by manipulating the connection string to change the data source. – Jesse Carter Jun 08 '12 at 04:50
depends on what he wants to do. I thought he wanted to save the changes to the config file? – astro boy Jun 08 '12 at 05:30
To my knowledge you cannot save changes to the config file. You can only manipulate its values in memory. – Jesse Carter Jun 08 '12 at 05:33

score 0 · Answer 3 · answered Jun 08 '12 at 04:59

Try this,

 Configuration webConfig = WebConfigurationManager.OpenWebConfiguration("~");
 ConnectionStringsSection section = webConfig.GetSection("connectionStrings") as            
                                              ConnectionStringsSection;
 if (section != null)
 {
   section.ConnectionStrings["NameTheCnStr"].ConnectionString = "New Connection string here";
   webConfig.Save();
  }

score 0 · Answer 4 · edited Sep 06 '12 at 13:02

trying this Code

 private void UpdateConnectionString(string ConfigPath)
 {
     XmlDocument xmlDocument = new XmlDocument();
     xmlDocument.Load(ConfigPath);
      XmlNode parentNode = xmlDocument.DocumentElement;
     if (parentNode.Name == "connectionStrings")
     {
        foreach (XmlNode childNode in parentNode.ChildNodes)
        {
           if (childNode.Name == "add" && childNode.Attributes["name"].Value=="Punch_Uploader.Properties.Settings.testConnectionString")
           {
             string sqlConnectionString =childNode.Attributes["connectionString"].Value;
             SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder(sqlConnectionString);
             sqlBuilder.InitialCatalog = "yourDatabaseName";
             sqlBuilder.IntegratedSecurity = true;
             sqlBuilder.Password = "";

             //Change any other attributes using the sqlBuilder object
             childNode.Attributes["connectionString"].Value = sqlBuilder.ConnectionString;
           }
        }
      }
      xmlDocument.Save(ConfigPath);
  }

configPath is path of connection string from Web.config

string configPath  = ConfigurationManager.ConnectionStrings["Punch_Uploader.Properties.Settings.testConnectionString"].ConnectionString;

now am using the below code. Its working fine when i am changing the IP. Properties.Settings.Default["testConnectionString"] = String.Format("server={0};Port={1}; database={2};User Id={3};password={4}", "172.23.2.32", "3306", "sunhrm", "root", "test123");. Now am change the database name means its not working. How can i fix this error — Amulraj, Jun 08 '12 at 10:00

programmatically change mysql connection string using c#

4 Answers4