none
How to change connectionstring in app.config dynamically RRS feed

  • Question

  •  in my windows applications after changing the app.config connectionstring at runtime then after how to get the database names from sql server www.tutorialshelper.com



    Saturday, October 19, 2019 5:42 AM

All replies

  • Hello,

    First off in the future please ask one question at a time that are related as this time you have asked two different topics, thanks for understanding.

    All that follows was done in Visual Studio 2019, will work with Visual Studio 2015 and Visual Studio 2019.

    Question 1: Change a connection string at runtime.

    First step is to add a reference to your project for System.Configuration. The following code targets a single connection named SqlServer1.

    In app.config

    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <configSections>
        </configSections>
        <connectionStrings>
            <add name="WindowsFormsApp1.Properties.Settings.NorthWind" 
                 connectionString="Data Source=KARENS-PC;Initial Catalog=NorthWindAzure;Integrated Security=True" />
            <add name="WindowsFormsApp1.Properties.Settings.SqlServer1" 
                 connectionString="Data Source=KARENS-PC;Initial Catalog=NorthWindAzureForInserts;Integrated Security=True" />
        </connectionStrings>
        <startup> 
            <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
        </startup>
    </configuration>

    Add the following class which contains a language extension, change the namespace to you namespace.

    using System.Configuration;
    using System.IO;
    using System.Windows.Forms;
    
    namespace WindowsFormsApp1
    {
        public static class Extension
        {
            /// <summary>
            /// Determine if a connection string exists
            /// </summary>
            /// <param name="value"></param>
            /// <param name="key">ConnectionString name</param>
            /// <returns>true if connection string exists, false if not found</returns>
            /// <remarks>
            /// Throws an exception if not found, we ignore this.
            /// </remarks>
            public static bool HasConnectionString(this ConnectionStringSettingsCollection value, string key)
            {
                try
                {
                    return value[$"{Path.GetFileNameWithoutExtension(Application.ExecutablePath)}.Properties.Settings.{key}"]
                               .ConnectionString.Length > 0;
                }
                catch
                {
                    return false;
                }
            }
        }
    }

    In a form, add a two Buttons and a TextBox (names are shown below for this demo)

    First button shows simply how to a) check if the connection string exists and if so show it.

    private void ViewConnectionStringButton_Click(object sender, EventArgs e)
    {
        var connectionStringName = "SqlServer1";
    
        if (!ConfigurationManager.ConnectionStrings.HasConnectionString(connectionStringName))
        {
            MessageBox.Show("Connection string does not exists");
            return;
        }
    
        Console.WriteLine();
        // This variable represents the connection string to change
        var connectionName = $"{Path.GetFileNameWithoutExtension(Application.ExecutablePath)}.Properties.Settings.{connectionStringName}";
    
        var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
        //Get all connection strings
        var connectionStringsSection = (ConnectionStringsSection)config.GetSection("connectionStrings");
        // Show connection string we are interested in
        Console.WriteLine(connectionStringsSection.ConnectionStrings[connectionName].ConnectionString);
    
    }

    Second button shows how to change the connection string from a value in a TextBox.

    private void ChangeConnectionStringButton_Click(object sender, EventArgs e)
    {
        var connectionStringName = "SqlServer1"; 
    
        if (string.IsNullOrWhiteSpace(ServerNameTextBox.Text))
        {
            MessageBox.Show("Need a new server name");
            return;
        }
    
        if (!ConfigurationManager.ConnectionStrings.HasConnectionString(connectionStringName))
        {
            MessageBox.Show("Connection string does not exists");
            return;
        }
        Console.WriteLine();
        // This variable represents the connection string to change
        var connectionName = $"{Path.GetFileNameWithoutExtension(Application.ExecutablePath)}.Properties.Settings.{connectionStringName}";
    
        var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
        //Get all connection strings
        var connectionStringsSection = (ConnectionStringsSection)config.GetSection("connectionStrings");
        // Show connection string we are interested in
        Console.WriteLine(connectionStringsSection.ConnectionStrings[connectionName].ConnectionString);
    
        // Change the connection string, in this case just the [Data Source]
        connectionStringsSection.ConnectionStrings[connectionName].ConnectionString = 
            $"Data Source={ServerNameTextBox.Text};Initial Catalog=blah;UID=blah;password=blah";
    
        // save
        config.Save();
    
        // Important, needs to be done to recognize the change
        ConfigurationManager.RefreshSection("connectionStrings");
        
    }

    Question 2: Get database names on a server

    Add the following class to your project. Change the server name which is currently KARENS-PC to the name of your server e.g. if SQL-Server Express .\\SQLEXPRESS for example.

    using System.Collections.Generic;
    using System.Data.SqlClient;
    
    namespace WindowsFormsApp1
    {
        public class SqlServerOperations
        {
            public List<string> DatabaseNames()
            {
                var connectionString = "Data Source=KARENS-PC;Initial Catalog=master;Integrated Security=True";
                var nameList = new List<string>();
                
                using (var cn = new SqlConnection {ConnectionString = connectionString})
                {
                    using (var cmd = new SqlCommand {Connection = cn})
                    {
                        var selectStatement = 
                            "SELECT [name]  " + 
                            "FROM master.sys.databases " + 
                            "WHERE state <> 6 AND database_id > 4 AND HAS_DBACCESS([name]) = 1 " + 
                            "ORDER BY name";
    
                        cmd.CommandText = selectStatement;
    
                        cn.Open();
    
                        var reader = cmd.ExecuteReader();
    
                        while (reader.Read())
                        {
                            nameList.Add(reader.GetString(0));
                        }
    
                    }
                }
    
                return nameList;
            }
        }
    }
    

    On a form, add a Button and a ListBox, add the following.

    private void GetDatabaseNamesButton_Click(object sender, EventArgs e)
    {
        var ops = new SqlServerOperations();
        listBox1.DataSource = ops.DatabaseNames();
    }

    The above provides the answers you are looking for.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Saturday, October 19, 2019 11:02 AM
    Moderator
  • thanks.
    Friday, November 1, 2019 6:22 PM