Thursday, February 14, 2013

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);
        }
    }
}