How to change connection string in App.config dynamically in C#

By FoxLearn 7/16/2024 8:53:48 AM   33.55K
To change the ConnectionString at runtime in a C# Windows Forms application using App.config, you typically follow these steps.

Open your Visual Studio, then click New Project, then select Visual C# on the left, then Windows and then select Windows Forms Application. Name your project "DynamicallyConnectionString" and then click OK

Make sure your project already has an App.config file. If not, you can add it by right-clicking on your project in Solution Explorer, selecting "Add" -> "New Item...", then choosing "Application Configuration File".

Open the app.config file, then define your ConnectionString under the <configuration> section as shown below.

<configuration>
  <connectionStrings>
    <add name="cn" connectionString="Data Source=.;Initial Catalog=Northwind;User ID=sa;Password=123@qaz?;" providerName="System.Data.SqlClient"/>
  </connectionStrings>
</configuration>

Drag and drop the Label, TextBox, Button controls from your Visual toolbox onto your form desinger, then design a simple form as shown below.

connection string in c#

Create an AppSetting class to get the connection string in the app.config file

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace DynamicallyConnectionString
{
    public class AppSetting
    {
        Configuration config;
 
        public AppSetting()
        {
            // Assuming you want to change the "MyConnectionString" connection string
            config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
        }
 
        //Get connection string from App.Config file
        public string GetConnectionString(string key)
        {
            return config.ConnectionStrings.ConnectionStrings[key].ConnectionString;
        }
 
        //Save connection string to App.config file
        public void SaveConnectionString(string key,string value)
        {
            // Modify the connection string
            config.ConnectionStrings.ConnectionStrings[key].ConnectionString = value;
            config.ConnectionStrings.ConnectionStrings[key].ProviderName = "System.Data.SqlClient";
            // Save the configuration
            config.Save(ConfigurationSaveMode.Modified);
        }
    }
}

You can create a SqlHelper class to check connection to sql server

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
 
namespace DynamicallyConnectionString
{
    public class SqlHelper
    {
        SqlConnection cn;
        public SqlHelper(string connectionString)
        {
            cn = new SqlConnection(connectionString);
        }
 
        public bool IsConnection
        {
            get
            {
                if (cn.State == System.Data.ConnectionState.Closed)
                    cn.Open();
                return true;
            }
        }
    }
}

Add a Form_Load event handler allows you to set data to the combobox as shown below.

private void Form1_Load(object sender, EventArgs e)
{
    //Add server name to combobox
    cboServer.Items.Add(".");
    cboServer.Items.Add("(local)");
    cboServer.Items.Add(@".\SQLEXPRESS");
    cboServer.Items.Add(string.Format(@"{0}\SQLEXPRESS", Environment.MachineName));
    cboServer.SelectedIndex = 3;
}

Add a click event handler to the Connect button allows you to test the connection to the SQL Server.

// c# test connection to sql server
private void btnConnect_Click(object sender, EventArgs e)
{
    string connectionString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};", cboServer.Text, txtDatabase.Text, txtUsername.Text, txtPassword.Text);
    try
    {
        SqlHelper helper = new SqlHelper(connectionString);
        if (helper.IsConnection)
            MessageBox.Show("Test connection succeeded.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Add a click event handler to the Save button allows you to save the sql connection to the app.config file.

private void btnSave_Click(object sender, EventArgs e)
{
    //Set connection string
    string connectionString = string.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3};", cboServer.Text, txtDatabase.Text, txtUsername.Text, txtPassword.Text);
    try
    {
        SqlHelper helper = new SqlHelper(connectionString);
        if (helper.IsConnection)
        {
            AppSetting setting = new AppSetting();
            setting.SaveConnectionString("cn", connectionString);
            MessageBox.Show("Your connection string has been successfully saved.", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message, "Message", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
}

Ensure that your application has write permissions to the App.config file and its directory if you plan to modify it at runtime.

By following these steps, you can easily change the ConnectionString at runtime using App.config in a C# Windows Forms application.

VIDEO TUTORIAL

Related