Change connection string from class library in main application at run-time

Question

You can change the connection string at run-time like this. You make the connection string setting available for writing as a separate property inside the MySettings class:

Partial Friend NotInheritable Class MySettings
 Public WriteOnly Property RunTimeConnectionString()
  Set(ByVal value)
   My.Settings("MyConnectionString") = value
  End Set
 End Property
End Class

Then, in some place when the application is being initialized (before using any table adapters of typed datasets), write something like:

My.Settings.RunTimeConnectionString = My.Settings.ProductionConnectionString

Where ProductionConnectionString is a simple String setting. It is a User Scope setting so every user can change it (by assigning a value to it, similar to the code above) and save it by calling My.Settings.Save()

This code works well for connection strings which were initially created in the main project and stored in it's settings (= app.config file).

The connection string in the app.config actually has a longer name: MyApp.MySettings.MyConnectionString.

When you have a connection string stored in the app.config in a class library project, and reference that project in the main project, the app.config files will somehow be merged, so the class library has it's settings.

The thing that don't know how to do, is change a setting from the class library at run-time. I could copy the connection string setting from the class library to the main project's app.config. I must keep the same name, which looks something like: MyClassLibrary.My.MySettings.MyConnectionString.

Can the same principle I showed above be somehow applied to this second connection string?

Answer 1

I searched more, and found a way, but it isn't really runtime. At least not as runtime as I would like it to be. Anyway, here is the code, I tested it and it worked, but required me to restart the application first. That's not very runtime to me.

    Dim configLocation As String = Reflection.Assembly.GetExecutingAssembly().Location
 Dim config As Configuration.Configuration = Configuration.ConfigurationManager.OpenExeConfiguration(configLocation)
 config.ConnectionStrings.ConnectionStrings.Clear()
 For i As Integer = 0 To Configuration.ConfigurationManager.ConnectionStrings.Count - 1
  Dim connection As New Configuration.ConnectionStringSettings(Configuration.ConfigurationManager.ConnectionStrings(i).Name, My.Settings.ProductionConnectionString)
  connection.ProviderName = Configuration.ConfigurationManager.ConnectionStrings(i).ProviderName
  config.ConnectionStrings.ConnectionStrings.Add(connection)
 Next
 config.Save()

This is the article where I found this code.

Answer 2

Hi Dragolub,

Thanks for the message on the blog. Yes, it is hardly run-time as it requires you to stop running for the changes to be picked up. Unfortunately, because settings are loaded once and only once (when the app domain is loaded), there isn't a way for the settings infrastructure to pick up changes while running.

The only option is to either restart the app or recyle the app pool if a web application. Beyond that, you would have to roll your own.

I did the best I could :-)

Colby Africa

Answer 3

I tested a little more, and found out that the same solution can be used inside a class library.

I made a new class (in the class library) with a shared (static) method like this:

Public Class MySettingsChanger
 Public Shared Sub SetConnectionString(ByVal cnnString As String)
  My.Settings.RunTimeConnectionString = cnnString
 End Sub
End Class

And extended the MySettings class (in the class library) the same way as in the main project:

Namespace My
    Partial Friend NotInheritable Class MySettings
     Public WriteOnly Property RunTimeConnectionString()
      Set(ByVal value)
       My.Settings("MyConnectionString") = value
      End Set
        End Property
    End Class
End Namespace

At least it works in my case. The name of the connection in the main project and in the class library is the same only (the short name, not the whole ProjectNamespace.MySettings.ConnectionName). I haven't tested with having a different name of the connection in the class library, but think it should not matter.