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

You can Like also my previous two links

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" %>

.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>

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>

$(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>

<legend>Employee Exit </legend>

Branch :

</div>

<asp:DropDownList ID="ddlBranch" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlBranch_SelectedIndexChanged">

</asp:DropDownList>

</div>

Department :

</div>

<asp:DropDownList ID="ddlDepartment" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddlDepartment_SelectedIndexChanged">

</asp:DropDownList>

</div>

Designation:

</div>

<asp:DropDownList ID="ddlDesignation" runat="server" OnSelectedIndexChanged="ddlDesignation_SelectedIndexChanged"

AutoPostBack="True">

</asp:DropDownList>

</div>

Employee Name :

</div>

<asp:DropDownList ID="ddlEmpName" runat="server" OnSelectedIndexChanged="ddlEmpName_SelectedIndexChanged"

AutoPostBack="True">

</asp:DropDownList>

</div>

Supervisor :

</div>

<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>

 Date of Resignation/Intimation :</div>

<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>

Last Working Date/Left Date :</div>

<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>

Notice Period(In Month) :

</div>

<asp:TextBox ID="txtNoticePeriod" ReadOnly="true" Text="2" runat="server"></asp:TextBox>

</div>

Reason for Resignation :

</div>

<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>

Comments :

</div>

<asp:TextBox ID="txtComment" TextMode="MultiLine" runat="server" Height="56px" Width="330px"></asp:TextBox>

</div>

Is Re-hirable :

</div>

<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>

Supervisors Comments :

</div>

<asp:TextBox ID="txtSupervisorComment" TextMode="MultiLine" runat="server" Height="56px"

Width="330px"></asp:TextBox>

</div>

Continue with Interview Process :

</div>

<asp:CheckBox ID="chkcontinueProcess" runat="server" />

</div>

Terms & Conditions :

</div>

<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>

<asp:CheckBox ID="ChkBoxAcceptAgrrements" Text=" " runat="server" />

</div>

I Accept the Agreement.

</div>

<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">

<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

}

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

}

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

}

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'

)

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

}

Labels

Tuesday, July 17

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

My Store Procedure:-

Thanks Shibashish Mohanty

No comments:

Post a Comment

.