Click here to Skip to main content
Click here to Skip to main content

How to persist changes to My.Settings.ConnectionString

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

Introduction

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.

Background

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
                Next
            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)
                Next
            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. Smile | :)

History

  • July 12, 2007: Initial version.

License

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

About the Author

mjmeans
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 http://mjmeans.com/dcd.aspx.

Comments and Discussions

 
GeneralThanks man PinmemberMehdi Shahgholi17-Aug-13 21:52 
SuggestionGeneric implementation [modified] PinmemberAdam Skinner24-May-13 1:42 
QuestionVS 2010; it sort of works, but really doesn't. PinmemberBryanBentz11-Apr-13 3:25 
AnswerRe: VS 2010; it sort of works, but really doesn't. Pinmembermjmeans11-Apr-13 20:06 
QuestionDesperate Need HELP! PinmemberMember 98187987-Feb-13 7:10 
Questionthanks ! Pinmemberjair2226-Feb-13 12:57 
AnswerRe: Could you give me some hints on steps that might be missing PinmemberMember 98187987-Feb-13 7:19 
QuestionSo Totally useful its not even funny. Pinmemberreeselmiller226-Dec-12 9:48 
Question(how) does it works on .NET 4? PinmemberMember 87082467-Mar-12 3:48 
AnswerRe: (how) does it works on .NET 4? PinmemberRyan C. Price23-Apr-12 19:28 
GeneralRe: (how) does it works on .NET 4? PinmemberBryanBentz11-Apr-13 3:26 
GeneralRe: (how) does it works on .NET 4? PinmemberRyan C. Price11-Apr-13 16:17 
GeneralAwesome Pinmembergritsandcornbread4-Jan-11 9:46 
GeneralRe: Awesome Pinmembermjmeans4-Jan-11 17:48 
GeneralMy vote of 5 Pinmembergritsandcornbread4-Jan-11 9:44 
GeneralThanks from Venezuela PinmemberBboyMacareno1-May-10 9:53 
GeneralRe: Thanks from Venezuela Pinmembermjmeans2-May-10 11:45 
Generalusing from a separate project Pinmembercaptainmogo26-Jun-09 4:53 
GeneralRe: using from a separate project Pinmembermjmeans26-Jun-09 19:29 
GeneralRe: using from a separate project Pinmembercaptainmogo29-Jun-09 2:27 
GeneralRe: using from a separate project Pinmembermjmeans29-Jun-09 7:25 
GeneralRe: using from a separate project Pinmembercaptainmogo29-Jun-09 7:49 
GeneralYou might also be interested in... Pinmembermjmeans17-Apr-09 10:24 
Hi. If you like this project, you might also be insterested in a DataConnectionDialog component I am selling. Please see http://mjmeans.com/dcd.aspx[^] for more informaiton.
QuestionHow to control more than one project? PinmemberRicardo Ildefonso16-Apr-09 10:05 
AnswerRe: How to control more than one project? Pinmembermjmeans16-Apr-09 14:56 
GeneralThanks!!! Pinmembere.frankie1018-Mar-09 0:10 
QuestionUsage in A-level coursework Pinmemberultrafez0015-Feb-09 1:15 
AnswerRe: Usage in A-level coursework Pinmembermjmeans15-Feb-09 20:28 
NewsC# version works great! Pinmemberdmageiras10-Feb-09 6:16 
GeneralRe: C# version works great! Pinmembermjmeans10-Feb-09 8:28 
GeneralRe: C# version works great! Pinmemberdmageiras10-Feb-09 22:35 
GeneralRe: C# version works great! PinmemberJlrray16-Mar-09 14:32 
AnswerRe: C# version works great! Pinmemberdmageiras17-Mar-09 5:10 
GeneralRe: C# version works great! PinmemberJlrray17-Mar-09 6:04 
AnswerRe: C# version works great! Pinmemberdmageiras17-Mar-09 6:28 
GeneralRe: C# version works great! PinmemberJlrray17-Mar-09 9:17 
GeneralGREAT solution PinmemberCharles Carr17-Jan-09 10:24 
GeneralRe: GREAT solution Pinmembermjmeans18-Jan-09 17:22 
GeneralReally Great Solution [modified] PinmemberGeorge_Bat4-Dec-08 2:28 
GeneralRe: Really Great Solution Pinmembermjmeans4-Dec-08 8:26 
Generalplease help me i cant know where is the error Pinmemberseman7117-Nov-08 2:07 
GeneralRe: please help me i cant know where is the error Pinmembermjmeans19-Nov-08 20:40 
GeneralYour code. PinmemberRogerio P16-Oct-08 12:56 
GeneralRe: Your code. Pinmembermjmeans19-Nov-08 20:34 
GeneralHelp PinmemberBruno Ricardo Filipe das Neves8-Jul-08 8:56 
GeneralRe: Help Pinmembermjmeans8-Jul-08 10:53 
QuestionCode variance Pinmemberdlcarp3-Jul-08 10:26 
AnswerRe: Code variance Pinmembermjmeans3-Jul-08 20:40 
GeneralRe: Code variance Pinmemberdlcarp4-Jul-08 8:04 
GeneralProblem Pinmembercrosurfer18-Jun-08 2:40 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 1 Aug 2007
Article Copyright 2007 by mjmeans
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid