Using MySQL with visual studio and changing the connection at runtime

Question

I use something like this for my application

MySqlConnection cnn = new MySqlConnection("Server=myServerAddress;" + 
                                          "Database=myDataBase;" + 
                                          "Uid=myUsername;" + 
                                          "Pwd=myPassword;");

And this changes everytime because we deploy databases with our application. It works fine. I type in using(new connection(cnn)){ query... } and go. And I've got it working with a dataset using a connection defined in the windows ODBC datasouce administrator. But I'm curious, is there a way to use visual studio's dataset items using the my local test db and then change the connection of the dataset at runtime? Even better, can I use c# to programmatically add the ODBC data source at runtime?

Answer 1

Your connection string will be stored in your App.config (or c# equivalent). Say it's called MyConnectionString. Just add My.Settings("MyConnectionString")="[your new connection string]" to your entry point to change to database binding at runtime. E.g:

Public Sub New()

    ' This call is required by the designer.
    InitializeComponent()

    My.Settings("MyConnectionString") = "server=remotedb.uk;user id=MainUser;password=2jdi38edhnche73g;database=mainDb;persistsecurityinfo=True;allowuservariables=True;defaultcommandtimeout=480;characterset=utf8mb4"

End Sub

Answer 2

Application connection string cannot be changed at runtime. User settings can be changed.

Assuming you are using an application setting-property named "MyConnectionString" which holds the connection string for the entire application. On your main Program class create a global string:

internal static string Prconnstring;

Create and save this settings.cs file:

namespace MYSOLUTIONORPROJECTNAME.Properties
{
    // (Not sure where I found this solution some time ago)
    // This class allows you to handle specific events on the settings class:
    //  The SettingChanging event is raised before a setting's value is changed.
    //  The PropertyChanged event is raised after a setting's value is changed.
    //  The SettingsLoaded event is raised after the setting values are loaded.
    //  The SettingsSaving event is raised before the setting values are saved.
    internal sealed partial class Settings
    {

        public Settings()
        {
            // // To add event handlers for saving and changing settings, uncomment the lines below:
            //
            // this.SettingChanging += this.SettingChangingEventHandler;
            //
            // this.SettingsSaving += this.SettingsSavingEventHandler;
            //
        }

        private void SettingChangingEventHandler(object sender, System.Configuration.SettingChangingEventArgs e)
        {
            // Add code to handle the SettingChangingEvent event here.
        }

        private void SettingsSavingEventHandler(object sender, System.ComponentModel.CancelEventArgs e)
        {
            // Add code to handle the SettingsSaving event here.
        }

        public override object this[string propertyName]
        {
            get
            {
                if (propertyName == "MyConnectionString")
                {
                    return Program.Prconnstring;
                }
                else
                {
                    return base[propertyName];
                }
            }
            set
            {
                base[propertyName] = value;
            }
        }
    }
}

Before calling-opening any object that uses the connection string (examples include Forms that use datasets or other classes that use datasets created on the development enviroment) create your new connection string by any means you think. (Example: You might want to use as user name in the connection string the current user. Create the connection string using the info provided form the environment.)

Program.Prconnstring = thenewruntimeconnectionstring.

Now whenever the application tries to get MyConnectionString (which is hardcoded in the myapplicationname.config and cannot be changed) instead gets the new thenewruntimeconnectionstring you provided to Program.Prconnstring.

Be aware that the development connection string will be available-visible to final user, since it is just a text file. If you do not want this, you can change that file (will be a file named NAMEOFMYAPPLICATION.exe.config) during deployment, since the connection string hardcoded there, will be of no use for the running app. Do not delete it, just change.

Answer 3

Usually a connection string is loaded from the application exe.config file present in the same folder of the application. This connection string could be defined using the Settings tab in the project properties.

• Right click on Properties of your project
• Select the Settings tab (confirm the creation if you have no settings)
• Click on the ComboBox in the column type and select Connection String
• Give a symbolic name to your connection
• Type the connection string in the Value column (Examples at connectionstrings.com)

Now in your project files you should have the file app.config (that becomes yourapp.exe.config) where there is a section like this

<configuration>
  <connectionStrings>
    <add name="MyAppConnection"
      connectionString="Server=myServerAddress;Database=myDB;Uid=user;Pwd=pass;" />
  </connectionStrings>
</configuration 

At this point you read it in the program using

  string conString = ConfigurationManager
                        .ConnectionStrings["MyAppConnection"]
                        .ConnectionString;

Instead in a dynamic situation where you want to build yourself the connection string during runtime (from user inputs, your own configuration files and so on) then you could leverage the functionality of the class MySqlConnectionStringBuilder

MySqlConnectionStringBuilder msb = new MySqlConnectionStringBuilder();
msb.Server = "localhost";
msb.Port = 3306;
msb.UserID = "root";
msb.Password = "xxx";
msb.Database = "test";
MySqlConnection cnn = new MySqlConnection(msb.ConnectionString);
cnn.Open();

Of course, these literal values could be substituted by your own variables. The documentation of this class is surprising difficult to find. The best docs are the one of the Sql Server equivalent. It is interesting that you could read a static connection string from your config file and then change only the property needed.

string conString = ConfigurationManager
                  .ConnectionStrings["MyAppConnection"]
                  .ConnectionString;
MySqlConnectionStringBuilder msb = new MySqlConnectionStringBuilder(conString);
msb.Database = "AnotherDB";
MySqlConnection cnn = new MySqlConnection(msb.ConnectionString);