Configuring a Connection String in the App.Config File During Runtime in C#

• Download source code - 26 KB

Introduction

This document covers how to configure a connection string at runtime for a Windows application using C#. This will help the user to switch connections during runtime. Also if there are multiple servers and databases, the number of connection strings will be high and the user cannot add everything in the App.config file. This will be helpful for users who might use a random server and database during runtime and there is no need to create a connection string beforehand.

Steps to Configure

1. Open Visual Studio and create a new Windows application. (Path : Start -> All Programs -> Microsoft Visual Studio 2010-> Microsoft Visual Studio 2010)

   

2. Add the App.config file to the project.

   

3. Add the below 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 namespaces area and write the below 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",Db);

    SqlDataAdapter da = new SqlDataAdapter("select * from employee");//incase earlier Visualstudios
       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 namespaces section. 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 dropdown is populated correctly. Change database name and click Connect again and verify the new database is connected.

   

   

Conclusion

By using the above code, the user can switch over to any server and database at runtime instead of editing the app.config file each time for adding a new connection string.