Tuesday, July 17

Insert Data into database Using Jquery and Web services in N-Tier pattern

You can Like also my previous two links 


Select,Insert and Update data  using JSON, ASP.NET Web services and jQuery

I am going to show you how to insert data using jquery in  N-tier architecture pattern.
You can follow your own pattern,But here i am putting some basic steps of my pattern.
Step 1: 
My Design View:-

i am taking one user control and one page to call that user control
My Source Code of My user control(UHRMS_EmployeeExit.ascx):-

<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="UHRMS_EmployeeExit.ascx.cs"
    Inherits="Website.App_WebControls.UHRMS_UserControls.UHRMS_EmployeeExit" %>
<%@ Register Assembly="AjaxControlToolkit" Namespace="AjaxControlToolkit" TagPrefix="asp" %>

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4/jquery.min.js" type="text/javascript"></script>
<script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8/jquery-ui.min.js" type="text/javascript"></script>
<style type="text/css">
    .MainDiv1
    {
        width: 100%;
        background-color: #F5F6F7;
        overflow: auto;
        height: 100%;
    }
    .Internaldiv
    {
        width: 98%;
        height: auto;
        float: left;
        margin: auto;
        margin-top: 6px;
    }
    .Lable
    {
        width: 30%;
        height: 18px;
        text-align: right;
        font-family: calibri;
        font-size: 12px;
        font-style: normal;
        text-decoration: none;
        font-weight: normal;
        color: #333333;
        float: left;
        padding-right: 5px;
        vertical-align: middle;
    }
    .Text_Long
    {
        width: 44.8%;
        float: left;
        height: 18px;
        display: block;
    }
    .Text_Long input
    {
        width: 70%;
        padding-left: 2px;
        font-size: 12px;
        font-family: calibri;
    }
    .Text_Long select
    {
        width: 70%;
        padding-left: 2px;
        font-size: 12px;
        font-family: calibri;
    }
    .Text_Long span
    {
        text-align: right;
        font-family: calibri;
        font-size: 12px;
        font-style: normal;
        text-decoration: none;
        font-weight: normal;
        color: #333333;
        padding-left: 5px;
    }
    .heading
    {
        margin-top: 10px;
        width: 95%;
        margin-left: 30px;
        margin-right: 14px;
        background-color: Teal;
        font-family: Calibri;
        font-size: 15px;
        font-weight: bold;
        color: White;
        height: 20px;
        vertical-align: middle;
        padding-left: 15px;
        float: left;
    }
    .text_short
    {
        float: left;
        width: 20%;
        height: 18px;
    }
    .text_short input
    {
        color: #333333;
        width: 70%;
        padding-left: 2px;
        font-size: 12px;
        font-family: calibri;
    }
    .text_short a
    {
        height: 10px;
        width: 70%;
        padding-left: 2px;
        font-size: 12px;
        font-family: calibri;
    }
    .text_short select
    {
        height: 18px;
        color: #333333;
        width: 90%;
        padding-left: 2px;
        font-size: 12px;
        font-family: calibri;
    }
    .text_short_datalist
    {
        height: 18px;
        width: 70%;
        display: block;
        margin-left: 1px;
        margin-top: 1px;
        margin-bottom: 5px;
        background-color: White;
        border: 1px solid Gray;
        float: left;
    }
    .lable_short
    {
        width: 20%;
        height: 18px;
        text-align: right;
        font-family: calibri;
        font-size: 12px;
        font-style: normal;
        text-decoration: none;
        font-weight: normal;
        color: #333333;
        float: left;
        padding-right: 5px;
    }
    .ModalBackgroundCSS
    {
        width: 100%;
        background-color: black;
        moz-opacity: 0.5;
        khtml-opacity: .5;
        opacity: .5;
        filter: alpha(opacity=50);
        z-index: 120;
        height: 100%;
        position: absolute;
        top: 0;
        left: 0;
    }
    .button_area
    {
        width: 100%;
        float: left;
        margin-top: 30px;
        text-align: center;
    }
    .heading_
    {
        width: 95%;
        margin-top: 10px;
        font-family: Calibri;
        font-size: 12px;
        font-weight: bold;
        height: auto;
        vertical-align: middle;
        padding-left: 15px;
        float: left;
        border: 1px Solid Teal;
        color: Teal;
    }
    .Internaldiv1
    {
        width: 98%;
        height: auto;
        float: left;
        margin: auto;
        margin-top: 9px;
        margin-bottom: 9px;
    }
    .Lable_
    {
        width: 26%;
        height: 18px;
        text-align: right;
        font-family: calibri;
        font-size: 12px;
        font-style: normal;
        text-decoration: none;
        font-weight: normal;
        color: #333333;
        float: left;
        padding-right: 5px;
        vertical-align: middle;
    }
    .Text_Long_
    {
        width: 72%;
        float: left;
        height: 18px;
        display: block;
    }
    .Text_Long_Multi
    {
        height: 60px;
        display: block;
        width: 64.8%;
        float: left;
    }
</style>
<script type="text/javascript" src="http://code.jquery.com/jquery-latest.min.js"></script>
<script src="../../Scripts/jquery-1.7.1.min.js" type="text/javascript"></script>
<script type="text/javascript">
function GetQueryStringParams(sParam)

{

          
    var sPageURL = window.location.search.substring(1);

    var sURLVariables = sPageURL.split('&');

    for (var i = 0; i < sURLVariables.length; i++)

    {

        var sParameterName = sURLVariables[i].split('=');

        if (sParameterName[0] == sParam)
{
return sParameterName[1];
}
}
}​





    function CallService() {
 
    }


</script>

<script type="text/javascript" language="javascript">
    $(document).ready(function () {

        $('#<%= BtnSave.ClientID %>').click(function (e) {



            // Creating variables to hold data from textboxes
            var Employee_ID = $('#<%=ddlEmpName.ClientID %>').val();

            var Date_of_Resignation = $('#<%= txtIntimationDate.ClientID %>').val();

            var Last_Working_Date = $('#<%=txtLastWorkingDate.ClientID %>').val();

            var Notice_Period = $('#<%=txtNoticePeriod.ClientID %>').val();

            var Reason_for_Resignation = $('#<%=ddlResignationReason.ClientID %>').val();
            var Comments = $('#<%=txtComment.ClientID %>').val();
            if ($('#<%=RdBtnLstReHirable.ClientID %> input[type=radio]:checked').val() == "1") {
                var Is_Rehireable = 1;
            }
            else {
                var Is_Rehireable = 0;
            }


            var Supervisor_Comment = $('#<%=txtSupervisorComment.ClientID %>').val();
            if ($('#<%=chkcontinueProcess.ClientID %>').attr('checked', 'checked')) {
                var Continue_With_Interview_Process = true;
            }
            else {
                var Continue_With_Interview_Process = false;
            }

            if ($('#<%=ChkBoxAcceptAgrrements.ClientID %>').attr('checked', 'checked')) {
                var Accepted_Agreement = true;
            }
            else {
                var Accepted_Agreement = false;
            }


            var Created_By = 3;
            var Is_Active = true;
            var Is_Deleted = false;

            $.ajax({

                type: "POST",
                url: "../../App_WebServices/UHRMS_EmployeeExit.asmx/ReceiveWebService",
                data: "{ 'Employee_ID': '" + Employee_ID + "','Date_of_Resignation': '" + Date_of_Resignation + "', 'Last_Working_Date': '" + Last_Working_Date + "','Notice_Period':'" + Notice_Period + "', 'Reason_for_Resignation': '" + Reason_for_Resignation + "', 'Comments': '" + Comments + "', 'Is_Rehireable': '" + Is_Rehireable + "', 'Supervisor_Comment': '" + Supervisor_Comment + "', 'Continue_With_Interview_Process': '" + Continue_With_Interview_Process + "', 'Accepted_Agreement': '" + Accepted_Agreement + "', 'Created_By': '" + Created_By + "', 'Is_Active': '" + Is_Active + "', 'Is_Deleted': '" + Is_Deleted + "'}",
                contentType: "application/json",
                async: false,
                success: function (data) {
                    alert(data.d);
                }
            });
           

        });
    });
