Runtime Connection Wizard

• Download binary with necessary controls - 13 Kb
• Download two demo projects with complete source code - 66 Kb

Introduction

Have you ever been looking for a way to let the users change the database connection in runtime? This might be the solution for your problem!

Background

I was frustrated several times when I needed to change the database connection, and found out there was no easy way to do so. I asked the newsgroup forum and got some very useful comments (especially by Linda Liu [MSFT], Thanks!). The answers really helped me so I decided to share the idea in case there is someone else that might benefit from it.

My situation was a project where I had a WindowsForms application that was deployed with ClickOnce. As you might know this means it is very complicated to find and edit the config file manually. It is hidden deep down in the "Document and Settings" directory. So to change connection settings I needed a runtime GUI to do so.

Another obstacle was that the supplied My.Settings objects for connection strings are ReadOnly. A solution would be to store all connection strings as general string values. The downside with that workaround is that all objects that refer to the inbuilt connections will have problems. In this particular project I used many typed datasets with DataAdapters because it was very convenient. But they all relied on the project defined connections for their setup. So either I needed to somehow overcome the ReadOnly obstacle of the connection strings, or I needed to supply all adapters with manual connection strings every time they are used.

My criteria for a solution were the following:

• All connection string definitions should stay in the provided config file section, reachable through the My.Settings object
• It should be possible to change connection settings at runtime
• A nice GUI that would ensure correct connection string syntax
• Verification of valid connection string
• There should be some kind of display of what connection is in use.
• The password should never be visible to the users. This will ensure the connection string is not used outside its application context.
• Encapsulation into controls that made it easy to reuse

Using the code

One approach is to use the Visual Studio IDE inbuilt controls for defining connections. This solution is described by AdoGuy at http://adoguy.com/viewrant.aspx?id=2193. That solution builds on the assumption that you are allowed to distribute Visual Studio components to your customers. I am afraid this is not the case! I also had some problems adjusting that solution to my needs, so I decided to look for another solution.

My solution was to:

• Use the Configuration object of the System.Configuration namespace to manipulate the config settings.
• Use the SqlConnectionStringBuilder of the System.Data.SqlClient namespace to ensure correct syntax.
• Use password entry textboxes and only display a limited part of the resulting connection string to the user.

I decided to limit myself to SQL Server connections as that is enough for my own needs. If you need a more general solution it should be fairly easy to extend the solution to cover other alternatives.

The ctrlConnection is a usercontrol that you can add to your settings form. It will make use of the frmConnection class to validate, edit, test and save the connection string. For your convenience I have also included two controls for editing file name and directory as this is another common need in my settings. Please note that they have design time properties that will configure the dialogs. Finally there is frmSettings to put it all together and give you an example of how to use it.

To use this in your own projects:

1. Add a reference to System.Configuration.dll.
2. Add the Project Settings that you need. Make sure all Connections have default value set, otherwise the connection keys will not be written to app.config file. The code assumes the settings are present when saving.
3. As for the code you can either:
   • Include ctrlConnection and frmConnection in your project.
   • Include the compiled SettingControls.dll in your solution and make a reference to it
4. • When you rebuild your solution the controls should automatically appear in your Toolbox.
   • You need to manually add the controls to the toolbox by referring to the assembly file
   • Add controls to your forms and configure.

Saving connection configuration is done by this procedure:

    Public Sub SaveChange(ByVal connectionName As String)
    Dim Config As Configuration
    Dim Section As ConnectionStringsSection
    Dim Setting As ConnectionStringSettings
    Dim ConnectionFullName As String

    'There is no inbuilt way to change application setting
    'values in the config file.
    'So that needs to be done manually by calling config
    'section object.

    Try
        'No need to bother at all if there is no change ...
        If Not _IsChanged Then Return

        'Concatenate the full settings name
        ConnectionFullName = String.Format("{0}.My
            .MySettings.{1}", System.Reflection.Assembly
            .GetExecutingAssembly.GetName.Name,
                connectionName)

        'Point out the objects to manipulate
        Config = ConfigurationManager.OpenExeConfiguration(
                 ConfigurationUserLevel.None)
        Section = CType(Config.GetSection(
                 "connectionStrings"),
                 ConnectionStringsSection)
        Setting = Section.ConnectionStrings(
                  ConnectionFullName)

        'Ensure connection setting is defined (Note: A
        'default value must be set to save the connection
        'setting!)
        If IsNothing(Setting) Then Throw New Exception(
            "There is no connection with this name defined
            in the config file.")

        'Set value and save it to the config file
        Setting.ConnectionString = Me.ConnectionString
        Config.Save(ConfigurationSaveMode.Full)

        'The above will still not change the runtime value,
        'so this needs to be changed explicitly.
        My.MySettings.Default.Item(connectionName) = Me
          .ConnectionString

    Catch ex As Exception
        Throw New Exception(String.Format("Failed saving
            connection '{0}':{1}{2}", connectionName,
            vbCrLf, ex.Message), ex)
    End Try
End Sub

History

First version distributed 2007-02-02.