In this article I will explain how to programmatically add, modify (change) and update SQL connection strings at runtime in ASP.Net Web.Config file using C# and VB.Net.

He has explained how we can access and update the different attributes of a connection string like DataSource, IntitialCatalog, UserId, Password and IntegratedSecurity.
 
Namespaces
You will need to inherit the following namespaces.
C#
using System.Xml;
using System.Data.SqlClient;
 
VB.Net
Imports System.Xml
Imports System.Data.SqlClient
 
 
Code snippet to add or update Connection String in Web.Config file
The following method adds or updates the connection string based on the connection string name. If the connection string with the name does not exists a new connection string node will be created in the Web.Config file.
C#
private void AddUpdateConnectionString(string name)
{
    bool isNew = false;
    string path = Server.MapPath("~/Web.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 = "TestDB";
    conStringBuilder.DataSource = "myserver";
    conStringBuilder.IntegratedSecurity = false;
    conStringBuilder.UserID = "test";
    conStringBuilder.Password = "12345";
    node.Attributes["connectionString"].Value = conStringBuilder.ConnectionString;
    if (isNew)
    {
        doc.DocumentElement.SelectNodes("connectionStrings")[0].AppendChild(node);
    }
    doc.Save(path);
}
 
VB.Net
Private Sub AddUpdateConnectionString(name As String)
   Dim isNew As Boolean = False
   Dim path As String = Server.MapPath("~/Web.Config")
   Dim doc As New XmlDocument()
   doc.Load(path)
   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 New SqlConnectionStringBuilder(conString)
   conStringBuilder.InitialCatalog = "TestDB"
   conStringBuilder.DataSource = "myserver"
   conStringBuilder.IntegratedSecurity = False
   conStringBuilder.UserID = "test"
   conStringBuilder.Password = "12345"
   node.Attributes("connectionString").Value = conStringBuilder.ConnectionString
   If isNew Then
            doc.DocumentElement.SelectNodes("connectionStrings")(0).AppendChild(node)
  End If
   doc.Save(path)
End Sub
 
 
Downloads
You can download the complete source code in VB.Net and C# using the download link provided below.