</script>
<div id="MainDiv" class="MainDiv1">
    <fieldset runat="server" id="Fieldset1" class="heading_">
        <legend>Employee Exit </legend>
        <div class="Internaldiv1">
            <div class="Lable_">
                Branch :
            </div>
            <div class="Text_Long">
                <asp:DropDownList ID="ddlBranch" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlBranch_SelectedIndexChanged">
                </asp:DropDownList>
            </div>
        </div>
        <div class="Internaldiv1">
            <div class="Lable_">
                Department :
            </div>
            <div class="Text_Long">
                <asp:DropDownList ID="ddlDepartment" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlDepartment_SelectedIndexChanged">
                </asp:DropDownList>
            </div>
        </div>
        <div class="Internaldiv1">
            <div class="Lable_">
                Designation:
            </div>
            <div class="Text_Long">
                <asp:DropDownList ID="ddlDesignation" runat="server" OnSelectedIndexChanged="ddlDesignation_SelectedIndexChanged"
                    AutoPostBack="True">
                </asp:DropDownList>
            </div>
        </div>
        <div class="Internaldiv1">
            <div class="Lable_">
                Employee Name :
            </div>
            <div class="Text_Long">
                <asp:DropDownList ID="ddlEmpName" runat="server" OnSelectedIndexChanged="ddlEmpName_SelectedIndexChanged"
                    AutoPostBack="True">
                </asp:DropDownList>
            </div>
        </div>
        <div class="Internaldiv1">
            <div class="Lable_">
                Supervisor :
            </div>
            <div class="Text_Long">
                <asp:DropDownList ID="ddlSuperVisor" runat="server">
                    <asp:ListItem Value="0">Jyoti Prakash Mahapatra</asp:ListItem>
                    <asp:ListItem Value="1">Prajanuranjan Maharana</asp:ListItem>
                    <asp:ListItem Value="7">Shibashish Mohanty</asp:ListItem>
                </asp:DropDownList>
            </div>
        </div>
        <div class="Internaldiv">
            <div class="Lable_">
                &nbsp;Date of Resignation/Intimation :</div>
            <div class="text_short">
                <asp:TextBox ID="txtIntimationDate" runat="server" Style="width: 77%; float: left"></asp:TextBox>
                <asp:ImageButton ID="imgBtnIntiCal" runat="server" CausesValidation="false" ImageUrl="~/images/Calendar_scheduleHS.png"
                    Style="height: 20px; width: 20px; float: left;" />
                <asp:FilteredTextBoxExtender ID="FilterDeclartion" runat="server" FilterMode="ValidChars"
                    TargetControlID="txtIntimationDate" ValidChars="0123456789/">
                </asp:FilteredTextBoxExtender>
                <asp:CalendarExtender ID="CalIntiDate" runat="server" TargetControlID="txtIntimationDate"
                    PopupButtonID="imgBtnIntiCal" Format="dd/MM/yyyy">
                </asp:CalendarExtender>
            </div>
            <div class="lable_short">
                Last Working Date/Left Date :</div>
            <div class="text_short">
                <asp:TextBox ID="txtLastWorkingDate" runat="server" Style="width: 77%; float: left"></asp:TextBox>
                <asp:ImageButton ID="imgBtnlast" runat="server" CausesValidation="false" ImageUrl="~/images/Calendar_scheduleHS.png"
                    Style="height: 20px; width: 20px; float: left;" />
                <asp:FilteredTextBoxExtender ID="Filteredlast" runat="server" FilterMode="ValidChars"
                    TargetControlID="txtLastWorkingDate" ValidChars="0123456789/">
                </asp:FilteredTextBoxExtender>
                <asp:CalendarExtender ID="callast" runat="server" TargetControlID="txtLastWorkingDate"
                    PopupButtonID="imgBtnlast" Format="dd/MM/yyyy">
                </asp:CalendarExtender>
            </div>
        </div>
        <div class="Internaldiv1">
            <div class="Lable_">
                Notice Period(In Month) :
            </div>
            <div class="text_short">
                <asp:TextBox ID="txtNoticePeriod" ReadOnly="true" Text="2" runat="server"></asp:TextBox>
            </div>
        </div>
        <div class="Internaldiv1">
            <div class="Lable_">
                Reason for Resignation :
            </div>
            <div class="Text_Long">
                <asp:DropDownList ID="ddlResignationReason" runat="server">
                    <asp:ListItem>Select</asp:ListItem>
                    <asp:ListItem>Career Growth</asp:ListItem>
                    <asp:ListItem>Change in Career Path</asp:ListItem>
                    <asp:ListItem>Further Education</asp:ListItem>
                    <asp:ListItem>Re-Location</asp:ListItem>
                    <asp:ListItem>Health Reason</asp:ListItem>
                    <asp:ListItem>Personal Reason</asp:ListItem>
                    <asp:ListItem>Others</asp:ListItem>
                </asp:DropDownList>
            </div>
        </div>
        <div class="Internaldiv1">
            <div class="Lable_">
                Comments :
            </div>
            <div class="Text_Long_Multi">
                <asp:TextBox ID="txtComment" TextMode="MultiLine" runat="server" Height="56px" Width="330px"></asp:TextBox>
            </div>
        </div>
        <div class="Internaldiv1">
            <div class="Lable_">
                Is Re-hirable :
            </div>
            <div class="text_short input">
                <asp:RadioButtonList RepeatDirection="Horizontal" ID="RdBtnLstReHirable" runat="server"
                    Height="16px" Width="144px">
                    <asp:ListItem Value="1">Yes</asp:ListItem>
                    <asp:ListItem Value="0">No</asp:ListItem>
                </asp:RadioButtonList>
            </div>
        </div>
        <div class="Internaldiv1">
            <div class="Lable_">
                Supervisors Comments :
            </div>
            <div class="Text_Long_Multi">
                <asp:TextBox ID="txtSupervisorComment" TextMode="MultiLine" runat="server" Height="56px"
                    Width="330px"></asp:TextBox>
            </div>
        </div>
        <div class="Internaldiv1">
            <div class="Lable_">
                Continue with Interview Process :
            </div>
            <div class="text_short input">
                <asp:CheckBox ID="chkcontinueProcess" runat="server" />
            </div>
        </div>
        <div class="Internaldiv1">
            <div class="Lable_">
                Terms & Conditions :
            </div>
            <div class="Text_Long_Multi">
                <asp:TextBox ID="txtTermsAndCond" TextMode="MultiLine" runat="server" Height="56px"
                    Width="330px">This is a test Term And condition of ken cloud</asp:TextBox>
            </div>
        </div>
        <div class="Internaldiv1">
            <div class="Lable_">
                <asp:CheckBox ID="ChkBoxAcceptAgrrements" Text="&nbsp;" runat="server" />
            </div>
            <div class="text_short ">
                I Accept the Agreement.
            </div>
        </div>
         <div class="button_area">
             <asp:Button ID="BtnSave" runat="server" Text="Save" />
        </div>
    </fieldset>
