Modifying Database Connection information Dynamically from the Application at run time

Configuration File: Below is the Configuration File

xml version="1.0"?>

<configuration>

  <configSections>

  </configSections>

    <startup useLegacyV2RuntimeActivationPolicy="true">

      <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/>

    </startup>

  <appSettings>

    <add key="DBAuthenticationType" value="SQLServerAuthentication"/>

    <add key="DbSchemaName" value="HMGT"/>

    <add key="DbServerName" value="KP107\SQLEXPRESS"/>

    <add key="DbUserId" value="sa"/>

    <add key="DbUserPassword" value="testpass"/>

   

    <add key="ChildWindowWidth" value="15" />

    <add key="ChildWindowHeight" value="175" />

   

  </appSettings>

</configuration>

Application Screen: Below is the Configuration File

Code Behind File...

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlClient;

using System.Configuration;

using System.Xml;

using System.Xml.Linq;

namespace ManagementSystem

{

    public partial class frmDataBaseSetting : Form

    {

        public frmDataBaseSetting()

        {

            InitializeComponent();

        }

        static string constr = "";

        static SqlConnection sqlcon = new SqlConnection();

        static SqlTransaction SqlTran;

        public bool SavePressed = false;

        ///

        /// Authentication Enumneration

        ///

        public enum EnumAuthenticationType

        {

            WindowsAuthentication = 1,

            SQLServerAuthentication = 2

        }

        ///

        /// get the connection String Sql Authentication

        ///

        private string connectionStringSqlAuth

        {

            get

            {

                return "user id=" + txtDBLoginId.Text +

                ";password=" + txtDBLoginPassword.Text +

                ";data source=" + txtServerName.Text +

                ";Initial Catalog=" + txtDBName.Text;

            }

        }

        ///

        /// get the connection String Windows Suthentication

        ///

        private string connectionStringWindowAuth

        {

            get

            {

                return "data source=" + txtServerName.Text +

                ";Initial Catalog=" + txtDBName.Text +

                ";Integrated Security=True";

            }

        }

        ///

        /// Method for when Test Connection Button will Press

        ///

        ///

        ///

        private void btnTestConnection_Click(object sender, EventArgs e)

        {

            if (txtServerName.Text.Equals(""))

            {

                MessageBox.Show("Enter Server Name!", rsCommon.CompanyName,                                                    MessageBoxButtons.OK, MessageBoxIcon.Error);

                return ;

            }

            if (txtDBName.Text.Equals(""))

            {

                MessageBox.Show("Enter DataBase Name!",rsCommon.CompanyName,                                                   MessageBoxButtons.OK, MessageBoxIcon.Error);

                return ;

            }

            if (cmbAuthenticationType.Text.Equals(""))

            {

                MessageBox.Show("Select Authentication Type!", rsCommon.CompanyName, 
                                 MessageBoxButtons.OK, MessageBoxIcon.Error);

                return ;

            }

            if (cmbAuthenticationType.Text.Equals(
                      EnumAuthenticationType.SQLServerAuthentication.ToString()))

            {

                if (txtDBLoginId.Text.Equals(""))

                {

                    MessageBox.Show("Enter DataBase Login Id!", rsCommon.CompanyName, 
                                     MessageBoxButtons.OK, MessageBoxIcon.Error);

                    return;

                }

                if (txtDBLoginPassword.Text.Equals(""))

                {

                    MessageBox.Show("Enter DataBase Login Password!", rsCommon.CompanyName,
                                    MessageBoxButtons.OK, MessageBoxIcon.Error);

                    return;

                }

            }

            String ConnectionMasg = "";

            if (IsvalidConnection(out ConnectionMasg))

            {

                if (!SavePressed)

                {

                    MessageBox.Show(ConnectionMasg, rsCommon.CompanyName, 
                                    MessageBoxButtons.OK, MessageBoxIcon.Exclamation);

                }

                else

                {

                  

                    SavePressed = false;

                  

                    string appPath = 
                            System.IO.Path.GetDirectoryName(
                                 System.Reflection.Assembly.GetExecutingAssembly().Location);

                    string configFile = System.IO.Path.Combine(appPath, 
                                                          "ManagementSystem.exe.config");

                    ExeConfigurationFileMap configFileMap = new ExeConfigurationFileMap();

                    configFileMap.ExeConfigFilename = configFile;

                    System.Configuration.Configuration config = 
                              ConfigurationManager.OpenMappedExeConfiguration(configFileMap, 
                                     ConfigurationUserLevel.None);

                    config.AppSettings.Settings.Remove("DbServerName"); 
                    config.AppSettings.Settings.Add("DbServerName",
                                                             txtServerName.Text.Trim());

                    config.AppSettings.Settings.Remove("DbSchemaName"); 
                    config.AppSettings.Settings.Add("DbSchemaName", txtDBName.Text.Trim());

                    config.AppSettings.Settings.Remove("DBAuthenticationType"); 
                    config.AppSettings.Settings.Add("DBAuthenticationType",  
                                                cmbAuthenticationType.Text.Trim());

                    config.AppSettings.Settings.Remove("DbUserId"); 
                    config.AppSettings.Settings.Add("DbUserId", txtDBLoginId.Text.Trim());

                    config.AppSettings.Settings.Remove("DbUserPassword"); 
                    config.AppSettings.Settings.Add("DbUserPassword",  
                                                txtDBLoginPassword.Text.Trim());

                    config.Save();

                    ConfigurationManager.RefreshSection("appSettings");

                    MessageBox.Show("Database Setting information has been updated 
                                     successfully", rsCommon.CompanyName, 
                                     MessageBoxButtons.OK, MessageBoxIcon.Exclamation);

                }

            }

            else

            {

                MessageBox.Show("Connection Not Succeed : " + ConnectionMasg, 
                       rsCommon.CompanyName, MessageBoxButtons.OK, MessageBoxIcon.Error);

            }

           

        }

      

