Share via

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

Comments

  • Anonymous
    July 25, 2005
    If we want to encrypt/decrypt the connection string, are there any suggested "best practice" techniques to use?

  • Anonymous
    August 16, 2005
    The DataAdaptor in Beta 1 had a generate WebMethod property. In Beta 2 this property isn't there. I want to know how to web-enable the dataset and update data across the web using the DataSet designer rather than hand coding it, the way I could use the generateWebMethod property.

    Please let us know whether this feature will be available in VS2005 RTM product.

  • Anonymous
    August 26, 2005

    Overview
    In Visual Studio 2005 we added a number of features to help developers build and deploy applications...

  • Anonymous
    August 26, 2005

    Overview
    In Visual Studio 2005 we added a number of features to help developers build and deploy applications...

  • Anonymous
    January 19, 2006
    This is my workaround, it doesn't need an app.config at runtime:

    http://forums.microsoft.com/msdn/ShowPost.aspx?postid=210683&isthread=false&siteid=1&authhash=a59c997bbbb849c1b318b58c1bf1a579f9b0bcc5&ticks=632732557629342289

  • Anonymous
    March 01, 2006
    In instruction "To add an event handler" step 5 is applicable for VB only.
    AFAIK Visual Studio doesn't help to create c#  event handler and you should do it manually.

  • Anonymous
    March 01, 2006
    The comment has been removed

  • Anonymous
    March 23, 2006
    The comment has been removed

  • Anonymous
    August 25, 2006
    Hi...I'm a solo programer. I've create a new C# project in VS 2005. I've intall SQL Server 2005 Ent. eddition on my localhost and I'm using it as my database server. At VS in "server Explorer" window I create a new data connection to my sql server. Test and OK!At next step I create a new sqlconnection object to use it in my application. I don't want to use dataset, I need just to add data to my table manually. Every thing is good. but now I'm trying to deploy my very simple application to destination computer on a network. I JUST NEED TO CHANGE MY COMPUTER NAME TO SQL SERVER NAME ON THAT NETWORK... WHAT SHOUL I DO?I tried to change .Config file, but it seems these files are using when I'm using "Data Source" in VS2005.ashkan.email@gmail.comThank you.Ashkan

  • Anonymous
    November 15, 2006
    si es que estamos usando SQLExpress, y estamos creando una aplicación .NET para el acceso a datos, hay...

  • Anonymous
    November 15, 2006
    si es que estamos usando SQLExpress, y estamos creando una aplicación .NET para el acceso a datos, hay

  • Anonymous
    July 21, 2007
    PingBack from http://arnulfo.wordpress.com/2007/07/21/visual-studio-and-local-databases/

  • Anonymous
    July 02, 2008
    Modificare le Settings con scope Application

  • Anonymous
    March 31, 2009
    PingBack from http://iuvo.wordpress.com/2009/04/01/how-to-programmatically-change-connection-string/

  • Anonymous
    June 08, 2009
    Workingwithlocaldatabases Overview InVisualStudio2005weaddedanumberoffeaturestohelp...