Click here to Skip to main content
15,867,308 members
Articles / Programming Languages / Visual Basic
Article

How to persist changes to My.Settings.ConnectionString

Rate me:
Please Sign up or sign in to vote.
4.75/5 (32 votes)
1 Aug 2007CPOL2 min read 280.6K   1.4K   62   96
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. VB
    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. VB
    ' 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. :)

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)


Written By
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

 
GeneralMy vote of 5 Pin
gritsandcornbread4-Jan-11 9:44
gritsandcornbread4-Jan-11 9:44 

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.