Managing Application Scoped Connection Strings

If you haven't used DataSets and TableAdapters in your WinForms application, you probably will at some point. As with everything, there are positives and negatives. One big negative I experienced (and maybe you will too), is managing those darn connection strings. So let's get started.

After hard work, long hours spent finishing your software product, it's time to get that application to its end destination. By now, you would've noticed that the connection strings used for your DataSets cannot be changed during runtime (according to Microsoft (and several other articles and blogs)). Ultimately, any and all connection strings are "Application Scoped" which just simply means that whatever value is contained within that specific setting, is used for the entire application (or "globally" if you prefer). Other settings (usually those set to "User Scoped") are managed on a per-user basis. In other words, User A will have his/her setting values, and User B will have his/her setting values. With Application Scoped settings, User A and User B will share the same setting and its value.

Many articles out there claim that these values ("Application Scoped") cannot be changed during runtime. Some give you pages and pages of code to get the settings to change. Some even provide a workaround by overriding the actual setting with a temporary setting. Well, I'm here to give you the ultimate solution. Before I show you how, you need to understand why people claim that "Application Scoped"-settings cannot be changed: When your application is launched, these "Application Scoped" settings are loaded and used for the duration of the application session. Once the application exits, the "lock" or "read-only" status is removed from these settings.

Unlike normal settings that can be accessed via My.Settings (VB) or Properties.Settings.Default (C#), the application scoped settings requires the use of the ConfigurationManager.

IMPORTANT: In order to use the Configuration Manager, you need to import the namespace in the code file AND add the reference to your project (VB: Project > Settings > References / C#: Right-click References).

Note: For this article, I will be providing the source code in C#. It is easily adaptable to VB.NET or you can simply convert the code using an online converter.

After you have added the reference to System.Configuration, import the namespace into your code file:

using System.Configuration;

Now, you need to open the settings, make the changes, save it, then refresh the settings so that it reflects the new value:

Configuration config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
config.ConnectionStrings.ConnectionStrings["connStringName"].ConnectionString = "New conn string";
config.Save(ConfigurationSaveMode.Modified, true);
ConfigurationManager.RefreshSection("connectionStrings");

• ["connStringName"] refers to the name of the setting (you can find this by opening your Settings);
  
• "New conn string" - You should put the new connection string you would like to use here.
  
• ("connectionStrings") should not be changed under any circumstances (if you change it, the application will fail to register the connection string).