[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1087
  • Last Modified:

vb.net VS2008 changing connection string in app.config or machine.config

I am looking at using the app.config or machine.config to store connection information to an MSSQL database ideally encrypted. Windows forms application.

I can set up the connection in the app or machine config and use it fine but I have issues where I want to change the connection string - e.g. if password changes. Attached code is run from a form to set up the connection string. If it exists I would like it to update the existing info.

appreciate any help.
Private Shared Sub WriteEncryptedConnectionStringSection(ByVal name As String, ByVal constring As String, ByVal provider As String)

        Dim config As Configuration = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.PerUserRoamingAndLocal)
        Dim section As ConnectionStringsSection = config.ConnectionStrings

        If section Is Nothing Then
            section = New ConnectionStringsSection
            config.Sections.Add("connectionSettings", section)

        End If

        If Not section.SectionInformation.IsProtected Then
            section.SectionInformation.ProtectSection("RsaProtectedConfigurationProvider")
        End If

        Dim csExisting As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("ConnectionString2")
        If Not csExisting Is Nothing Then
            'Update connection string ** This is where I want to update existing connection
            'csExisting.ConnectionString = constring
        Else
            Dim cs As New ConnectionStringSettings(name, constring, provider)
            section.ConnectionStrings.Add(cs)
            section.SectionInformation.ForceSave = True
        End If

        config.Save(ConfigurationSaveMode.Full)

    End Sub

Open in new window

0
donhannam
Asked:
donhannam
  • 4
  • 3
  • 2
  • +3
1 Solution
 
HainKurtSr. System AnalystCommented:
check this sample I grabbed from

http://odetocode.com/Articles/418.aspx

when you use

csExisting.ConnectionString = constring
csExisting.Save

what do you get?
protected void toggleDebugButton_Click(object sender, EventArgs e) {
    Configuration config;
    config = WebConfigurationManager.OpenWebConfiguration("~");
    CompilationSection compilation;
    compilation = config.GetSection("system.web/compilation") as CompilationSection;

    if (compilation != null) {
        compilation.Debug = !compilation.Debug;
        config.Save();
        WriteMessage("Debug setting is now: " + compilation.Debug);
    }
}

Open in new window

0
 
joriszwaenepoelCommented:
That is why I always try to use integrated security.  That way, you don't need to supply a password in the connection string, and there is no need to encrypt it.
0
 
donhannamAuthor Commented:
Thanks for comments:-

When I use csExisting.Save I get:-

The configuration is read only.

I looked at code but seems to refer to compilation sections which I think are only for web applications?.

I would like to have an option of integrated security or user name password - even with integrated there are instances where I would want to change the connection string on an application.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
donhannamAuthor Commented:
emoreau:

I spent some time looking at the link you mentioned - I think I am missing something - I could not find anything about saving a connection string to a configuration file.

0
 
joriszwaenepoelCommented:
You could application settings for the connection string.
User settings can be modified and saved.

http://msdn.microsoft.com/en-us/library/k4s6c3a0.aspx
0
 
donhannamAuthor Commented:
joriszwaenepoel:

This article does say that they can be changed but not how to do it in code.
0
 
Éric MoreauSenior .Net ConsultantCommented:
If you have to change the password of your connection string, you need to redeploy your config file. Otherwise, you may open the file as a XML file and overwrite the node you want.
0
 
AshokCommented:
Create a new module called Settings.UserOverride.vb, and insert this code:

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

Step 2:
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".

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

Step 4: (How to use)
' This works!
My.Settings.SetUserOverride("ConnectionString1", "My new string")

Note:  Go to your My Project, Application Settings, and make sure that Save My.Settings on Shutdown is enabled.

HTH
Ashok
0
 
joriszwaenepoelCommented:
Saving user settings is only one line of code:

My.Settings.Save()
0
 
CodeCruiserCommented:
Settings with Application scope are readonly. Settings with the User scope can be modified and persisted. Setting of type ConnectionString can only have Application scope. So you would have to change the type to string and scope to User and then deal with the connectionstring as a string.
0
 
donhannamAuthor Commented:
Thanks Ashok - Just got around to trying this and worked well with a connection string set up in application settings.

Surprised this is so hard. Looks like the app.config etc set up to allow a change of connection?. Anyway this works so thanks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now