</div>

 My Source Code of My Page(UHRMS_EmployeeExit.aspx):-

<%@ Page Title="" Language="C#" MasterPageFile="~/App_WebControls/App_MasterPages/DemoMaster.Master" AutoEventWireup="true" CodeBehind="UHRMS_EmployeeExit.aspx.cs" Inherits="Website.UERP.UHRMS.UHRMS_EmployeeExit" %>
<%@ Register src="../../App_WebControls/UHRMS_UserControls/UHRMS_EmployeeExit.ascx" tagname="UHRMS_EmployeeExit" tagprefix="uc1" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
<asp:UpdatePanel ID="gfhg" runat="server">
<ContentTemplate>
 <uc1:UHRMS_EmployeeExit ID="UHRMS_EmployeeExit1" runat="server" />
</ContentTemplate>
</asp:UpdatePanel>
  
</asp:Content>


Step 2:-
i am taking some basic layers as Abstract class which contains some common features as execute nonquery, execute scalar.
Object Layer,DataAccess layer,Intigration Layer,Cache Layer,Business layer,Presentation Layer. 

Step 3:-
My Object Layer(UHRMS_EmployeeExitObject.cs):-
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Swash.Objects
{
  public  class UHRMS_EmployeeExitObject
    {
      public string Operation
      {
          get;
          set;
      }
        public int Employee_Exit_ID
        {
            get;
            set;
        }
        public int Employee_ID
        {
            get;
            set;
        }
        public DateTime Date_of_Resignation
        {
            get;
            set;
        }
        public DateTime Last_Working_Date
        {
            get;
            set;
        }

        public int Notice_Period
        {
            get;
            set;
        }

        public string Reason_for_Resignation
        {
            get;
            set;
        }
        public string Comments
        {
            get;
            set;
        }
        public bool Is_Rehireable
        {
            get;
            set;
        }
        public string Supervisor_Comment
        {
            get;
            set;
        }
        public bool Continue_With_Interview_Process
        {
            get;
            set;
        }

        public bool Accepted_Agreement
        {
            get;
            set;
        }
        public DateTime Created_On
        {
            get;
            set;
        }
        public int Created_By
        {
            get;
            set;
        }
        public DateTime Modified_On
        {
            get;
            set;
        }
        public int Modified_By
        {
            get;
            set;
        }
        public bool Is_Active
        {
            get;
            set;
        }
        public bool Is_Deleted
        {
            get;
            set;
        }

    }
}

 Step 4:-
My DataAccessLayer(UHRMS_EmployeeExitDataAccess.cs):-

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using Swash.Objects;
namespace Swash.DataAccessLayer
{
    public partial class UERPDataAccess
    {
        
        #region Retrive EmployeeName By Designation

        public DataTable GetEmployeeNameByDesignation(int Company_ID, int DesignetionID)
        {
            DataTable dtEmpname = new DataTable();
            SqlCommand SelectCommand = new SqlCommand("Uhrms.KC_SelectEmpNameByDesignation");
            SelectCommand.CommandType = CommandType.StoredProcedure;
            SelectCommand.Parameters.AddWithValue("@Company_ID", Company_ID);
            SelectCommand.Parameters.AddWithValue("@Designation_ID", DesignetionID);
            dtEmpname = ExecuteGetDataTable(SelectCommand);
            return dtEmpname;
        }
        #endregion


        //-------------------------------------------- Insert Employee Exit Details Details-------------------------------------------
        #region Insert Employee Exit Records

        public void InsertExitDetails(UHRMS_EmployeeExitObject EmpExit)
        {
            SqlCommand Exit = new SqlCommand("Uhrms.KC_EmployeeExit");
            Exit.CommandType = CommandType.StoredProcedure;
            Exit.Parameters.AddWithValue("@Operation", EmpExit.Operation);
            Exit.Parameters.AddWithValue("@Employee_ID", Convert.ToInt32(EmpExit.Employee_ID));
            Exit.Parameters.AddWithValue("@Date_of_Resignation", Convert.ToDateTime(EmpExit.Date_of_Resignation));
            Exit.Parameters.AddWithValue("@Last_Working_Date", Convert.ToDateTime(EmpExit.Last_Working_Date));
            Exit.Parameters.AddWithValue("@Notice_Period", Convert.ToInt32(EmpExit.Notice_Period));
            Exit.Parameters.AddWithValue("@Comments", EmpExit.Comments);
            Exit.Parameters.AddWithValue("@Reason_for_Resignation", EmpExit.Reason_for_Resignation);
             Exit.Parameters.AddWithValue("@Is_Rehireable", EmpExit.Is_Rehireable);
             Exit.Parameters.AddWithValue("@Supervisor_Comment", EmpExit.Supervisor_Comment);
             Exit.Parameters.AddWithValue("@Continue_With_Interview_Process", EmpExit.Continue_With_Interview_Process);
             Exit.Parameters.AddWithValue("@Accepted_Agreement", EmpExit.Accepted_Agreement);
            Exit.Parameters.AddWithValue("@Created_By", Convert.ToInt32(EmpExit.Created_By));
            Exit.Parameters.AddWithValue("@Is_Active", EmpExit.Is_Active);
            Exit.Parameters.AddWithValue("@Is_Deleted", EmpExit.Is_Deleted);
            ExecuteStoredProcedure(Exit);
        }
        #endregion


    }
}


 Step 5:-
My  Integration Layer(UHRMS_EmployeeExitIntegration.cs):- 

 using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using Swash.DataAccessLayer;
using Swash.Objects;
namespace Swash.IntegrationLayer
{
    public partial class UERPIntegration
    {
        #region Retrive EmployeeName
        public static List<UHRMS_EmpContractRenewalDetails> GetEmployeeNameByDesignation(int Company_ID, int DesignetionID)
        {
            List<UHRMS_EmpContractRenewalDetails> EmpNameList = new List<UHRMS_EmpContractRenewalDetails>();
            DataTable dtEmpNameList = new DataTable();
            dtEmpNameList = UERPDataAccess.GetInstance.GetEmployeeNameByDesignation(Company_ID, DesignetionID);
            foreach (DataRow drEmpName in dtEmpNameList.Rows)
            {
                UHRMS_EmpContractRenewalDetails objEmpName = new UHRMS_EmpContractRenewalDetails();
                objEmpName.EmployeeID = Convert.ToInt32(drEmpName["Employee_ID"].ToString());
                objEmpName.EmployeeName = drEmpName["NAME"].ToString();
             
                EmpNameList.Add(objEmpName);
            }
            return EmpNameList;
        }

        #endregion


        #region Inserting Records



        #region Inserting Exit Information
        public static void InsertExitDetails(UHRMS_EmployeeExitObject EmpExit)
        {
            UERPDataAccess.GetInstance.InsertExitDetails(EmpExit);
        }
        #endregion

        #endregion
    }
}

 Step 6:-
My  Cache Layer(UHRMS_EmployeeExitCache.cs):- 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Swash.IntegrationLayer;
using Swash.Objects;
using System.IO;
namespace Swash.CacheLayer
{
    public partial class UERPCache
    {
        #region Retrive EmployeeName
        public static List<UHRMS_EmpContractRenewalDetails> GetEmployeeNameByDesignation(int Company_ID, int DesignetionID)
        {
            return UERPIntegration.GetEmployeeNameByDesignation(Company_ID, DesignetionID);
        }
        #endregion
        #region Inserting Records



