Social Icons

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







1 comment:

  1. Hi, Great.. Tutorial is just awesome..It is really helpful for a newbie like me.. I am a regular follower of your blog. Really very informative post you shared here. Kindly keep blogging. If anyone wants to become a .Net developer learn from Dot Net Training in Chennai. or learn thru ASP.NET Essential Training Online . Nowadays Dot Net has tons of job opportunities on various vertical industry.
    or Javascript Training in Chennai. Nowadays JavaScript has tons of job opportunities on various vertical industry.

    ReplyDelete

 

Sample text

Sample Text