Click here to Skip to main content
15,868,101 members
Articles / Desktop Programming / Windows Forms
Article

Runtime Connection Wizard

Rate me:
Please Sign up or sign in to vote.
3.47/5 (10 votes)
10 Feb 20074 min read 79.5K   1.2K   34   23
A control to define and save SQL Connections in runtime

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

Connection dialog

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.

Settings Form

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

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Web Developer
Sweden Sweden
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Generalgood work! exactly what I was lookin for :-) Pin
gijoe66611-Jun-09 10:59
gijoe66611-Jun-09 10:59 
GeneralUnable to work when control is used as a dll Pin
Randy Parsons9-Jul-08 9:10
Randy Parsons9-Jul-08 9:10 
GeneralRaiseEvent: connectionStringChanged Pin
Her Highness3-May-08 9:32
Her Highness3-May-08 9:32 
GeneralGetting "Failed saving connection 'DemoConnection'" Error [modified] Pin
AndrewHuk8-Dec-07 7:11
AndrewHuk8-Dec-07 7:11 
GeneralRe: Getting "Failed saving connection 'DemoConnection'" Error Pin
donut.biz8-Feb-08 15:28
donut.biz8-Feb-08 15:28 
QuestionHelp! Pin
sudjp12-Sep-07 23:52
sudjp12-Sep-07 23:52 
GeneralMany Thanks Pin
CodeCarpenter15-Aug-07 7:02
CodeCarpenter15-Aug-07 7:02 
QuestionSome clarification please. Pin
Steveinbeloit30-May-07 6:29
Steveinbeloit30-May-07 6:29 
AnswerRe: Some clarification please. Pin
Jakob L30-May-07 10:20
Jakob L30-May-07 10:20 
GeneralNeed help here Pin
Brljak29-Mar-07 5:59
Brljak29-Mar-07 5:59 
GeneralRe: Need help here Pin
thermskin29-Mar-07 12:27
thermskin29-Mar-07 12:27 
GeneralRe: Need help here Pin
Jakob Lithner29-Mar-07 21:55
Jakob Lithner29-Mar-07 21:55 
Sorry, I don't have the time to engage in extensive hands-on support ....
If you download the code I am sure you will be able to work it out yourself.
QuestionChange connectionString after? Pin
thermskin22-Mar-07 12:05
thermskin22-Mar-07 12:05 
AnswerRe: Change connectionString after? Pin
Jakob Lithner22-Mar-07 23:06
Jakob Lithner22-Mar-07 23:06 
GeneralRe: Change connectionString after? Pin
thermskin25-Mar-07 20:34
thermskin25-Mar-07 20:34 
GeneralRe: Change connectionString after? Pin
Jakob Lithner29-Mar-07 21:51
Jakob Lithner29-Mar-07 21:51 
Questionupdate app.config during runtime? Pin
imranmp26-Feb-07 7:33
imranmp26-Feb-07 7:33 
AnswerRe: update app.config during runtime? [modified] Pin
Jakob Lithner27-Feb-07 2:38
Jakob Lithner27-Feb-07 2:38 
QuestionRe: update app.config during runtime? Pin
imranmp27-Feb-07 3:32
imranmp27-Feb-07 3:32 
AnswerRe: update app.config during runtime? Pin
Jakob Lithner27-Feb-07 4:13
Jakob Lithner27-Feb-07 4:13 
GeneralRe: update app.config during runtime? [modified] Pin
imranmp28-Feb-07 5:10
imranmp28-Feb-07 5:10 
GeneralRe: update app.config during runtime? Pin
Jakob Lithner28-Feb-07 22:39
Jakob Lithner28-Feb-07 22:39 
GeneralGood work. Pin
TarunNeo20-Feb-07 7:02
TarunNeo20-Feb-07 7:02 

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.