        #region Inserting Empoyee's Exit Information
        public static void InsertExitDetails(UHRMS_EmployeeExitObject EmpExit)
        {
            UERPIntegration.InsertExitDetails(EmpExit);
        }
        #endregion

        #endregion

    }
}

 Step 7:-
My   Business Layer(UHRMS_EmployeeExitBusiness.cs):- 


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Swash.CacheLayer;
using Swash.Objects;
using System.IO;
namespace Swash.BusinessLayer
{
    public partial class UERPManagement
    {
        #region Retrive EmployeeName

        public List<UHRMS_EmpContractRenewalDetails> GetEmployeeNameByDesignation(int Company_ID, int DesignetionID)
        {

            string Context = "UERPManagement.GetEmployeeNameByDesignation()";
            try
            {
                return UERPCache.GetEmployeeNameByDesignation(Company_ID, DesignetionID);
            }
            catch (System.Exception ex)
            {
                throw (new Exception(Context, ex));
            }
        }
        #endregion
        #region Inserting Records



        #region Inserting Employee's Exit Information
        public void InsertExitDetails(UHRMS_EmployeeExitObject EmpExit)
        {
            string Context = "UERPManagement.InsertExitDetails(EmpExit)";
            try
            {
                UERPCache.InsertExitDetails(EmpExit);
            }
            catch (System.Exception ex)
            {

                throw (new Exception(Context, ex));
            }
        }

        #endregion

        #endregion
      
    }
}

 Step 8:-
My  Abstract Class(_UERPDataAccess.cs):- 




using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Swash.DataAccessLayer
{
    public partial class UERPDataAccess : AbstractData_SqlClient
    {
        #region Code for Singleton Instance of the Data Layer
        #region Variables
        /// <summary>
        /// Private static member to implement singleton
        /// </summary>
        private static UERPDataAccess instance = new UERPDataAccess();
        #endregion Variables

        #region Properties
        /// <summary>
        /// Static property of class which will give singleton instance of it.
        /// </summary>
        /// <returns>return singleton instance of ClientData class</returns>
        public static UERPDataAccess GetInstance
        {
            get
            {
                return instance;
            }
        }
        #endregion Properties
        #endregion

        #region Specify Database Connection String
        private UERPDataAccess()
        {
            this.ConnectionKey = "ConnectionString";
        }
        #endregion

        public System.Data.DataTable GetParentCompany()
        {
            throw new NotImplementedException();
        }


    }
}


Here is the Common Abstract Class:-

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Swash.Frameworks;

namespace Swash.DataAccessLayer
{
    // <summary>
    /// Provide CRUD(CREATE, READ, UPDATE, DELETE) functionality for database
    /// </summary>
    public abstract class AbstractData_SqlClient
    {
        #region Methods and Implementations
        /// <summary>
        /// Key of the connection string in web.config
        /// </summary>
        private string _ConnectionKey;

        /// <summary>
        /// Key of the connection string in web.config
        /// </summary>
        public string ConnectionKey
        {
            get
            {
                return _ConnectionKey;
            }
            set
            {
                _ConnectionKey = value;
            }
        }

        /// <summary>
        /// Context from MultiCompaniesShipCompCode in web.config defines in BusinessDataAccess Layer
        /// </summary>
        private string _Context;

        /// <summary>
        /// Context from MultiCompaniesShipCompCode in web.config defines in BusinessDataAccess Layer
        /// </summary>
        public string Context
        {
            get { return _Context; }
            set { _Context = value; }
        }

        /// <summary>
        /// No argument constructor for Abstract Data
        /// </summary>
        protected AbstractData_SqlClient()
        {
        }

        /// <summary>
        /// Exception Policy to sue to handle the exception
        /// </summary>
        private const string ExceptionPolicyValue = "Data Policy";

        /// <summary>
        /// Replace single quote with its ASCII equivalent.
        /// </summary>
        /// <param name="input">String contains single quote</param>
        /// <returns>Parsed string</returns>
        private string parseString(string input)
        {
            return input.Replace("'", "&#39");
        }


        /// <summary>
        /// Execute the SQL command and return single value (Used for return count from SQL)
        /// </summary>
        /// <param name="strSQL">SqlClient command</param>
        /// <returns>Return single query result</returns>
        protected object ExecuteScalar(string strSQL)
        {
            // Variables
            object objResult = null;
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
                oCommand.Connection.Open();

                oCommand.CommandText = strSQL;

                objResult = oCommand.ExecuteScalar();
            }
            catch (Exception e)
            {
                if (strSQL != null)
                {
                    Exception ex = new Exception(strSQL, e);
                    Log.Error(ex, true);
                }
                else
                {
                    Log.Error(e, true);
                }
            }
            finally
            {
                ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
            }

            // Returning object
            return objResult;
        }

        /// <summary>
        /// Execute the SQL command and return single value (Used for return count from SQL)
        /// </summary>
        /// <param name="oCommand">Sql command</param>
        /// <returns>Return single query result</returns>
        protected object ExecuteScalar(SqlCommand oCommand)
        {
            // Variables
            object objResult = null;

            try
            {
                oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
                oCommand.Connection.Open();

                oCommand.CommandType = CommandType.StoredProcedure;

                objResult = oCommand.ExecuteScalar();
            }
            catch (Exception e)
            {
                bool rethrow = true;
                if (oCommand != null)
                {
                    Exception ex = new Exception(oCommand.CommandText, e);
                    Log.Error(ex, true);
                }
                else
                {
                    Log.Error(e, true);
                }
                if (rethrow)
                {
                    throw;
                }
            }
            finally
            {
                ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
            }

            // Returning object
            return objResult;
        }

        /// <summary>
        /// Execute the SQL command and return a datarow containing data.
        /// </summary>
        /// <param name="oCommand">Sql command</param>
        /// <returns>Datarow containing data</returns>
        protected DataRow ExecuteGetDataRow(SqlCommand oCommand)
        {
            // Variables
            DataSet DsResult = new DataSet();
            DataRow drowObject = null;
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            try
            {
                oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
                oCommand.Connection.Open();

                oCommand.CommandType = CommandType.StoredProcedure;

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(DsResult);
                if (DsResult.Tables.Count != 0 && DsResult.Tables[0].Rows.Count != 0)
                {
                    drowObject = DsResult.Tables[0].Rows[0];
                }

                // Genetating an exception if more than one record has been found.
                if (DsResult.Tables[0].Rows.Count > 1)
                {
                    throw new Exception("More than 1 record found for stored procedure " + oCommand.CommandText + ".");
                }
            }
            catch (Exception e)
            {
                if (oCommand != null)
                {
                    Exception ex = new Exception(oCommand.CommandText, e);
                    Log.Error(ex, true);
                }
                else
                {
                    Log.Error(e, true);
                }
            }
            finally
            {
                ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
            }

            return drowObject;
        }

        /// <summary>
        /// Execute the SQL command and return a datable containing data.
        /// </summary>
        /// <param name="oCommand">Oledb command</param>
        /// <returns>Datarow containing data</returns>
        protected DataTable ExecuteGetDataTable(SqlCommand oCommand)
        {
            // Variables
            DataSet DsResult = new DataSet();
            DataTable dtableObject = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            try
            {
                oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
                oCommand.Connection.Open();

                oCommand.CommandType = CommandType.StoredProcedure;

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(DsResult);
                dtableObject = DsResult.Tables[0];
            }
            catch (Exception e)
            {
                if (oCommand != null)
                {
                    Exception ex = new Exception(oCommand.CommandText, e);
                    Log.Error(ex, true);
                }
                else
                {
                    Log.Error(e, true);
                }
            }
            finally
            {
                ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
            }

            return dtableObject;
        }

