CODEWITHSUNDEEP
c#asp.netapp-config
Amulraj
Amulraj

Reputation: 269

programmatically change mysql connection string using c#

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.

Upvotes: 1

Views: 5431

Answers (4)

dhiraj
dhiraj

Reputation: 7

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;

Upvotes: 0

Jesse Carter
Jesse Carter

Reputation: 21147

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.

Upvotes: 3

KV Prajapati
KV Prajapati

Reputation: 94645

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();
  }

Upvotes: 0

astro boy
astro boy

Reputation: 1430

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

Upvotes: 0

Related Questions