Customizing the connection string for TableAdapters

  • Article

Visual Studio 2005 now leverages the strongly typed settings file which wraps app.config.  With connection strings stored in app.config/Settings, developers can centralize their connection strings across their application.
Some advantages over VS2003 are:

  • Strongly typed settings file.  
    Rather then having to write AppSettinsReader.GetValue(“NameOfConfigItem”) as Object you can simply write:
    conn.ConnectionString = My.Settings.NorthwindConnectionString 
    The new settings file is compiled by name so you get intellisense as well as compile time verification if you ever change the name.  You also get strong types so you know if the setting returns a string, bool, int, or bitmap.
  • Default Values
    Often developers want to ship their app with some default value.  In 2003 developers had to ship their app.config file just to get the default.  If the end user ever mucked with this value there wasn’t any way to get back to the default.  In 2005, VS now generates a DefaultValue directly in the code using the System.Configuration.DefaultSettingValueAttribute
    With this attribute, developers no longer have to ship anything in the app.config file.  When Settings loads, if it finds a value in app.config, it will use it.  If no value is found, it will use the DefaultSettingsValue.  Not only does this help runtime scenarios, but VS can now host components in the component tray that utilize app.config.  In VS2003, this would cause the “white screen of darn”

Customizing the connection string at runtime
Developers often want to customize the connection string.  In most cases the password simply needs to be provided.  The strongly typed Settings file also has a few events that can be leveraged. 

  1. To add an event handler:
  2. Double click My Project in Solution Explorer
  3. Click the Settings tab on the left side
    In C#, double click the Properties folder in Solution Explorer
  4. Click the View Code toolbar in the top of the designer
  5. Select MySettings Events from the top left drop down and then select SettingsLoaded in the top right.

Here’s a snippet I user for SQL Mobile to replace .\ with the data directory when running under click once.  This same code can be modified to replace the password of a connection string.

Private Sub Settings_SettingsLoaded(ByVal sender As Object, ByVal e As System.Configuration.SettingsLoadedEventArgs) Handles Me.SettingsLoaded

    Dim dataDirectory As String

    ' When running under debug mode, use the data file from the same directory as the executable

    ' otherwise, use the Data Directory which will be set for ClickOnce or MSI based installs

    ' This assumes that MSI based apps placed the data file in the standard non roaming data directory:

    ' C:\Documents and Settings\<UserName>\Application Data\<Company>\<Application Name>\<Version>

    If AppDomain.CurrentDomain.DomainManager IsNot Nothing AndAlso _

        AppDomain.CurrentDomain.DomainManager.ToString().Contains("VSHost") Then

        dataDirectory = Windows.Forms.Application.StartupPath

    Else

        dataDirectory = Windows.Forms.Application.UserAppDataPath

    End If

    Me.Item("NorthwindConnectionString") = My.Settings.NorthwindConnectionString.Replace(".\", dataDirectory & "\")

End Sub

Steve Lasker