        /// <summary>
        /// Execute the SQL command and return a datable containing data.
        /// </summary>
        /// <param name="oCommand">Oledb command</param>
        /// <returns>Datarow containing data</returns>
        protected DataTable ExecuteGetDualRequest(SqlCommand oCommandSP, string strSQL)
        {
            // Variables
            DataSet DsResult = new DataSet();
            DataTable dtableObject = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
                oCommand.Connection.Open();


                oCommand.CommandType = CommandType.Text;
                oCommand.CommandText = strSQL;

                oAdapter.SelectCommand = oCommand;
                oAdapter.SelectCommand.ExecuteScalar();

                oCommand.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter OP in oCommandSP.Parameters)
                {
                    oCommand.Parameters.Add(GetParameter(OP.ParameterName, OP.SqlDbType, OP.Value)).Direction = OP.Direction;
                }

                oCommand.CommandText = oCommandSP.CommandText;

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(DsResult);
                dtableObject = DsResult.Tables[0];
            }
            catch (Exception e)
            {
                if (oCommand != null)
                {
                    Exception ex = new Exception(oCommand.CommandText, e);
                    Log.Error(ex, true);
                }
                else
                {
                    Log.Error(e, true);
                }
            }
            finally
            {
                ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
            }

            return dtableObject;
        }

        /// <summary>
        /// Execute the SQL query and return a datable containing data.
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns>Datarow containing data</returns>
        protected DataTable ExecuteGetDataTable(string strSQL)
        {
            // Variables
            DataTable dtableObject = new DataTable();
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
                oCommand.Connection.Open();

                oCommand.CommandText = strSQL;
                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(dtableObject);
            }
            catch (Exception e)
            {
                if (oCommand != null)
                {
                    Exception ex = new Exception(oCommand.CommandText, e);
                    Log.Error(ex, true);
                }
                else
                {
                    Log.Error(e, true);
                }
            }
            finally
            {
                ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
            }

            return dtableObject;
        }

        ///// <summary>
        ///// Execute the stored procedure stored in oCommand
        ///// </summary>
        ///// <param name="oCommand">the Command to execute (stored procedure)</param>
        ///// <param name="oBlobParameters">the Oledb blob parameters to store the contents in</param>
        ///// <param name="oContents">the binary contents (in same order than oBlobParameters)</param>
        //protected void ExecuteStoredProcedureWithBlob(SqlCommand oCommand, SqlParameter[] oBlobParameters, Byte[][] oContents)
        //{
        //    // OledbParameter currentParameter;
        //    SqlTransaction oTransaction = null;
        //    OledbLob[] oOledbLobs = null;
        //    OledbConnection oConnection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
        //    try
        //    {
        //        OledbCommand blobCommands = new OledbCommand();
        //        oConnection.Open();
        //      

        //        // Create the transaction within the blob inserts will be executed
        //        oTransaction = oConnection.BeginTransaction();
        //        blobCommands.Connection = oConnection;
        //        blobCommands.Transaction = oTransaction;

        //        int i = 0;
        //        oOledbLobs = new OledbLob[oBlobParameters.Length];
        //        foreach (OledbParameter currentParameter in oBlobParameters)
        //        {
        //            // Create temporary blob Oledb parameters
        //            blobCommands.CommandText = "declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob" + i + ":= xx; end;";
        //            blobCommands.Parameters.Add(new OledbParameter("tempblob" + i, OledbType.Blob)).Direction = ParameterDirection.Output;
        //            blobCommands.Transaction = oTransaction;
        //            blobCommands.CommandType = CommandType.Text;
        //            blobCommands.ExecuteNonQuery();

        //            // We get the the previously created parameter from the command
        //            // and fill in the content with oContents argument
        //            oOledbLobs[i] = (OledbLob)blobCommands.Parameters[0].Value;
        //            oOledbLobs[i].BeginBatch(OledbLobOpenMode.ReadWrite);
        //            oOledbLobs[i].Write(oContents[i], 0, oContents[i].Length);
        //            // This operation is made under the same transaction than
        //            // the insert one (see oTransaction)
        //            oOledbLobs[i].EndBatch();
        //            oBlobParameters[i].Value = oOledbLobs[i];

        //            i++;
        //        }
        //        // Then execute the main command
        //        oCommand.Connection = oConnection;
        //        oCommand.Transaction = oTransaction;
        //        oCommand.CommandType = CommandType.StoredProcedure;
        //        oCommand.ExecuteNonQuery();

        //        oCommand.Transaction.Commit();
        //    }
        //    catch (Exception e)
        //    {
        //        if (oConnection.State == ConnectionState.Open)
        //        {
        //            if (oTransaction != null)
        //            {
        //                oTransaction.Rollback();
        //            }
        //        }
        //        if (oCommand != null)
        //        {
        //            Exception ex = new Exception(oCommand.CommandText, e);
        //            Log.Error(ex, true);
        //        }
        //        else
        //        {
        //            Log.Error(e, true);
        //        }
        //    }
        //    finally
        //    {
        //        ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
        //    }
        //}

        /// <summary>
        /// Execute the SQL query and return a datable containing data.
        /// </summary>
        /// <param name="SQL"></param>
        /// <returns>Datarow containing data</returns>
        protected DataRow ExecuteGetDataRow(string strSQL)
        {
            // Variables
            DataSet DsResult = new DataSet();
            DataRow drowObject = null;
            SqlDataAdapter oAdapter = new SqlDataAdapter();
            SqlCommand oCommand = new SqlCommand();

            try
            {
                oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
                oCommand.Connection.Open();

                oCommand.CommandText = strSQL;
                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(DsResult);

                if (DsResult.Tables.Count != 0 && DsResult.Tables[0].Rows.Count != 0)
                {
                    drowObject = DsResult.Tables[0].Rows[0];
                }

                // Genetating an exception if more than one record has been found.
                if (DsResult.Tables[0].Rows.Count > 1)
                {
                    throw new Exception("More than 1 record found for stored procedure " + oCommand.CommandText + ".");
                }
            }
            catch (Exception e)
            {
                bool rethrow = true;
                if (oCommand != null)
                {
                    Exception ex = new Exception(oCommand.CommandText, e);
                    Log.Error(ex, true);
                }
                else
                {
                    Log.Error(e, true);
                }
                if (rethrow)
                {
                    throw;
                }
            }
            finally
            {
                ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
            }

            return drowObject;
        }

        /// <summary>
        /// Execute the SQL command and return a dataset containing data.
        /// </summary>
        /// <param name="oCommand">Oledb command</param>
        /// <param name="strTableName">Name of the table to fill</param>
        /// <param name="dsetObject">DataSet containing data</param>
        protected void ExecuteGetDataSet(SqlCommand oCommand, string strTableName, DataSet dsetObject)
        {
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            try
            {
                oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
                oCommand.Connection.Open();

                oCommand.CommandType = CommandType.StoredProcedure;

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(dsetObject, strTableName);
            }
            catch (Exception e)
            {
                if (oCommand != null)
                {
                    Exception ex = new Exception(oCommand.CommandText, e);
                    Log.Error(ex, true);
                }
                else
                {
                    Log.Error(e, true);
                }
            }
            finally
            {
                ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
            }
        }

        /// <summary>
        /// Execute the SQL command and return a dataset containing multiple table.
        /// </summary>
        /// <param name="oCommand">Oledb command</param>
        /// <param name="dsetObject">DataSet containing data</param>
        protected void ExecuteGetDataSet(SqlCommand oCommand, DataSet dsetObject)
        {
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            try
            {
                oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
                oCommand.Connection.Open();

                oCommand.CommandType = CommandType.StoredProcedure;

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(dsetObject);
            }
            catch (Exception e)
            {
                if (oCommand != null)
                {
                    Exception ex = new Exception(oCommand.CommandText, e);
                    Log.Error(ex, true);
                }
                else
                {
                    Log.Error(e, true);
                }
            }
            finally
            {
                ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
            }
        }

        protected void ExecuteSqlStatement(SqlCommand oCommand)
        {
            try
            {
                oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
                oCommand.Connection.Open();
                oCommand.CommandType = CommandType.Text;
                oCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                if (oCommand != null)
                {
                    Exception ex = new Exception(oCommand.CommandText, e);
                    Log.Error(ex, true);
                }
                else
                {
                    Log.Error(e, true);
                }
            }
            finally
            {
                ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
            }
        }


        /// <summary>
        /// Execute the stored procedure
        /// </summary>
        /// <param name="strSPName">Name of procedure to be executed</param>
        /// <param name="parametersList">List of input parameter for stored procedure</param>
        protected void ExecuteStoredProcedure(SqlCommand oCommand)
        {
            try
            {
                oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
                oCommand.Connection.Open();

                oCommand.CommandType = CommandType.StoredProcedure;
                oCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                if (oCommand != null)
                {
                    Exception ex = new Exception(oCommand.CommandText, e);
                    Log.Error(ex, true);
                }
                else
                {
                    Log.Error(e, true);
                }
            }
            finally
            {
                ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
            }
        }


        /// <summary>
        /// Execute the stored procedure
        /// </summary>
        /// <param name="strSPName">Name of procedure to be executed</param>
        /// <param name="parametersList">List of input parameter for stored procedure</param>
        protected int ExecuteStoredProcedureGetID(SqlCommand oCommand)
        {
            int RetValue = 0;
            try
            {
                oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
                oCommand.Connection.Open();

                oCommand.CommandType = CommandType.StoredProcedure;
                oCommand.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                if (oCommand != null)
                {
                    Exception ex = new Exception(oCommand.CommandText, e);
                    Log.Error(ex, true);
                }
                else
                {
                    Log.Error(e, true);
                }
            }
            finally
            {
                ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
            }
            return RetValue;
        }

        /// <summary>
        /// Execute the stored procedure list under same transaction.
        /// </summary>
        /// <param name="strSPName">Name of procedure to be executed</param>
        /// <param name="commandList">List of Command object to be executed under same transaction</param>
        protected int ExecuteStoredProcedure(SqlCommand[] commandList)
        {
            SqlTransaction oTransaction = null;
            string currentProcedure = "";
            int count = 0;

            SqlConnection oConn = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);

            try
            {
                oConn.Open();
                ////InitializeContext(oConn);
                oTransaction = oConn.BeginTransaction();

                foreach (SqlCommand command in commandList)
                {
                    command.Transaction = oTransaction;
                    command.Connection = oConn;
                    command.CommandType = CommandType.StoredProcedure;
                    currentProcedure = command.CommandText;
                    count += command.ExecuteNonQuery();
                }

                oTransaction.Commit();
            }
            catch (Exception e)
            {
                if (oConn.State == ConnectionState.Open)
                {
                    if (oTransaction != null)
                    {
                        oTransaction.Rollback();
                    }
                }

                Log.Error(e, true);
            }
            finally
            {
                ConnectionFactory.GetInstance.CloseConnection(oConn);
            }

            return count;
        }

        /// <summary>
        /// Get the Next seed value of auto generated column.
        /// </summary>
        /// <param name="strSequenceName">Name of sequence to be get</param>
        /// <returns>Next seed value</returns>
        protected int GetNextSequence(string strSequenceName)
        {
            // Variables
            int intReturn = -1;
            SqlCommand oCommand = new SqlCommand();
            SqlDataReader oReader;

            try
            {
                oCommand.Connection = ConnectionFactory.GetInstance.GetConnection(_ConnectionKey);
                oCommand.Connection.Open();

                oCommand.CommandText = "SELECT " + strSequenceName + ".NEXTVAL SEQVALUE FROM DUAL";
                oReader = oCommand.ExecuteReader();

                // Generating string
                if (oReader.Read())
                {
                    intReturn = int.Parse(oReader["SEQVALUE"].ToString());
                }
            }
            catch (Exception e)
            {
                if (strSequenceName != null)
                {
                    Exception ex = new Exception(strSequenceName, e);
                    Log.Error(ex, true);
                }
                else
                {
                    Log.Error(e, true);
                }
            }
            finally
            {
                ConnectionFactory.GetInstance.CloseConnection(oCommand.Connection);
            }

            // Returning string.
            return intReturn;
        }

        /// <summary>
        /// Return Oledb parameter object.
        /// </summary>
        /// <param name="paramName">Oledb parameter name</param>
        /// <param name="paramType">Oledb parameter type</param>
        /// <param name="paramValue">Oledb parameter value</param>
        /// <returns>Oledb parameter</returns>
        protected SqlParameter GetParameter(string paramName, SqlDbType paramType, object paramValue)
        {
            return GetParameter(paramName, paramType, paramValue, 0);
        }

        /// <summary>
        /// Return Oledb parameter object.
        /// </summary>
        /// <param name="paramName">Oledb parameter name</param>
        /// <param name="paramType">Oledb parameter type</param>
        /// <param name="paramValue">Oledb parameter value</param>
        /// <param name="paramSize">Oledb parameter size</param>
        /// <returns>Oledb parameter</returns>
        protected SqlParameter GetParameter(string paramName, SqlDbType paramType, object paramValue, int paramSize)
        {
            SqlParameter oParameter = new SqlParameter();
            oParameter.ParameterName = paramName;
            oParameter.SqlDbType = paramType;

            if (paramType == SqlDbType.VarChar || paramType == SqlDbType.VarBinary)
            {
                oParameter.Value = (paramValue == null) ? "" : (string)paramValue;
            }
            else
            {
                oParameter.Value = paramValue;
            }

            if (paramType == SqlDbType.VarChar || paramType == SqlDbType.Int)
            {
                if (paramSize != 0)
                {
                    oParameter.Size = paramSize;
                }
            }

            oParameter.Direction = ParameterDirection.Input;
            return oParameter;
        }
        #endregion Methods and Implementations

        #region InnerClass ConnectionFactory
        /// <summary>
        /// It give connection objects. It is a singleton class.
        /// </summary>
        private sealed class ConnectionFactory
        {
            #region Variables
            /// <summary>
            /// Static memeber of connection factory class.
            /// </summary>
            private static ConnectionFactory _instance = new ConnectionFactory();
            #endregion Variables

            #region Properties
            /// <summary>
            /// Static property, it returns the static private member of connection factory. Its
            /// for implementing singleton.
            /// </summary>
            public static ConnectionFactory GetInstance
            {
                get
                {
                    return _instance;
                }
            }
            #endregion Properties

            #region Methods & Implementation
            /// <summary>
            /// Construtor of connection factory.
            /// </summary>
            private ConnectionFactory()
            {
            }

            /// <summary>
            /// Returns Oledb connection object.
            /// </summary>
            /// <returns>Oledb connection object.</returns>
            public SqlConnection GetConnection(string ConnectionKey)
            {
                //string myConnString = ConfigurationManager.ConnectionStrings[ConnectionKey].ToString();

                // To use encrypted password
                string myConnString = ConfigurationManager.ConnectionStrings[ConnectionKey].ToString();
                string[] pwd = myConnString.Split(';');
                string pass = pwd[pwd.Length - 1];
                string passWord = pass.Substring(4); //Swash.Frameworks.Password.DecryptBase64(pass.Substring(4));
                myConnString = pwd[0] + ";" + pwd[1] + ";" + pwd[2] + ";pwd=" + passWord;

                SqlConnection oConn = new SqlConnection(myConnString);

                return oConn;
            }

            /// <summary>
            /// Close the connection state.
            /// </summary>
            /// <param name="oConn">Connection object to be closed.</param>
            public void CloseConnection(SqlConnection oConn)
            {
                if (oConn != null)
                {
                    if (oConn.State == ConnectionState.Open)
                    {
                        oConn.Close();
                    }
                    oConn.Dispose();
                }
            }


            private string EncryptBase64(string thePassword)
            {
                try
                {
                    byte[] encData_byte = new byte[thePassword.Length];
                    encData_byte = System.Text.Encoding.UTF8.GetBytes(thePassword);
                    string encodedData = Convert.ToBase64String(encData_byte);
                    return encodedData;
                }
                catch (Exception ex)
                {
                    throw new Exception("Error in EncryptBase64" + ex.Message);
                }
            }

            private string DecryptBase64(string thePassword)
            {
                try
                {
                    System.Text.UTF8Encoding encoder = new System.Text.UTF8Encoding();
                    System.Text.Decoder utf8Decode = encoder.GetDecoder();
                    byte[] todecode_byte = Convert.FromBase64String(thePassword);
                    int charCount = utf8Decode.GetCharCount(todecode_byte, 0, todecode_byte.Length);
                    char[] decoded_char = new char[charCount];
                    utf8Decode.GetChars(todecode_byte, 0, todecode_byte.Length, decoded_char, 0);
                    string result = new String(decoded_char);
                    return result;
                }
                catch (Exception ex)
                {
                    throw new Exception("Error in DecryptBase64" + ex.Message);
                }

            }
            #endregion Methods & Implementation
        }
        #endregion InnerClass ConnectionFactory
    }
}




