Introduction
This document explains how to configure a connection string at runtime for a Windows application using C#. This will help the user to switch connections during run time. Also if there are multiple servers and databases, the number of connection strings will be high and a user cannot add everything to the App.config file. This will be helpful for users to use any server and database during run time without the need to create any connection string before execution.
Steps to configure
1. Open Visual Studio and create new a Windows application. (Path : Start -> All Programs -> Microsoft Visual Studio 2005-> Microsoft Visual Studio 2005)
2. Add App.config file to the project.
3. Add the following code in the connection string.
<configuration>
<connectionStrings>
<add name="con" providerName="System.Data.sqlclient" connectionString="" />
</connectionStrings>
</configuration>
4. Now add two text boxes, two labels and a button in the form as shown below.
5. Add a dropdown below to populate a column from a table as shown below.
6. Add "using System.Data.SqlClient;" in the namespace and write the following code in the buttonclick event.
try
{
//Constructing connection string from the inputs
StringBuilder Con = new StringBuilder("Data Source=");
Con.Append(txtServer.Text);
Con.Append(";Initial Catalog=");
Con.Append(txtDatabase.Text);
Con.Append(";Integrated Security=SSPI;");
string strCon = Con.ToString();
updateConfigFile(strCon);
//Create new sql connection
SqlConnection Db = new SqlConnection();
//to refresh connection string each time else it will use previous connection string
ConfigurationManager.RefreshSection("connectionStrings");
Db.ConnectionString = ConfigurationManager.ConnectionStrings["con"].ToString();
//To check new connection string is working or not
SqlDataAdapter da = new SqlDataAdapter("select * from employee");
//In case of VS2010 comment the above line and use the below line
//SqlDataAdapter da = new SqlDataAdapter("select * from employee",Db);
DataTable dt = new DataTable();
da.Fill(dt);
cmbTestValue.DataSource=dt;
cmbTestValue.DisplayMember="Emp_Id";
}
catch (Exception E)
{
MessageBox.Show(ConfigurationManager.ConnectionStrings["con"].ToString() + ".This is invalid connection", "Incorrect server/Database");
}
7. Add "using System.Xml; using System.Configuration;" in the name space. Write a new function updateConfigFile with the following code.
public void updateConfigFile(string con)
{
//updating config file
XmlDocument XmlDoc = new XmlDocument();
//Loading the Config file
XmlDoc.Load(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
foreach (XmlElement xElement in XmlDoc.DocumentElement)
{
if (xElement.Name == "connectionStrings")
{
//setting the coonection string
xElement.FirstChild.Attributes[2].Value = con;
}
}
//writing the connection string in config file
XmlDoc.Save(AppDomain.CurrentDomain.SetupInformation.ConfigurationFile);
}
8. Now build the solution and enter the server name and database. Check that the dropdown is populated correctly. Change database name and click connect again and verify that the new database is connected.
Conclusion
By using the above code, a user can switch over to any server and database at runtime instead of editing app.config file each time for adding a new connection string.