Dynamically modify Connection String in App.Config file in Windows Application using C# and VB.Net

Hi nirmal90,

Check this example. Now please take its reference and correct your code.

FormDesign

Namespaces

C#

using System.IO;
using System.Xml;
using System.Data.SqlClient;

VB.Net

Imports System.Xml
Imports System.Data.SqlClient
Imports System.IO

Code

C#

private void button1_Click(object sender, EventArgs e)
{
    Modify(txtName.Text, txtServer.Text, txtDataBase.Text, txtUID.Text, txtPassword.Text);
}

public void Modify(string name, string server, string dataBase, string uId, string password)
{
    string ApplicationPath = Application.StartupPath;
    string YourPath = Path.GetDirectoryName(ApplicationPath);
    bool isNew = false;

    string path = Path.GetDirectoryName(YourPath) + "\\App.config";
    XmlDocument doc = new XmlDocument();
    doc.Load(path);
    XmlNodeList list = doc.DocumentElement.SelectNodes(string.Format("connectionStrings/add[@name='{0}']", name));
    XmlNode node;
    isNew = list.Count == 0;
    if (isNew)
    {
        node = doc.CreateNode(XmlNodeType.Element, "add", null);
        XmlAttribute attribute = doc.CreateAttribute("name");
        attribute.Value = name;
        node.Attributes.Append(attribute);

        attribute = doc.CreateAttribute("connectionString");
        attribute.Value = "";
        node.Attributes.Append(attribute);

        attribute = doc.CreateAttribute("providerName");
        attribute.Value = "System.Data.SqlClient";
        node.Attributes.Append(attribute);
    }
    else
    {
        node = list[0];
    }
    string conString = node.Attributes["connectionString"].Value;
    SqlConnectionStringBuilder conStringBuilder = new SqlConnectionStringBuilder(conString);
    conStringBuilder.InitialCatalog = dataBase;
    conStringBuilder.DataSource = server;
    conStringBuilder.IntegratedSecurity = false;
    conStringBuilder.UserID = uId;
    conStringBuilder.Password = password;
    node.Attributes["connectionString"].Value = conStringBuilder.ConnectionString;
    if (isNew)
    {
        doc.DocumentElement.SelectNodes("connectionStrings")[0].AppendChild(node);
    }
    doc.Save(path);
}

VB.Net

Private Sub button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button1.Click
    Modify(txtName.Text, txtServer.Text, txtDataBase.Text, txtUID.Text, txtPassword.Text)
End Sub

Public Sub Modify(ByVal name As String, ByVal server As String, ByVal dataBase As String, ByVal uId As String, ByVal password As String)
    Dim ApplicationPath As String = Application.StartupPath
    Dim YourPath As String = Path.GetDirectoryName(ApplicationPath)
    Dim isNew As Boolean = False
    Dim path_1 As String = Path.GetDirectoryName(YourPath) & "\App.config"
    Dim doc As XmlDocument = New XmlDocument()
    doc.Load(path_1)
    Dim list As XmlNodeList = doc.DocumentElement.SelectNodes(String.Format("connectionStrings/add[@name='{0}']", name))
    Dim node As XmlNode
    isNew = list.Count = 0
    If isNew Then
        node = doc.CreateNode(XmlNodeType.Element, "add", Nothing)
        Dim attribute As XmlAttribute = doc.CreateAttribute("name")
        attribute.Value = name
        node.Attributes.Append(attribute)
        attribute = doc.CreateAttribute("connectionString")
        attribute.Value = ""
        node.Attributes.Append(attribute)
        attribute = doc.CreateAttribute("providerName")
        attribute.Value = "System.Data.SqlClient"
        node.Attributes.Append(attribute)
    Else
        node = list(0)
    End If

    Dim conString As String = node.Attributes("connectionString").Value
    Dim conStringBuilder As SqlConnectionStringBuilder = New SqlConnectionStringBuilder(conString)
    conStringBuilder.InitialCatalog = dataBase
    conStringBuilder.DataSource = server
    conStringBuilder.IntegratedSecurity = False
    conStringBuilder.UserID = uId
    conStringBuilder.Password = password
    node.Attributes("connectionString").Value = conStringBuilder.ConnectionString
    If isNew Then
        doc.DocumentElement.SelectNodes("connectionStrings")(0).AppendChild(node)
    End If
    doc.Save(path_1)
End Sub

Screenshot

Connectionstring before modify

<connectionStrings>
  <add name="constr" connectionString="Data Source=.;Initial Catalog=Test;Integrated Security=False;User ID=sa;Password=password@123" providerName="System.Data.SqlClient" />
</connectionStrings>

Connectionstring after modify

<connectionStrings>
  <add name="constr" connectionString="Data Source=test;Initial Catalog=test;Integrated Security=False;User ID=test;Password=test" providerName="System.Data.SqlClient" />
</connectionStrings>