Step 9:-
My  Code Behind of User control(UHRMS_EmployeeExit.ascx.cs):- 


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using Swash.Objects;
using Swash.BusinessLayer;

namespace Website.App_WebControls.UHRMS_UserControls
{
    public partial class UHRMS_EmployeeExit : System.Web.UI.UserControl
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                FillBranchName();
                ddlDepartment.Items.Insert(0, "Select");
                ddlDesignation.Items.Insert(0, "Select");
                ddlEmpName.Items.Insert(0, "Select");
                ddlSuperVisor.Items.Insert(0, "Select");
                ddlDepartment.Enabled = false;
                ddlDesignation.Enabled = false;
                ddlEmpName.Enabled = false;
                ddlSuperVisor.Enabled = false;
            }
            //###################################For not refreshing parent data in any postback ##############################################

            ScriptManager.GetCurrent(this.Parent.Page).RegisterPostBackControl(BtnSave);
            ScriptManager.GetCurrent(this.Parent.Page).RegisterPostBackControl(ddlBranch);
            ScriptManager.GetCurrent(this.Parent.Page).RegisterPostBackControl(ddlDepartment);
            ScriptManager.GetCurrent(this.Parent.Page).RegisterPostBackControl(ddlDesignation);
            ScriptManager.GetCurrent(this.Parent.Page).RegisterPostBackControl(ddlEmpName);
        }

        protected void ddlBranch_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddlBranch.SelectedIndex != 0)
            {
                ddlDepartment.Enabled = true;
                ddlDepartment.Items.Clear();
                FillDepartmentName(int.Parse(ddlBranch.SelectedItem.Value));
            }
            else
            {
                ddlDepartment.Items.Clear();
                ddlDepartment.Items.Insert(0, "Select");
                ddlDepartment.Enabled = false;
                ddlDesignation.Items.Clear();
                ddlDesignation.Items.Insert(0, "Select");
                ddlDesignation.Enabled = false;

                ddlEmpName.Items.Clear();
                ddlEmpName.Items.Insert(0, "Select");
                ddlEmpName.Enabled = false;

                ddlSuperVisor.Items.Clear();
                ddlSuperVisor.Items.Insert(0, "Select");
                ddlSuperVisor.Enabled = false;
            }
        }

        protected void ddlDesignation_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddlDesignation.SelectedIndex != 0)
            {
                ddlEmpName.Enabled = true;
                ddlEmpName.Items.Clear();
                FillEmpName(1,int.Parse(ddlDesignation.SelectedItem.Value));
            }
            else
            {
               
                ddlEmpName.Items.Clear();
                ddlEmpName.Items.Insert(0, "Select");
                ddlEmpName.Enabled = false;

                ddlSuperVisor.Items.Clear();
                ddlSuperVisor.Items.Insert(0, "Select");
                ddlSuperVisor.Enabled = false;
            }
        }

        protected void ddlEmpName_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddlEmpName.SelectedIndex != 0)
            {
              
                ddlSuperVisor.Items.Clear();
                ddlSuperVisor.Items.Insert(0, "Select");
                ddlSuperVisor.Enabled = true;
            }
        }

        protected void ddlDepartment_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (ddlDepartment.SelectedIndex != 0)
            {
                ddlDesignation.Enabled = true;
                ddlDesignation.Items.Clear();
                FillDesignationName(int.Parse(ddlDepartment.SelectedItem.Value));
            }
            else
            {
                ddlDesignation.Items.Clear();
                ddlDesignation.Items.Insert(0, "Select");
                ddlDesignation.Enabled = false;
                ddlEmpName.Items.Clear();
                ddlEmpName.Items.Insert(0, "Select");
                ddlEmpName.Enabled = false;

                ddlSuperVisor.Items.Clear();
                ddlSuperVisor.Items.Insert(0, "Select");
                ddlSuperVisor.Enabled = false;
            }
        }

        private void FillEmpName(int Company_ID, int DesignationID)
        {
            List<UHRMS_EmpContractRenewalDetails> EmpList = new List<UHRMS_EmpContractRenewalDetails>();
            EmpList = UERPManagement.GetInstance.GetEmployeeNameByDesignation(Company_ID, DesignationID);
            if (EmpList.Count != 0)
            {
                ddlEmpName.DataSource = EmpList;
                ddlEmpName.DataTextField = "EmployeeName";
                ddlEmpName.DataValueField = "EmployeeID";
                ddlEmpName.DataBind();
                ddlEmpName.Items.Insert(0, "Select");
            }
        }

        //###################################Fill Branch ##############################################
        private void FillBranchName()
        {
            UHRMS_EmployeeTransferWithStatusObject objBranch = new UHRMS_EmployeeTransferWithStatusObject();
            objBranch.Operation = "SelectDivisionBranch";
            List<UHRMS_EmployeeTransferWithStatusObject> BranchList = new List<UHRMS_EmployeeTransferWithStatusObject>();
            BranchList = UERPManagement.GetInstance.ShowDivisionBranch(objBranch);
            if (BranchList.Count != 0)
            {
                for (int i = 0; i < BranchList.Count; i++)
                {
                    ListItem li = new ListItem();
                    li.Value = BranchList[i].Division_Branch_ID.ToString();
                    li.Text = BranchList[i].Branch_Name;
                    ddlBranch.Items.Add(li);
                }
                ddlBranch.Items.Insert(0, "Select");
            }
        }
        //###################################Fill Department ##############################################
        private void FillDepartmentName(int Branch_ID)
        {
            UHRMS_EmployeeTransferWithStatusObject objDepartment = new UHRMS_EmployeeTransferWithStatusObject();
            objDepartment.Operation = "SelectDepartment";
            objDepartment.Division_Branch_ID = Branch_ID;
            List<UHRMS_EmployeeTransferWithStatusObject> DepartmentList = new List<UHRMS_EmployeeTransferWithStatusObject>();
            DepartmentList = UERPManagement.GetInstance.ShowDepartment(objDepartment);
            if (DepartmentList.Count > 0)
            {
                ddlDepartment.DataSource = DepartmentList;
                ddlDepartment.DataTextField = "Department_Name";
                ddlDepartment.DataValueField = "Department_ID";
                ddlDepartment.DataBind();
                ddlDepartment.Items.Insert(0, "Select");
            }
        }


        //###################################Fill designation ##############################################
        private void FillDesignationName(int Department_ID)
        {
            UHRMS_EmployeeTransferWithStatusObject objDesignation = new UHRMS_EmployeeTransferWithStatusObject();
            objDesignation.Operation = "SelectDesignation";
            List<UHRMS_EmployeeTransferWithStatusObject> DesignationList = new List<UHRMS_EmployeeTransferWithStatusObject>();
            DesignationList = UERPManagement.GetInstance.ShowDesignation(objDesignation);
            var Desgn = from Designation in DesignationList
                        where Designation.Department_ID == Department_ID
                        select Designation;
            ddlDesignation.DataSource = Desgn.ToList();
            ddlDesignation.DataTextField = "Designation_Name";
            ddlDesignation.DataValueField = "Designation_ID";
            ddlDesignation.DataBind();
            ddlDesignation.Items.Insert(0, "Select");
        }

    }
}
Running View:-



