Post
Ask Question

How To Change Database In Web.Config File Programmatically

In this article, you will learn how to change a database name dynamically in a Web.config file in ASP.NET.

Step 1

Open Visual Studio and create a New Project, change the project name to Databasechange.

How We Change Database In Web.config File Programmatically

Step 2

Add a new item to the project.

How We Change Database In Web.config File Programmatically 

Step 3

Add new web form and rename it as Databsechnge.

How We Change Database In Web.config File Programmatically 

Step 4

Add Bootstrap References in the page and add a Dropdownlist and a button in the page.

 How We Change Database In Web.config File Programmatically
Code Snippet
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="databasechnge.aspx.cs" Inherits="Databasechange.databasechnge" %>  
  2. <!DOCTYPE html>  
  3. <html xmlns="http://www.w3.org/1999/xhtml">  
  4. <head runat="server">  
  5.     <meta name="viewport" content="width=device-width, initial-scale=1" />  
  6.     <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />  
  7.     <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>  
  8.     <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>  
  9. </head>  
  10. <body>  
  11.     <form id="form1" runat="server">  
  12.         <div class="col-md-4 col-md-offset-4" style="margin-top: 40px">  
  13.             <div class="login-panel panel panel-default">  
  14.   
  15.                 <div class="panel-heading" style="background-color: lightgreen">  
  16.                     <h3 class="panel-title" style="text-align: center">Change Database in Web.config</h3>  
  17.                 </div>  
  18.                 <div class="panel-body">  
  19.                     <div class="form-group">  
  20.                         <asp:DropDownList ID="DropDownList1" runat="server" CssClass="form-control">  
  21.                             <asp:ListItem>Select Database</asp:ListItem>  
  22.                         </asp:DropDownList>  
  23.                     </div>  
  24.                     <div class="form-group">  
  25.                         <asp:Button ID="Button1" runat="server" Text="Change Database" OnClick="Button1_Click" CssClass="btn-block" />  
  26.                     </div>  
  27.                 </div>  
  28.             </div>  
  29.         </div>  
  30.     </form>  
  31. </body>  
  32. </html>  

Step 5

Now, Right click on the page and click on View Code and add the following code to bind the dropdown with all databases available on a given server.

  1. private void FillDropDown()  
  2.        {  
  3.            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConctionString"].ToString()))  
  4.            {  
  5.   
  6.                SqlCommand cmd = new SqlCommand("SELECT name FROM sys.sysdatabases", con);  
  7.                con.Open();  
  8.                SqlDataReader dr = cmd.ExecuteReader();  
  9.                if (dr != null)  
  10.                {  
  11.                    while (dr.Read())  
  12.                        DropDownList1.Items.Add(dr.GetString(0));  
  13.                }  
  14.            }  
  15.        }  

Step 6

Now, call the FillDropDown()on page load events.

  1. protected void Page_Load(object sender, EventArgs e)  
  2.        {  
  3.          FillDropDown();  
  4.        }  

Step 7

Now, we add a function Changedatabasename and add the following code to this function.

  1. private void Changedatabasename(string name)  
  2.       {  
  3.           bool isbool = false;  
  4.           string path = Server.MapPath("~/Web.Config");  
  5.           XmlDocument doc = new XmlDocument();  
  6.           doc.Load(path);  
  7.           XmlNodeList list = doc.DocumentElement.SelectNodes(string.Format("connectionStrings/add[@name='{0}']", name));  
  8.           XmlNode node;  
  9.           isbool = list.Count == 0;  
  10.           if (isbool)  
  11.           {  
  12.               node = doc.CreateNode(XmlNodeType.Element, "add", null);  
  13.               XmlAttribute attribute = doc.CreateAttribute("name");  
  14.               attribute.Value = name;  
  15.               node.Attributes.Append(attribute);  
  16.   
  17.               attribute = doc.CreateAttribute("connectionString");  
  18.               attribute.Value = "";  
  19.               node.Attributes.Append(attribute);  
  20.   
  21.               attribute = doc.CreateAttribute("providerName");  
  22.               attribute.Value = "System.Data.SqlClient";  
  23.               node.Attributes.Append(attribute);  
  24.           }  
  25.           else  
  26.           {  
  27.               node = list[0];  
  28.           }  
  29.           string ConctionString = node.Attributes["connectionString"].Value;  
  30.           SqlConnectionStringBuilder conStringBuilder = new SqlConnectionStringBuilder(ConctionString);  
  31.           conStringBuilder.InitialCatalog = DropDownList1.SelectedValue;  
  32.           node.Attributes["connectionString"].Value = conStringBuilder.ConnectionString;  
  33.           if (isbool)  
  34.           {  
  35.               doc.DocumentElement.SelectNodes("connectionStrings")[0].AppendChild(node);  
  36.           }  
  37.           doc.Save(path);  
  38.       }  

