Dynamically change connection string

Dynamically change connection string

Old forum URL: forums.lhotka.net/forums/t/3427.aspx

Nicholas Trevatt posted on Sunday, August 26, 2007

Hi,

I'm building an application that I intend to sell as an off the shelf product and I need to set the connection string after installation so the end user can select a local or remote database server.  Once chosen I need to update the readonly connection string accordingly to reflect her selection.  I would prefer to do this in the application and refresh the data without a restart.

What is the best practice for this surely common task? 
I'm thinking along the path of manually editing the app.config in code but this seems archaic to me!

Also, any ideas why MS made application scoped settings readonly anyway? 

Cheers,
Nicholas

Marjon1 replied on Sunday, August 26, 2007

We have implemented the ability for the end-user is able to change their database connection string within the app.config file by using the ConfigurationManager library; the following code changes the connection string:

  Dim config As System.Configuration.Configuration = _
      ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None)

      If config.ConnectionStrings.ConnectionStrings("Common") Is Nothing Then
        config.ConnectionStrings.ConnectionStrings.Add(New System.Configuration.ConnectionStringSettings("Common", _ConnectionString))
      Else
        config.ConnectionStrings.ConnectionStrings("Common").ConnectionString = _ConnectionString
      End If

      config.Save(ConfigurationSaveMode.Modified)
      ConfigurationManager.RefreshSection("connectionStrings")

This is then accessed through a shared property on a class called Database, CommonConnection which is passed through to all SQL connection constructor.

Assuming that there is no security stored within the database, the only other thing to then do would be to refresh all the objects and/or invalidate all cached lists (assuming this is done while actually logged in to a database and wishing to change).

Nicholas Trevatt replied on Tuesday, August 28, 2007

Excellent!  Thanks very much for this very helpful reply code and all.  I'll be giving it a try soon.

Cheers,
Nicholas

Flippsie replied on Tuesday, June 10, 2008

Hi,

I'm trying this in c# and I have a problem reading this new configuration setting.  I can create the new connection string and it is present and correct in the config file after I have executed my code, but when I try and read it the value is null!

I create the new connection string, I save it, I refresh the section, but I can't read it back.  Can you help?

Copyright (c) Marimer LLC