DotNetOcean: How to Select, Update, Insert Data in database Using Json, Jquery and Webservices

Introduction:
In this article i have explained all database operation using ajax.
Description:
In my Previous Post i have described how to insert data using Both normal and n-tier pattern.Now I am going to explain how to retrieve data from database and bind with the respective controls for Updating that data in database using json and webservice.
Previously i have shown the exact scenario,so i am directly implementing the code without describing the database design etc.

Here is my Source code of usercontrol(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" %>

type="text/javascript"></script>

.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">Select</asp:ListItem>

<asp:ListItem Value="5">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" />

<asp:Button ID="BtnUpdate" runat="server" Text="Save" />

</div>

</fieldset>

</div>

$(document).ready(function () {

$('#<%= ddlEmpName.ClientID %>').change(function () {

var Employee_ID = $('#<%=ddlEmpName.ClientID %>').val();

var data1 = JSON.stringify({ Employee_ID: Employee_ID });

$.ajax({

type: "POST",

contentType: "application/json; charset=utf-8",

url: "../../App_WebServices/UHRMS_EmployeeExit.asmx/SelectWebService",

data: data1,

contentType: "application/json",

async: false,

success: function (data) {

//########################@if your database contains datetime format then you have convert it like any of the following pattern@#####################################

////// var date = eval(data.Data.Entity.Slrq.replace(/\/Date$(\d )$\//gi, "new Date($1)"));

////// alert(date.format("yyyy-MM-dd HH:mm:ss"));

////// alert(dateFormat(date, "yyyy-MM-dd HH:mm:ss"));

//if your parameter contains datetime format you can convert like bellow

//#############################################################

// var d = eval(data.d[0].Last_Working_Date.slice(1, -1))

//#############################################################

// alert(dateFormat(d, "yyyy-MM-dd HH:mm:ss"));

// var jsonDate = data.d[0].Last_Working_Date; // returns "/Date(1245398693390)/";

// var re = /-?\d+/;

// var m = re.exec(jsonDate);

// var d = new Date(parseInt(m[0]));

//#############################################################

// var d = new Date();

// d.setTime(data.d[0].Last_Working_Date);

//#############################################################

$('#<%= txtIntimationDate.ClientID %>').val(data.d[0].Date_of_Resignation1);

$('#<%=txtLastWorkingDate.ClientID %>').val(data.d[0].Last_Working_Date1);

$('#<%=txtNoticePeriod.ClientID %>').val(data.d[0].Notice_Period);

$('#<%=ddlResignationReason.ClientID %>').val(data.d[0].Reason_for_Resignation);

$('#<%=txtComment.ClientID %>').val(data.d[0].Comments);

if (data.d[0].Is_Rehireable == true) {

var value = "1";

$('#<%=RdBtnLstReHirable.ClientID %>').find("input[value=" + value + "]").attr("checked", "checked");

//#############################################################

// $('#<%=RdBtnLstReHirable.ClientID %> input[type=radio]').filter(function (btn) { return btn.value == value; }).attr('checked', true);

//#############################################################

}

else {

var value = "0";

$('#<%=RdBtnLstReHirable.ClientID %>').find("input[value=" + value + "]").attr("checked", "checked");

}

$('#<%=txtSupervisorComment.ClientID %>').val(data.d[0].Supervisor_Comment);

if (data.d[0].Continue_With_Interview_Process == true) {

$('#<%=chkcontinueProcess.ClientID %>').attr('checked', 'checked');

}

else {

$('#<%=chkcontinueProcess.ClientID %>').attr('checked', 'unchecked');

}

if (data.d[0].Accepted_Agreement == true) {

$('#<%=ChkBoxAcceptAgrrements.ClientID %>').attr('checked', 'checked');

}

else {

$('#<%=ChkBoxAcceptAgrrements.ClientID %>').attr('checked', 'unchecked');

}

});

</script>

<%--Comment for Update--%>

$(document).ready(function () {

$('#<%= BtnUpdate.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 Modified_By = 3;

var Is_Active = true;

var Is_Deleted = false;

$.ajax({

type: "POST",

url: "../../App_WebServices/UHRMS_EmployeeExit.asmx/UpdateWebService",

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 + "', 'Modified_By': '" + Modified_By + "', 'Is_Active': '" + Is_Active + "', 'Is_Deleted': '" + Is_Deleted + "'}",

contentType: "application/json",

async: false,

success: function (data) {

alert(data.d);

}

});

</script>

Here is my code behind of usercontrol(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)

{

ScriptManager.GetCurrent(this.Parent.Page).RegisterPostBackControl(ddlEmpName);

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

}

Here is my code of Webservice(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;

using System.Web.UI.WebControls;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.IO;

using System.Text.RegularExpressions;

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

}

//************ To Insert Employee Exit Details************//

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

}

//************ To Update Employee Exit Details************//

[WebMethod]

public string UpdateWebService(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 Modified_By, string Is_Active, string Is_Deleted)

{

UHRMS_EmployeeExitObject EmpExit = new UHRMS_EmployeeExitObject();

EmpExit.Operation = "UpdateEmployeeExit";

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.Modified_By = Convert.ToInt32(Modified_By);

EmpExit.Is_Active = Convert.ToBoolean(Is_Active);

EmpExit.Is_Deleted = Convert.ToBoolean(Is_Deleted);

UERPManagement.GetInstance.UpdateExitDetails(EmpExit);

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

}

//************ To Select Employee Exit Details************//

[WebMethod]

public List<UHRMS_EmployeeExitObject> SelectWebService(string Employee_ID)

{

List<UHRMS_EmployeeExitObject> EmpExit = new List<UHRMS_EmployeeExitObject>();

UHRMS_EmployeeExitObject EmpExitDetails = new UHRMS_EmployeeExitObject();

EmpExitDetails.Operation = "SelectEmployeeExit";

EmpExitDetails.Employee_ID = Convert.ToInt32(Employee_ID);

EmpExit = UERPManagement.GetInstance.GetEmployeeExitByEmployee(EmpExitDetails);

ListItem li = new ListItem();

var lnq = from emp in EmpExit

where emp.Employee_ID == EmpExitDetails.Employee_ID

select new { Date_of_Resignation1 = emp.Date_of_Resignation1, Last_Working_Date1 = emp.Last_Working_Date1, Notice_Period = emp.Notice_Period, Reason_for_Resignation = emp.Reason_for_Resignation, Comments = emp.Comments, Is_Rehireable = emp.Is_Rehireable, Supervisor_Comment = emp.Supervisor_Comment, Continue_With_Interview_Process = emp.Continue_With_Interview_Process, emp.Accepted_Agreement };

List<UHRMS_EmployeeExitObject> second = new List<UHRMS_EmployeeExitObject>();

for (int i = 0; i < lnq.Count(); i++)

{

UHRMS_EmployeeExitObject Obj = new UHRMS_EmployeeExitObject();

Obj.Date_of_Resignation1 = lnq.ToList()[i].Date_of_Resignation1;

Obj.Last_Working_Date1 = lnq.ToList()[i].Last_Working_Date1;

Obj.Notice_Period = lnq.ToList()[i].Notice_Period;

Obj.Reason_for_Resignation = lnq.ToList()[i].Reason_for_Resignation;

Obj.Comments = lnq.ToList()[i].Comments;

Obj.Is_Rehireable = Convert.ToBoolean(lnq.ToList()[i].Is_Rehireable.ToString());

Obj.Supervisor_Comment = lnq.ToList()[i].Supervisor_Comment;

Obj.Continue_With_Interview_Process = lnq.ToList()[i].Continue_With_Interview_Process;

Obj.Accepted_Agreement = lnq.ToList()[i].Accepted_Agreement;

second.Add(Obj);

}

return second.ToList();

}

[WebMethod]

public string GetControlHtml(string controlLocation)

{

// string co

Page page = new Page();

UserControl userControl = (UserControl)page.LoadControl(controlLocation);

HtmlForm form = new HtmlForm();

form.Controls.Add(userControl);

page.Controls.Add(form);

StringWriter textWriter = new StringWriter();

HttpContext.Current.Server.Execute(page, textWriter, false);

string newVal = CleanHtmlNew(textWriter.ToString());

return newVal;

}

private string CleanHtmlNew(string html)

{

string value = Regex.Replace(html, @"<[/]?(form)[^>]*?>", "", RegexOptions.IgnoreCase);

return value;

}

[WebMethod]

public static string Result(string controlName)

{

return RenderControl(controlName);

}

public static string RenderControl(string controlName)

{

Page page = new Page();

UserControl userControl = (UserControl)page.LoadControl(controlName);

userControl.EnableViewState = false;

HtmlForm form = new HtmlForm();

form.Controls.Add(userControl);

page.Controls.Add(form);

StringWriter textWriter = new StringWriter();

HttpContext.Current.Server.Execute(page, textWriter, false);

return textWriter.ToString();

}

Here is my Web.Config ConnectionString :-

</connectionStrings>

Labels

Friday, July 20

How to Select, Update, Insert Data in database Using Json, Jquery and Webservices

Thanks Shibashish Mohanty

No comments:

Post a Comment

.