My database View:-



My Store Procedure:-


ALTER PROCEDURE [Uhrms].[KC_EmployeeExit]
(      @Operation varchar(30)='',
    @Employee_Exit_ID      int=0,
       @Employee_ID  int=0,
      
       @Date_of_Resignation datetime=''
       @Last_Working_Date   datetime=''
       @Notice_Period       int=0,
       @Reason_for_Resignation varchar(100)='',
       @Comments varchar(100)='',
       @Is_Rehireable bit='false',
       @Supervisor_Comment varchar(100)='',
       @Continue_With_Interview_Process bit='false',
       @Accepted_Agreement bit='false',
       @Created_On   datetime='',
       @Created_By   int=0,
       @Modified_On  datetime='',
       @Modified_By  int=0,
       @Is_Active bit='true',
       @Is_Deleted bit='false'
             
)
AS
BEGIN

if @Operation='InsertEmployeeExit'
begin
insert into [kenCampus].[Uhrms].[HRMS_EmployeeExit]
(
       Employee_ID,        
       Date_of_Resignation,
       Last_Working_Date,  
       Notice_Period,
    Reason_for_Resignation,
       Comments,    
       Is_Rehireable,      
       Supervisor_Comment,
       Continue_With_Interview_Process, 
       Accepted_Agreement, 
       Created_On,
       Created_By,
       Is_Active,
       Is_Deleted
                    
       )
       values
       (
             
       @Employee_ID,
       @Date_of_Resignation,     
       @Last_Working_Date, 
       @Notice_Period,     
       @Reason_for_Resignation ,
       @Comments,
       @Is_Rehireable,
       @Supervisor_Comment,
       @Continue_With_Interview_Process ,
       @Accepted_Agreement,
        getdate(),
       @Created_By,
      
       @Is_Active,
       @Is_Deleted
              )
              end
             

       END

 WebService that i have used(UHRMS_EmployeeExit.asmx):-



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using Swash.Objects;
using Swash.BusinessLayer;

