change the connection string values the app.config

Question

I am trying to change the connection String values

connectionString="Data Source=localhost;Initial Catalog=Tracker;Persist Security Info=false;User ID=sa;Password=p@ssw0rd"

from my user interface. Could any one help to change the settings from the user interface in the windows application?

Answer 1

From the comment thread on the original post, it sounds like the OP needs to enumerate data sources and allow a user to pick one (and perhaps store that preference). Assuming that is the case...

• The actual connection to the database should be secured using integrated Windows security if at all possible. This a best practice. Integrated security eliminates the need to store credentials anywhere.

• If the goal is to pick a datasource, it's not hard to list all databases within an environment using C#.

• The user's preference/selection should be stored somewhere other than the app.config. The registry, isolated storage, or an XML file are all valid options if no credentials are involved.

Answer 2

ASP.NET provides the Web Site Administration Tool to view and manage the configuration settings for your ASP.NET website. These configuration settings are stored in an xml file called web.config.

web.config is an application configuration file used to define configuration settings such as connecting strings, authentication, authorization etc., for an ASP.NET application

The tool provides an easy to use interface to edit the configuration settings. However this tool works well when you have to manually make changes. At times, there could be a possibility where we need to make changes to the web.config at runtime. For example: a user requesting to control the connecting string, change session timeouts and so on. For this purpose, ASP.NET provides the Configuration API to programmatically manipulate the configuration settings in web.config. The API’s are contained in the System.Configuration and System.Web.Configuration namespaces. In fact, internally the Web Site Administration Tool uses the same API to edit configuration settings. The WebConfigurationManager class in the System.Web.Configuration namespace is used to create a Configuration object. This object is used to read and write changes to the web.config file. See.

See also Securing Connection Strings

Let me clarify that this is not what you're asking but by reading this, you will be able to find some clues and come to know what is recommended and what is not.

Answer 3

If this is a Windows application then you don't need to change the app.config file to change the active database connection. I've written about this on my blog.

Answer 4

User below method to change connection string using C# :

public void SaveConnectionString(DbInfo dbinfo, string path,string appConfigFile)
    {
        try
        {
            string configFile = Path.Combine(path, appConfigFile);
            var doc = new XmlDocument();
            doc.Load(configFile);
            XmlNodeList endpoints = doc.GetElementsByTagName("connectionStrings");
            foreach (XmlNode item in endpoints)
            {
                if (item.HasChildNodes)
                {
                    foreach (var c in item.ChildNodes)
                    {
                        if (((XmlNode)c).NodeType == XmlNodeType.Element)
                        {


                            var adressAttribute = ((XmlNode)c).Attributes["name"];
                            if (adressAttribute.Value.Contains("YourConStrName"))
                            {
                                if (dbinfo.dbType == dataBaseType.Embedded)
                                {
                                    ((XmlNode)c).Attributes["connectionString"].Value = SetupConstants.DbEmbededConnectionString;
                                    ((XmlNode)c).Attributes["providerName"].Value = SetupConstants.DbEmbededConnectionProvider;
                                }
                                else if (dbinfo.dbType == dataBaseType.Microsoft_SQL_Server)
                                {
                                    if (dbinfo.sqlServerAuthType == SqlServerAuthenticationType.SQL_Server_Authentication)
                                    {
                                       // ((XmlNode)c).Attributes["connectionString"].Value = string.Format(SetupConstants.dbConnStringwithDb, dbinfo.databaseAdress, SetupConstants.SqlDbName, dbinfo.userId, dbinfo.password) + "MultipleActiveResultSets=true;";
                                        ((XmlNode)c).Attributes["connectionString"].Value = string.Format(SetupConstants.dbConnStringwithDb, dbinfo.databaseAdress, dbinfo.DatabaseName, dbinfo.userId, dbinfo.password) + "MultipleActiveResultSets=true;";

                                    }
                                    else if (dbinfo.sqlServerAuthType == SqlServerAuthenticationType.Windows_Authentication)
                                    {
                                        //((XmlNode)c).Attributes["connectionString"].Value = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;MultipleActiveResultSets=true;", dbinfo.databaseAdress, SetupConstants.SqlDbName);
                                        ((XmlNode)c).Attributes["connectionString"].Value = string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;MultipleActiveResultSets=true;", dbinfo.databaseAdress, dbinfo.DatabaseName);
                                    }
                                    ((XmlNode)c).Attributes["providerName"].Value = SetupConstants.DbSqlConnectionProvider;
                                }
                            }
                        }
                    }
                }

            }
            doc.Save(configFile);
            string exePath = Path.Combine(path, "EDC.Service.exe");
            InstallerHelper.EncryptConnectionString(true, exePath);
        }
        catch (Exception ex)
        {
            //TODO://log here exception
            Helper.WriteLog(ex.Message + "\n" + ex.StackTrace);
            throw;
        }
    }

Add bellow class DBinfo

public class DbInfo
{
    public DataBaseType dbType { get; set; }
    public SqlServerAuthenticationType sqlServerAuthType { get; set; }
    public string ConnectionString { get; set; }
    public string databaseAdress { get; set; }
    public string userId { get; set; }
    public string password { get; set; }
    public string Port { get; set; }
    public string DatabaseName { get; set; }
}

public enum DataBaseType
{
    Unknown = 0,
    Embedded = 1,
    Microsoft_SQL_Server =2,
}

public enum SqlServerAuthenticationType
{
    Windows_Authentication = 0 ,
    SQL_Server_Authentication =1
}