Step 8

Now, call the method Changedatabasename() on clicking button1.

  1. protected void Button1_Click(object sender, EventArgs e)  
  2.        {  
  3.            Changedatabasename("ConctionString");  
  4.      
  5.        }  
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Configuration;  
  4. using System.Data.SqlClient;  
  5. using System.Linq;  
  6. using System.Web;  
  7. using System.Web.UI;  
  8. using System.Web.UI.WebControls;  
  9. using System.Xml;  
  10.   
  11. namespace WebApplication4  
  12. {  
  13.     public partial class WebForm1 : System.Web.UI.Page  
  14.     {  
  15.         protected void Page_Load(object sender, EventArgs e)  
  16.         {  
  17.             FillDropDown();  
  18.         }  
  19.         private void FillDropDown()  
  20.         {  
  21.             using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConctionString"].ToString()))  
  22.             {  
  23.   
  24.                 SqlCommand cmd = new SqlCommand("SELECT name FROM sys.sysdatabases", con);  
  25.                 con.Open();  
  26.                 SqlDataReader dr = cmd.ExecuteReader();  
  27.                 if (dr != null)  
  28.                 {  
  29.                     while (dr.Read())  
  30.                         DropDownList1.Items.Add(dr.GetString(0));  
  31.                 }  
  32.             }  
  33.         }  
  34.         private void Changedatabasename(string name)  
  35.         {  
  36.             bool isbool = false;  
  37.             string path = Server.MapPath("~/Web.Config");  
  38.             XmlDocument doc = new XmlDocument();  
  39.             doc.Load(path);  
  40.             XmlNodeList list = doc.DocumentElement.SelectNodes(string.Format("connectionStrings/add[@name='{0}']", name));  
  41.             XmlNode node;  
  42.             isbool = list.Count == 0;  
  43.             if (isbool)  
  44.             {  
  45.                 node = doc.CreateNode(XmlNodeType.Element, "add", null);  
  46.                 XmlAttribute attribute = doc.CreateAttribute("name");  
  47.                 attribute.Value = name;  
  48.                 node.Attributes.Append(attribute);  
  49.   
  50.                 attribute = doc.CreateAttribute("connectionString");  
  51.                 attribute.Value = "";  
  52.                 node.Attributes.Append(attribute);  
  53.   
  54.                 attribute = doc.CreateAttribute("providerName");  
  55.                 attribute.Value = "System.Data.SqlClient";  
  56.                 node.Attributes.Append(attribute);  
  57.             }  
  58.             else  
  59.             {  
  60.                 node = list[0];  
  61.             }  
  62.             string ConctionString = node.Attributes["connectionString"].Value;  
  63.             SqlConnectionStringBuilder conStringBuilder = new SqlConnectionStringBuilder(ConctionString);  
  64.             conStringBuilder.InitialCatalog = DropDownList1.SelectedValue;  
  65.             node.Attributes["connectionString"].Value = conStringBuilder.ConnectionString;  
  66.             if (isbool)  
  67.             {  
  68.                 doc.DocumentElement.SelectNodes("connectionStrings")[0].AppendChild(node);  
  69.             }  
  70.             doc.Save(path);  
  71.         }  
  72.   
  73.   
  74.   
  75.         protected void Button1_Click(object sender, EventArgs e)  
  76.         {  
  77.             Changedatabasename("ConctionString");  
  78.   
  79.         }  
  80.     }  
  81. }  
Step 9

Now, run the project

How We Change Database In Web.config File Programmatically 

Step 10

Select Database in DropDown and click on the button.

How We Change Database In Web.config File Programmatically 

Step 11

Choose a database and click on the button.

How We Change Database In Web.config File Programmatically 

Step 12

Check the connection string and check database name, it's successfully changed.

How We Change Database In Web.config File Programmatically


Similar Articles