namespace Website.App_WebServices
{
    /// <summary>
    /// Summary description for UHRMS_EmployeeExit
    /// </summary>
    [WebService(Namespace = "http://tempuri.org/")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
     [System.Web.Script.Services.ScriptService]
    public class UHRMS_EmployeeExit : System.Web.Services.WebService
    {

        [WebMethod]
        public string HelloWorld()
        {
            return "Hello World";
        }

        int rowsInserted = 1;
        [WebMethod]
        public string ReceiveWebService(string Employee_ID, string Date_of_Resignation, string Last_Working_Date, string Notice_Period, string Reason_for_Resignation, string Comments, int Is_Rehireable, string Supervisor_Comment, string Continue_With_Interview_Process, string Accepted_Agreement, string Created_By, string Is_Active, string Is_Deleted)
        {
            UHRMS_EmployeeExitObject EmpExit = new UHRMS_EmployeeExitObject();
            EmpExit.Operation = "InsertEmployeeExit";
            EmpExit.Employee_ID =Convert.ToInt32(Employee_ID);
            //************ To Change the Date format from dd/MM/yyyy to MM/dd/yyyy************//
            string resignationDate = Date_of_Resignation;
            System.Globalization.DateTimeFormatInfo dateresignation = new System.Globalization.DateTimeFormatInfo();
            dateresignation.ShortDatePattern = "dd/MM/yyyy";
            DateTime Resignation = Convert.ToDateTime(resignationDate, dateresignation);
            //********************************************************************************//
            EmpExit.Date_of_Resignation = Resignation;

            //************ To Change the Date format from dd/MM/yyyy to MM/dd/yyyy************//
            string LastDate = Last_Working_Date;
            System.Globalization.DateTimeFormatInfo dateWorking = new System.Globalization.DateTimeFormatInfo();
            dateWorking.ShortDatePattern = "dd/MM/yyyy";
            DateTime LastWorkingDate = Convert.ToDateTime(LastDate, dateWorking);
            //********************************************************************************//
            EmpExit.Last_Working_Date = LastWorkingDate;
            EmpExit.Notice_Period =Convert.ToInt32(Notice_Period);
            EmpExit.Reason_for_Resignation = Reason_for_Resignation;
            EmpExit.Comments = Comments;
            EmpExit.Is_Rehireable =Convert.ToBoolean(Is_Rehireable);
            EmpExit.Supervisor_Comment = Supervisor_Comment;
            EmpExit.Continue_With_Interview_Process =Convert.ToBoolean(Continue_With_Interview_Process);
            EmpExit.Accepted_Agreement = Convert.ToBoolean(Accepted_Agreement);
            EmpExit.Created_By =Convert.ToInt32( Created_By);
            EmpExit.Is_Active = Convert.ToBoolean(Is_Active);
            EmpExit.Is_Deleted = Convert.ToBoolean(Is_Deleted);
           
           UERPManagement.GetInstance.InsertExitDetails(EmpExit);

          

            return string.Format("Thank you ,{0} number of rows inserted!", rowsInserted);
        }
   
    }
}



Thanks Shibashish Mohanty




No comments:

Post a Comment

Please don't spam, spam comments is not allowed here.

ShibashishMnty
shibashish mohanty