Click here to Skip to main content
14,383,854 members

How to persist changes to My.Settings.ConnectionString

Rate this:
4.74 (30 votes)
Please Sign up or sign in to vote.
4.74 (30 votes)
1 Aug 2007CPOL
This article describes a solution for persisting changes to the ReadOnly My.Settings.ConnectionString with nearly trivial code.


Connection strings in the My.Settings class are Application scoped. This causes a problem when deploying an application because the development machine only rarely will have the same connection string as the deployment target machine of your customer. Add to this the fact that strongly typed datasets' table adapters get their connection string from the My.Settings class, and this creates the need for some kind of workaround. The most common workaround I have seen involves setting the TableAdapter.Connection.ConnectionString property with the appropriate connection string before calling Fill(). However, this is non-intuitive for most people, and for very large projects, updating these commands can be problematic.

Here is a better way to persist changes to application scoped connection strings for strongly typed datasets. This will handle any number of connection string overrides you want, with only trivial editing. No need to modify or re-modify the settings.designer.vb file or set TableAdapter.Connection.ConnectionString settings on every table adapter in your project.


How does it work? Well, all the settings, including the application scoped settings, are not kept in memory as read-only. So, the run-time value can be modified, and all future calls to the My.Settings item will get the modified value. What the application scoping does is refuse to persist them. So, this little addition to the MySettings class allows you to:

  1. Update the runtime value of the application scoped setting.
  2. Apply any saved user scoped override setting via the SettingsLoaded event before they are ever referenced by table adapters.
  3. Persist any modified application scoped setting to the user scoped override setting via the SettingsSaving event.

