Ref:
Initially i have set wrong password ,user name and Initial Catalog to test the code.
HTML
<form id="form1" runat="server">
<div>
<table runat="server" id="tblConnectinString" visible="false" border="0" cellpadding="0"
cellspacing="0">
<tr>
<td colspan="2" align="center">
<asp:Label ID="lblMessage" Text="" runat="server" />
</td>
</tr>
<tr>
<td>
DataSource Name
</td>
<td>
<asp:TextBox ID="txtDataSourceName" runat="server" />
<asp:RequiredFieldValidator ErrorMessage="Required" ControlToValidate="txtDataSourceName"
ForeColor="Red" ValidationGroup="UpdateConnectionString" runat="server" />
</td>
</tr>
<tr>
<td>
Initial Catalog
</td>
<td>
<asp:TextBox ID="txtInitialCatalog" runat="server" />
<asp:RequiredFieldValidator ErrorMessage="Required" ControlToValidate="txtInitialCatalog"
ForeColor="Red" ValidationGroup="UpdateConnectionString" runat="server" />
</td>
</tr>
<tr>
<td>
UserName
</td>
<td>
<asp:TextBox ID="txtUserId" runat="server" />
<asp:RequiredFieldValidator ErrorMessage="Required" ControlToValidate="txtUserId"
ForeColor="Red" ValidationGroup="UpdateConnectionString" runat="server" />
</td>
</tr>
<tr>
<td>
Password
</td>
<td>
<asp:TextBox ID="txtPassword" TextMode="Password" runat="server" />
<asp:RequiredFieldValidator ErrorMessage="Required" ControlToValidate="txtPassword"
ForeColor="Red" ValidationGroup="UpdateConnectionString" runat="server" />
</td>
</tr>
<tr>
<td colspan="2" align="center">
<asp:Button Text="Change ConnectinString" OnClick="ChangeConnectionString" runat="server"
ValidationGroup="UpdateConnectionString" />
</td>
</tr>
</table>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false">
<Columns>
<asp:BoundField DataField="CustomerId" HeaderText="Id" />
<asp:BoundField DataField="Name" HeaderText="Name" />
<asp:BoundField DataField="Country" HeaderText="Country" />
</Columns>
</asp:GridView>
</div>
</form>
Namespaces
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using System.Xml;
C#
protected void Page_Load(object sender, EventArgs e)
{
if (!this.IsPostBack)
{
this.PopulateGridView();
}
}
private void PopulateGridView()
{
try
{
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection conn = new SqlConnection(constr))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Customers", conn))
{
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
GridView1.DataSource = dt;
GridView1.DataBind();
}
}
}
}
catch (Exception e)
{
string message = e.Message;
if (message.Contains("A network-related or instance-specific error"))
{
tblConnectinString.Visible = true;
lblMessage.Text = "Please correct the DataSource Name in Connection String";
}
else if (message.Contains("Login failed"))
{
tblConnectinString.Visible = true;
lblMessage.Text = "Please correct the Initial Catalog, User Id or Password in Connection String";
}
}
}
protected void ChangeConnectionString(object sender, EventArgs e)
{
this.ChangeConnectionStringParameters(this.txtDataSourceName.Text.Trim(), this.txtInitialCatalog.Text.Trim(), this.txtUserId.Text.Trim(), this.txtPassword.Text.Trim());
Response.Redirect(Request.Url.AbsoluteUri);
}
private void ChangeConnectionStringParameters(string dataSource, string initialCatalog, string userId, string password)
{
bool isNew = false;
string path = Server.MapPath("~/Web.Config");
XmlDocument doc = new XmlDocument();
doc.Load(path);
XmlNodeList list = doc.DocumentElement.SelectNodes("connectionStrings/add[@name='constr']");
XmlNode node;
isNew = list.Count == 0;
if (isNew)
{
node = doc.CreateNode(XmlNodeType.Element, "add", null);
XmlAttribute attribute = doc.CreateAttribute("name");
attribute.Value = "constr";
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 = initialCatalog;
conStringBuilder.DataSource = dataSource;
conStringBuilder.IntegratedSecurity = false;
conStringBuilder.UserID = userId;
conStringBuilder.Password = password;
node.Attributes["connectionString"].Value = conStringBuilder.ConnectionString;
if (isNew)
{
doc.DocumentElement.SelectNodes("connectionStrings")[0].AppendChild(node);
}
doc.Save(path);
}
Database
I have made use of the following table Customers with the schema as follows.
I have already inserted few records in the table.
You can download database SQL from here.