How to Change Connection String at Run Time

Submitted by admin on Thursday, January 6, 2011 - 10:03.

One of the challenging parts in VB.NET is to change the connection string of your database location when you run your application.

This tutorial will help you solve this problem. This will apply only to SQL Server but it can be also modified easily to work with MS Access and other DBMS.

In my case I initialize this on the Form Load event on my Login form.

Private Sub frmLogin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ReadfromXML()
        MySettingsChanger.SetConnectionString(cnString)
 
End Sub

ReadfromXML is a procedure that reads the value from data.xml and assigns the value to cnString variable.

Public Sub ReadfromXML()
        Dim I As Integer
 
        For I = 0 To 2
            Dim doc As XmlDocument = New XmlDocument()
            doc.Load("data.xml")
 
            Dim root As XmlElement = doc.DocumentElement
 
            ServerName = root.Attributes.Item(0).Value
            DatabaseName = root.Attributes.Item(1).Value
 
            cnString = "Data Source=" & ServerName & ";Initial Catalog=" & DatabaseName & ";Persist Security Info=True;User ID=sa;Password=yourpasswordhere"
 
            Dim CN As SqlConnection
            CN = New SqlConnection
 
            Try
                With CN
                    If .State = ConnectionState.Open Then .Close()
 
                    .ConnectionString = cnString
                    .Open()
 
                    Exit For
                End With
            Catch ex As Exception
                If Err.Number = 5 Then
                    MsgBox("Cannot connect to server. Make sure that the server is running. " & vbCrLf & vbCrLf & "Otherwise please check for the configuration.", MsgBoxStyle.Exclamation)
 
                    Dim DBPath As New frmDBPath
 
                    DBPath.ShowDialog()
                End If
            Finally
                CN.Close()
            End Try
        Next I
End Sub

Here’s the value of data.xml file.

<?xml version="1.0"?>
<database server="computer_name" name="database_name">
</database>

If the connection is successful it will call the MySettingsChanger Class and assign the value to SetConnectionString procedure.

Public Class MySettingsChanger
    Public Shared Sub SetConnectionString(ByVal cnnString As String)
        My.Settings.RunTimeConnectionString = cnnString
    End Sub
End Class

You can create a class MySettingsChanger under the Project menu then “Add Class” and paste the above code in the code window.

cnnString will then pass its value to RunTimeConnectionString which can be found under the Project settings.

Here’s the complete code after you click the View Code:

Namespace My
    
    'This class allows you to handle specific events on the settings class:
    ' The SettingChanging event is raised before a setting's value is changed.
    ' The PropertyChanged event is raised after a setting's value is changed.
    ' The SettingsLoaded event is raised after the setting values are loaded.
    ' The SettingsSaving event is raised before the setting values are saved.
    Partial Friend NotInheritable Class MySettings
        Public WriteOnly Property RunTimeConnectionString()
 
            Set(ByVal value)
                My.Settings("ADC_DataConnectionString") = value
            End Set
        End Property
    End Class
End Namespace

Some of the code above is generated automatically. Only the RunTimeConnectionString Property has been set here.