Using the Code

  1. Go to your project Properties, Settings, and make a user scoped string entry for each Application scoped connection string with the same base name plus an added suffix. For example, I use the suffix "UserOverride". If I have two application scoped connection settings called "ConnectionString1" and "ConnectionString2", then I create two user scoped strings (not (connection strings)) called "ConnectionString1UserOverride" and "ConnectionString2UserOverride".
  2. Create a new module called Settings.UserOverride.vb, and insert this code:
  3. Option Strict On
    Option Explicit On
    Namespace My
        Partial Friend NotInheritable Class MySettings
            Inherits Global.System.Configuration.ApplicationSettingsBase
            Private Shared userOverrides() As String = { _
                "ConnectionString1", _
                "ConnectionString2" _
            Private Shared userOverrideSuffix As String = "UserOverride"
            Public Sub SetUserOverride(ByVal [property] As String, _
                                       ByVal value As String)
                Me([property]) = value
            End Sub
            Private Sub userOverride_SettingsLoaded(ByVal sender As Object, _
                    ByVal e As System.Configuration.SettingsLoadedEventArgs) _
                Handles Me.SettingsLoaded
                Dim userProperty As String
                For Each appProperty As String In userOverrides
                    userProperty = appProperty & userOverrideSuffix
                    If CType(Me(userProperty), String).Length > 0 Then
                        Me(appProperty) = Me(userProperty)
                    End If
            End Sub
            Private Sub userOverride_SettingsSaving(ByVal sender As Object, _
                    ByVal e As System.ComponentModel.CancelEventArgs) _
                Handles Me.SettingsSaving
                Dim userProperty As String
                For Each appProperty As String In userOverrides
                    userProperty = appProperty & userOverrideSuffix
                    Me(userProperty) = Me(appProperty)
            End Sub
        End Class
    End Namespace
  4. Edit the userOverrides() array to include the names of the Application scoped connection strings in your application for which you have created user overrides. If you used a different suffix for your overrides, then change the userOverrideSuffix value also.
  5. Now, wherever you need to update your connection string, call SetUserOverride(). For example, whenever you would want to call My.Settings.ConnectionString1 = "My new string", which is not allowed, you instead call My.Settings.SetUserOverride("ConnectionString1", "My new string").
  6. ' This doesn't work because ConnectionStrings are ReadOnly
    'My.Settings.ConnectionString1 = "My new string"
    ' This works!
    My.Settings.SetUserOverride("ConnectionString1", "My new string")
  7. Go to your My Project, Application Settings, and make sure that Save My.Settings on Shutdown is enabled.

Points of Interest

Las Vegas! Walt Disnet World Florida! Maybe I'll think of some REALLY interesting ones later. :)


  • July 12, 2007: Initial version.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Systems Engineer Mark J Means Consulting
United States United States
I have been a software consultant since 1985 working on everything from the Commodore VIC-20 & RadioShack CoCo games to 8051 Embedded USB Microcontrollers to Windows Vista database applications. I have written over a half million lines of code since 2004. Please see my DataConnectionDialog control at

Comments and Discussions

QuestionMy.Settings.SetUserOverride is not a member of settings Pin
herbaliser1-May-19 9:02
memberherbaliser1-May-19 9:02 
QuestionI'm Lost Pin
Member 107088755-Sep-17 14:09
memberMember 107088755-Sep-17 14:09 
GeneralThanks man Pin
Mehdi Shahgholi17-Aug-13 22:52
memberMehdi Shahgholi17-Aug-13 22:52 
SuggestionGeneric implementation Pin
Adam Skinner24-May-13 2:42
memberAdam Skinner24-May-13 2:42 
QuestionVS 2010; it sort of works, but really doesn't. Pin
BryanBentz11-Apr-13 4:25
memberBryanBentz11-Apr-13 4:25 
AnswerRe: VS 2010; it sort of works, but really doesn't. Pin
mjmeans11-Apr-13 21:06
membermjmeans11-Apr-13 21:06 
QuestionDesperate Need HELP! Pin
Member 98187987-Feb-13 8:10
memberMember 98187987-Feb-13 8:10 
Questionthanks ! Pin
jair2226-Feb-13 13:57
memberjair2226-Feb-13 13:57 
AnswerRe: Could you give me some hints on steps that might be missing Pin
Member 98187987-Feb-13 8:19
memberMember 98187987-Feb-13 8:19 
QuestionSo Totally useful its not even funny. Pin
reeselmiller226-Dec-12 10:48
memberreeselmiller226-Dec-12 10:48 
Question(how) does it works on .NET 4? Pin
Member 87082467-Mar-12 4:48
memberMember 87082467-Mar-12 4:48 
AnswerRe: (how) does it works on .NET 4? Pin
Ryan C. Price23-Apr-12 20:28
memberRyan C. Price23-Apr-12 20:28 
GeneralRe: (how) does it works on .NET 4? Pin
BryanBentz11-Apr-13 4:26
memberBryanBentz11-Apr-13 4:26 
GeneralRe: (how) does it works on .NET 4? Pin
Ryan C. Price11-Apr-13 17:17
memberRyan C. Price11-Apr-13 17:17 
GeneralAwesome Pin
gritsandcornbread4-Jan-11 10:46
membergritsandcornbread4-Jan-11 10:46 
GeneralRe: Awesome Pin
mjmeans4-Jan-11 18:48
membermjmeans4-Jan-11 18:48 
GeneralMy vote of 5 Pin
gritsandcornbread4-Jan-11 10:44
membergritsandcornbread4-Jan-11 10:44 
GeneralThanks from Venezuela Pin
BboyMacareno1-May-10 10:53
memberBboyMacareno1-May-10 10:53 
GeneralRe: Thanks from Venezuela Pin
mjmeans2-May-10 12:45
membermjmeans2-May-10 12:45 
Generalusing from a separate project Pin
captainmogo26-Jun-09 5:53
membercaptainmogo26-Jun-09 5:53 
GeneralRe: using from a separate project Pin
mjmeans26-Jun-09 20:29
membermjmeans26-Jun-09 20:29 
GeneralRe: using from a separate project Pin
captainmogo29-Jun-09 3:27
membercaptainmogo29-Jun-09 3:27 
GeneralRe: using from a separate project Pin
mjmeans29-Jun-09 8:25
membermjmeans29-Jun-09 8:25 
GeneralRe: using from a separate project Pin
captainmogo29-Jun-09 8:49
membercaptainmogo29-Jun-09 8:49 
GeneralYou might also be interested in... Pin
mjmeans17-Apr-09 11:24
membermjmeans17-Apr-09 11:24 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Posted 12 Jul 2007


62 bookmarked