        public bool IsvalidConnection(out String strMsg)

        {

            if (sqlcon.State == ConnectionState.Open)

            {

                sqlcon.Close();

            }

            try

            {

                String AuthenticationType = cmbAuthenticationType.Text;

                if(AuthenticationType.Equals(
                               EnumAuthenticationType.WindowsAuthentication.ToString()))

                {

                    constr = connectionStringWindowAuth;

                }

                else if (AuthenticationType.Equals( 
                               EnumAuthenticationType.SQLServerAuthentication.ToString()))

                {

                    constr = connectionStringSqlAuth;

                }

                else

                {

                    constr = connectionStringSqlAuth;

                }

                sqlcon = new SqlConnection(constr);

                sqlcon.Open();

                if (sqlcon.State == ConnectionState.Open)

                {

                    sqlcon.Close();

                }

                strMsg = "Test Connection Succeed";

                return true;

            }

            catch (Exception ex)

            {

                strMsg = ex.Message;

                return false;

            }

            finally

            {

                if (sqlcon.State == ConnectionState.Open)

                {

                    sqlcon.Close();

                }

            }

        }

        private void frmDataBaseSetting_Load(object sender, EventArgs e)

        {

            try

            {

                txtServerName.Text = 
                                 ConfigurationManager.AppSettings["DbServerName"].ToString();

                txtDBName.Text = ConfigurationManager.AppSettings["DbSchemaName"].ToString();

                cmbAuthenticationType.Text = 
                         ConfigurationManager.AppSettings["DBAuthenticationType"].ToString();

                txtDBLoginId.Text = ConfigurationManager.AppSettings["DbUserId"].ToString();

                txtDBLoginPassword.Text =
                ConfigurationManager.AppSettings["DbUserPassword"].ToString();

                if (ConfigurationManager.
                             AppSettings["DBAuthenticationType"].ToString().
                             Equals(EnumAuthenticationType.WindowsAuthentication.ToString()))

                {

                    txtDBLoginId.ReadOnly = true;

                    txtDBLoginPassword.ReadOnly = true;

                }

                else if (ConfigurationManager.
                           AppSettings["DBAuthenticationType"].ToString().
                           Equals(EnumAuthenticationType.SQLServerAuthentication.ToString()))

                {

                    txtDBLoginId.ReadOnly = false;

                    txtDBLoginPassword.ReadOnly = false;

                }

            }

            catch (Exception ex)

            {

            }

        }

        ///

        /// Changing Athuentication Type

        ///

        ///

        ///

        private void cmbAuthenticationType_SelectedIndexChanged(object sender, EventArgs e)

        {

            if(cmbAuthenticationType.Text.
                  Equals(EnumAuthenticationType.WindowsAuthentication.ToString()))

            {

                txtDBLoginId.ReadOnly = true;

                txtDBLoginPassword.ReadOnly = true;

            }

            else if(cmbAuthenticationType.Text.
                  Equals(EnumAuthenticationType.SQLServerAuthentication.ToString()))

            {

                txtDBLoginId.ReadOnly = false;

                txtDBLoginPassword.ReadOnly = false;

            }

        }

        ///

        /// Save button Event

        ///

        ///

        ///

        private void btnSave_Click(object sender, EventArgs e)

        {

            SavePressed = true;

            btnTestConnection_Click(null,null);

        }

    }

}