Update Connection String after Deployment

Code in VB.NET:
To update the ConnectionString in "App.Config" file after deploying a windows application and code to restore database.


Imports System.Configuration
Imports System.Configuration.Install
Imports System.ComponentModel
Imports System.XML
Imports System.Data.SqlClient

Public Class UpdateConnectionString
Inherits Installer

Public Sub New()

End Sub


Public Overrides Sub install(ByVal stateSaver As System.Collections.IDictionary)

MyBase.Install(stateSaver)

Try

Dim ServerName As String = Context.Parameters("PathValue")
'This comes from Set up page (Custom Actions)

Dim ConnectionString As String

If ServerName <> "" Then

Dim AuthenticationMode, UID, PWD As String
AuthenticationMode = Context.Parameters("AuthVal")

If AuthenticationMode = "2" Then
UID = Context.Parameters("UID")
PWD = Context.Parameters("PWD")
End If


Dim AssemblyPath As String = Context.Parameters("assemblypath")
Dim appConfigPath As String = AssemblyPath + ".config"

Dim doc As XmlDocument = New XmlDocument
doc.Load(appConfigPath)

Dim configuration As XmlNode = Nothing
For Each Node As XmlNode In doc.ChildNodes
If Node.Name = "configuration" Then
configuration = Node
Exit For
End If
Next

If Not configuration Is Nothing Then
Dim settingNode As XmlNode = Nothing
For Each ASNode As XmlNode In configuration.ChildNodes
If ASNode.Name = "appSettings" Then
settingNode = ASNode
Exit For
End If
Next

If Not settingNode Is Nothing Then
Dim NumNode As XmlNode = Nothing
For Each NNode As XmlNode In settingNode.ChildNodes
If Not NNode.Attributes("key") Is Nothing Then
If NNode.Attributes("key").Value "ConnectionString" Then
NumNode = NNode
Exit For
End If
End If
Next

If Not NumNode Is Nothing Then
Dim CnAttr As XmlAttribute = NumNode.Attributes("value")

If AuthenticationMode = "2" Then
ConnectionString = "Data Source=" + ServerName + "\SQLEXPRESS;Initial Catalog=SCSAdv1;User ID=" + UID + ";pwd=" + PWD

Else

ConnectionString = "Data Source=" + ServerName + "\SQLEXPRESS;Initial Catalog=SCSAdv1;Integrated Security=True"
End If

CnAttr.Value = ConnectionString
doc.Save(appConfigPath)
End If
End If
End If

Dim cnToRestore As String

If AuthenticationMode = "2" Then
cnToRestore = "Data Source=" + ServerName + "\SQLEXPRESS;Initial Catalog=master;User ID=" + UID + ";pwd=" + PWD

Else

cnToRestore = "Data Source=" + ServerName + "\SQLEXPRESS;Initial Catalog=master;Integrated Security=True"

End If

Dim Sqlcn As New SqlConnection(cnToRestore)
Dim sqlcmd As New SqlCommand()
sqlcmd.CommandType = CommandType.Text
sqlcmd.CommandText = "RESTORE DATABASE [SCSAdv1] FROM DISK = N'" + AssemblyPath.Substring(0, AssemblyPath.IndexOf("SCSAdv1.exe")) + "\SCSAdv1.bak' WITH FILE = 1, MOVE N'SCS_Data' TO N'" + AssemblyPath.Substring(0, AssemblyPath.IndexOf("SCSAdv1.exe")) + "\SCSAdv1.mdf', MOVE N'SCS_Log' TO N'" + AssemblyPath.Substring(0, AssemblyPath.IndexOf("SCSAdv1.exe")) + "\SCSAdv1_log.ldf', NOUNLOAD, STATS = 10"

sqlcmd.Connection = Sqlcn
Try
Sqlcn.Open()
Catch sqlex As SqlException
MsgBox("We are unable to locate the SQL Server instance you have specified." + vbCrLf + "Inorder to run the 'Simulator' you need to manually restore the database after the completion of setup.", MsgBoxStyle.Critical, "")
End Try
sqlcmd.ExecuteNonQuery()
End If

Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "")
End Try
End Sub



Public Overrides Sub Uninstall(
ByVal stateSaver As System.Collections.IDictionary)

MyBase.Uninstall(stateSaver)
End Sub

End Class