10:55 PM
Unknown
One of the challenging tasks in .NET programming is changing the connection string on runtime. In this post, I will give you the simple step to complete this task.
Step 1: Create a SQL Conneciton form:
On the Solution Explorer panle, right click on your project, select Add->New Item. Select Window Form. Name it SLQConnection, then click OK. Design it as the figure bellow:
Step 2: Create an app.config file
On the Solution Explorer, right click on your project, select Add->New Item. On the dialog appeared, click on Application Configuration File, name it app.config. Click OK
Open app.config file, modifying it as the figure bellow
Step 3: Coding
On the SQLConnection.cs file, add the following code:
public void updateConfigFile(string con)
{
XmlDocument XmlDoc = new XmlDocument();
//Road the Config file
XmlDoc.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
foreach (XmlElement xElement in XmlDoc.DocumentElement)
{
if (xElement.Name == "connectionStrings")
{
//setting the connection string
xElement.FirstChild.Attributes[2].Value = con;
}
}
//write the connection string in app.config file
XmlDoc.Save(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
}
Double click on Connect button of the Form created in step 1
Add the following code to the On_Clikc event of the button
StringBuilder Con = new StringBuilder("Data Source=");
Con.Append(txtServerName.Text);
Con.Append(";Initial Catalog=");
Con.Append(txtDBName.Text);
Con.Append(";Integrated Security=SSPI;");
string strCon = Con.ToString();
updateConfigFile(strCon);
//create a new connection
SqlConnection conn = new SqlConnection();
ConfigurationManager.RefreshSection("connectionStrings");
conn.ConnectionString = ConfigurationManager.ConnectionStrings["Conn"].ToString();
//get data from a table to test the connection
SqlDataAdapter da = new SqlDataAdapter("select * from Users", conn);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
MessageBox.Show("Connection successful", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
MessageBox.Show("Connection failed", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
To get the connection string on runtime, use the following code
string cnstr = ConfigurationManager.ConnectionStrings["Conn"].ToString();
Now, test it!
Hope this helps!
Step 1: Create a SQL Conneciton form:
On the Solution Explorer panle, right click on your project, select Add->New Item. Select Window Form. Name it SLQConnection, then click OK. Design it as the figure bellow:
On the Solution Explorer, right click on your project, select Add->New Item. On the dialog appeared, click on Application Configuration File, name it app.config. Click OK
Open app.config file, modifying it as the figure bellow
On the SQLConnection.cs file, add the following code:
public void updateConfigFile(string con)
{
XmlDocument XmlDoc = new XmlDocument();
//Road the Config file
XmlDoc.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
foreach (XmlElement xElement in XmlDoc.DocumentElement)
{
if (xElement.Name == "connectionStrings")
{
//setting the connection string
xElement.FirstChild.Attributes[2].Value = con;
}
}
//write the connection string in app.config file
XmlDoc.Save(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
}
Double click on Connect button of the Form created in step 1
Add the following code to the On_Clikc event of the button
StringBuilder Con = new StringBuilder("Data Source=");
Con.Append(txtServerName.Text);
Con.Append(";Initial Catalog=");
Con.Append(txtDBName.Text);
Con.Append(";Integrated Security=SSPI;");
string strCon = Con.ToString();
updateConfigFile(strCon);
//create a new connection
SqlConnection conn = new SqlConnection();
ConfigurationManager.RefreshSection("connectionStrings");
conn.ConnectionString = ConfigurationManager.ConnectionStrings["Conn"].ToString();
//get data from a table to test the connection
SqlDataAdapter da = new SqlDataAdapter("select * from Users", conn);
DataTable dt = new DataTable();
da.Fill(dt);
if (dt.Rows.Count > 0)
{
MessageBox.Show("Connection successful", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
else
{
MessageBox.Show("Connection failed", "Notice", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
To get the connection string on runtime, use the following code
string cnstr = ConfigurationManager.ConnectionStrings["Conn"].ToString();
Now, test it!
Hope this helps!
