Dynamic Connection EF C # Windows Forms Mysql

2

I have the following problem:

EF creates a connectionString in the app.config of the application and always uses that connection, but in case you need to change the server of the bank I will have to change the app.config.

I have tried a way to make the server ip exchange work, but when I do the same it takes & of the & quote symbol and looks like this: & quote; then when I start the application it automatically gives error because it does not recognize the information. Here is the code for the app.config and code I made via C # to change that 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=&quot;server="+_ipservidor+";user id=sa;password=master;persistsecurityinfo=True;database=ref_bd&quot;";
            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 pfv. 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 run the application and take the settings file Menu.exe.config (In case the exe name is Menu) it does not change this file and I want to be able to change this file, is it possible?

Update 3 - On the site link found an example that uses the xml change in the .config file, so I could also realize it uses a StringBuilder, the only part of the code I did not understand was: xElement.FirstChild.Attributes[2].Value = con; , can someone help me understand? I'm almost coming to a solution I just need some help.

    
asked by anonymous 11.03.2016 / 23:27

2 answers

0

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 app.config and I put below 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 updated the configuration and EntityConnection. This was just to make sure it would work, if anyone thinks they will have a performance drop please let me know.

But what really ended with my problem was to change the value of the 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 noticed is that as I was testing with the local machine ip 127.0.0.1, changing only the last number to any other it continued to connect but I think this happens because of port 3306, but when placing a different ip type 192.168.12.145 it does not find the same port then the connection error.

Any suggestions you have and would like to share with me, thank you!

Thanks for your attention.

    
12.03.2016 / 02:37
0

Basically, when I initialize the application I make a small query in the version table of my system, if this query attempt is error, it is certainly connectivity problem, so I call a dialog so that the address is set and record: -)

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

It works very well for me.

    
12.03.2016 / 17:23