Dynamic Connection EF C# Windows Forms Mysql

Question

I have the following problem:

EF creates a connectionString in the app.config and uses this connection always, but if you need to change the database server I will have to change the app.config.

I have tried a way to make the server ip exchange work, but when I run it it takes the & of the &quote symbol and is as follows: &quote; ai when I start the application automatically gives error because it does not recognize the proper information. Follow the code of the app.config and code I made via C# to change this property.

App.config

<connectionStrings>   
  <add name="ref_bdEntities" connectionString="metadata=res://*/ModeloDados.csdl|res://*/ModeloDados.ssdl|res://*/ModeloDados.msl;provider=MySql.Data.MySqlClient;provider connection string=&quot;server=127.0.0.1;user id=sa;password=master;persistsecurityinfo=True;database=ref_bd&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

Change code

string _ipservidor = string.Empty;
            _ipservidor = LerIni.LerINI(@"C:\Motion Software\bin\config.ini", "[IPServidor]");
            connectionString = "metadata=res://*/ModeloDados.csdl|res://*/ModeloDados.ssdl|res://*/ModeloDados.msl;provider=MySql.Data.MySqlClient;provider connection string="server="+_ipservidor+";user id=sa;password=master;persistsecurityinfo=True;database=ref_bd"";
            Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            config.ConnectionStrings.ConnectionStrings["ref_bdEntities"].ConnectionString = connectionString;
            config.Save(ConfigurationSaveMode.Modified); // Salva o que foi modificado
            ConfigurationManager.RefreshSection("connectionStrings"); // Atualiza no app o bloco connectionStrings
            Properties.Settings.Default.Reload(); // Recarrega os dados de conexão

Help me with this. I am using Mysql as a database and will probably use a hosting for the database (ignore the fact that it is Windows Forms).

Update - I’m using Database First

Update 2 - The above code does not change the value of the physical config file, when I put the necessary files to work the application and take the settings file Menu.exe.config (In the case the exe name is Menu) it does not alter that file and I want to be able to change that file, it is possible?

Update 3 - No website http://www.codeproject.com/Tips/411013/Configuring-a-Connection-String-in-the-App-Config I found an example that uses the xml change in the file . config, from what I could also perceive he uses a Stringbuilder, the only part of the code I didn’t understand was: xElement.FirstChild.Attributes[2].Value = con;, can anyone help me understand? I’m almost to a solution just need some help.

Answer 1

After much searching and analysis I was able to solve the problem. As described in Update 3 I found a method to change the xml file of the app.config and I put down the code with the solution of the problem:

public void Servidor()
        {
            string _ipservidor = string.Empty;
            _ipservidor = LerIni.LerINI(@"C:\Motion Software\bin\config.ini", "[IPServidor]");
            connectionString = "metadata=res://*/ModeloDados.csdl|res://*/ModeloDados.ssdl|res://*/ModeloDados.msl;provider=MySql.Data.MySqlClient;provider connection string='server="+_ipservidor+";user id=sa;password=master;persistsecurityinfo=True;database=ref_bd'";
            updateConfigFile(connectionString);

            /* Altera app.config */
            Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            config.ConnectionStrings.ConnectionStrings["ref_bdEntities"].ConnectionString = connectionString;
            config.Save(ConfigurationSaveMode.Modified); // Salva o que foi modificado
            ConfigurationManager.RefreshSection("connectionStrings"); // Atualiza no app o bloco connectionStrings
            Properties.Settings.Default.Reload(); // Recarrega os dados de conexão   

            /* Altera connection Entity Framework */
            EntityConnectionStringBuilder conEntity = new EntityConnectionStringBuilder();
            conEntity.Provider = "MySql.Data.MySqlClient";
            conEntity.ProviderConnectionString = "'server=" + _ipservidor + ";user id=sa;password=master;persistsecurityinfo=True;database=ref_bd'";
            conEntity.Metadata = "res://*/Modelo.csdl|res://*/Modelo.ssdl|res://*/Modelo.msl";
            ref_bdEntities BDEntidades = new ref_bdEntities(conEntity.ToString());
        }

        public void updateConfigFile(string con)
        {
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
            foreach (XmlElement xElement in xmlDoc.DocumentElement)
            {
                if (xElement.Name == "connectionStrings")
                {
                    xElement.FirstChild.Attributes[1].Value = con;
                }

            }
            xmlDoc.Save(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
        }

As you can see, I also did an update by the Configuration and Entityconnection. This was just to make sure it would work, in case anyone thinks they’re going to have a performance slump please let me know.

But what really ended my problem was changing the value of connectionString in the xml of the app.config, I tested it in a separate folder with the Menu.exe.config and it worked.

The only problem I recorded is that as I was testing with the local machine IP 127.0.0.1, by changing only the last number to any other one it continued to connect but I think it occurs because of port 3306, but by placing a different ip type 192.168.12.145 it does not find the same port then of connection error.

Any suggestions you have and want to share with me, I appreciate!

Thank you for your attention.

Answer 2

Basically, when initializing the application I make a small query in the version table of my system, if this query attempt gives error, for sure is connectivity problem, so I call a dialog so that the address is set and write :-)

        public static bool Configura(string strConecta, out string newStrConecta)
    {
        var builder = new SqlConnectionStringBuilder
        {
            ConnectionString = strConecta
        };
        newStrConecta = "";
        using (var bancoForm = new BancoForm())
        {
            bancoForm.tbServidor.Text = builder.DataSource;
            bancoForm.tbUsuario.Text = builder.UserID;
            bancoForm.tbPassword.Text = builder.Password;
            bancoForm.tbDatabase.Text = builder.InitialCatalog;
            var result = bancoForm.ShowDialog();
            if (result != DialogResult.OK)
            {
                Debug.WriteLine("\n\nFALHOU : " + strConecta + "\n\t");
                return false;
            }
            else
            {
                builder.DataSource = bancoForm.tbServidor.Text;
                builder.UserID = bancoForm.tbUsuario.Text;
                builder.Password = bancoForm.tbPassword.Text;
                builder.InitialCatalog = bancoForm.tbDatabase.Text;
                newStrConecta = builder.ConnectionString;
                Debug.WriteLine("\n\tSUCESSO !!! \n\t");
                Debug.WriteLine("Sucesso", newStrConecta);

                return true;
            }
        }
    }

Works great for me.