How to persist changes to My.Settings.ConnectionString

• Download source - 0.6 KB

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:
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

3. 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.
4. 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").
VB

' This doesn't work because ConnectionStrings are ReadOnly
'My.Settings.ConnectionString1 = "My new string"

' This works!
My.Settings.SetUserOverride("ConnectionString1", "My new string")

5. 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.