Adding ConnectionString at runtime App.Config and display without reloading application?

Question

Hi, We have an internal tool and we need to give the ability to add a connection string programmatically and then reload this connection string without reloading the application at all.

I am kind of confused and wasted 2 days on this and about to give up I have done the following

       var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
        int initialCount = ConfigurationManager.ConnectionStrings.Count;
        string connStringName = "TEST";
        string serverName="Servedr";
        string databaseName = "MyDb";
        string userId="MyUseId";
        string password="MyPassword";
        var connectionStringBuilder = new SqlConnectionStringBuilder
                                          {
                                              DataSource = serverName,
                                              InitialCatalog = databaseName,
                                              UserID = userId,
                                              Password = password
                                          };


        var csSetting = new ConnectionStringSettings(connStringName, connectionStringBuilder.ConnectionString, "System.Data.SqlClient");
        var csSection = config.ConnectionStrings;
        csSection.ConnectionStrings.Add(csSetting);
        config.Save(ConfigurationSaveMode.Modified, true);
        ConfigurationManager.RefreshSection("ConnectionStrings");

        int finalCount = ConfigurationManager.ConnectionStrings.Count;

This should work no? RefreshSection etc... Any suggestions? workarounds without restarting?

Thanks

Answer 1

Why not Load the connection string into a static variable. You could Initialize the variable with the connection string in the app.config. You could then easily change the varible at runtime.

You could have your application save the static variable to the app.config when it is closing down.

Answer 2

Hi, Seems to have lost the ability to comment ... anyway

I dont want to pass the connection string around.Also these will be encrypted!

this needs to be encrypted.

I thought I could do Add a new connection string(UI Layer)encrypt When in the dataLayer (different assembly) eg "ReadStuff" getConnectionString(decrypt connstring and proceed!)

Any more suggestions?

thanks

Answer 3

How about using the reflect method like following code snippets:

        var csSetting = new ConnectionStringSettings(connStringName, connectionStringBuilder.ConnectionString, "System.Data.SqlClient");

        var readonlyField = typeof(ConfigurationElementCollection).GetField("bReadOnly", BindingFlags.NonPublic | BindingFlags.Instance);
        readonlyField.SetValue(ConfigurationManager.ConnectionStrings, false);

        var baseAddMethod = typeof(ConfigurationElementCollection).GetMethod("BaseAdd",
            BindingFlags.Instance | BindingFlags.NonPublic, null, new Type[] { typeof(ConfigurationElement) }, null);
        baseAddMethod.Invoke(ConfigurationManager.ConnectionStrings, new object[] { csSetting });

        readonlyField.SetValue(ConfigurationManager.ConnectionStrings, true);

        int finalCount = ConfigurationManager.ConnectionStrings.Count;