Hitesh Patel: Fully JQuery Crud Operation / Insert ,Update, Delete Demo (Using Ajax And WebService)

============================
.aspx page (design page)
============================

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Employee Master</title>
<script src="Scripts/jquery-1.7.1.min.js"></script>
<link href="font/css/font-awesome.css" rel="stylesheet" type="text/css"></link>

<script type="text/javascript">
$(document).ready(function () {
// Country Combo Fill
GetAjaxcall("ControlMaster.asmx/GetCombo", "{ type: 'CON' }", bindCombo, "Country");
// Sate Combo Fill
GetAjaxcall("ControlMaster.asmx/GetCombo", "{ type: 'STA' }", bindCombo, "State");
// City Combo Fill
GetAjaxcall("ControlMaster.asmx/GetCombo", "{ type: 'CIT' }", bindCombo, "City");
// Employee Reord Fill
GetAjaxcall("ControlMaster.asmx/GetEmployee", "{ PerPage: " + $('#ddlPage').val() + ", searchKey: '" + $('#txtSearch').val() + "' , type: 'EMP' }", bindEmpolyee, "");
// Search Record Get
$("#txtSearch").keyup(function () {
GetAjaxcall("ControlMaster.asmx/GetEmployee", "{ PerPage: " + $('#ddlPage').val() + ", searchKey: '" + $('#txtSearch').val() + "' , type: 'EMP'}", bindEmpolyee, "");
});
// Search Record Pagewise Get
$("#ddlPage").change(function () {
GetAjaxcall("ControlMaster.asmx/GetEmployee", "{ PerPage: " + $('#ddlPage').val() + ", searchKey: '" + $('#txtSearch').val() + "' , type: 'EMP'}", bindEmpolyee, "");
});
// Save Button Click Event
$("#btnSave").click(function () {
var empDetail = {};
empDetail.id = ($('#hdnId').val() == undefined || $('#hdnId').val() == null || $('#hdnId').val() == "") ? 0 : $('#hdnId').val();
empDetail.name = $('#txtName').val();
empDetail.country = $('#ddlCountry').find('option:selected').text();
empDetail.state = $('#ddlState').find('option:selected').text();
empDetail.city = $('#ddlCity').find('option:selected').text();
empDetail.salary = $('#txtSalary').val();
GetAjaxcall("ControlMaster.asmx/IUDEMP", "{ empDetail: " + JSON.stringify(empDetail) + " , type: 'Save' }", "", "");
GetAjaxcall("ControlMaster.asmx/GetEmployee", "{ PerPage: " + $('#ddlPage').val() + ", searchKey: '" + $('#txtSearch').val() + "' , type: 'EMP'}", bindEmpolyee, "");
clearedata();
});
});
function clearedata() {
$('#hdnId').val('');
$('#txtName').val('');
$("#ddlCountry option:first").attr('selected', 'selected');
$("#ddlState option:first").attr('selected', 'selected');
$("#ddlCity option:first").attr('selected', 'selected');
$('#txtSalary').val('');
}
function EditData(id) {
GetAjaxcall("ControlMaster.asmx/GetEmployee", "{ PerPage: " + $('#ddlPage').val() + ", searchKey: '" + id + "', type: 'SNG' }", bindEditEmpolyee, "");
}
function bindEditEmpolyee(data) {
var row = (data.d.lstEmp);
if (row.length > 0) {
$('#hdnId').val(row[0].Id);
$('#txtName').val(row[0].Name);
$("#ddlCountry option:contains(" + row[0].Country + ")").attr("selected", "selected");
$("#ddlState option:contains(" + row[0].State + ")").attr("selected", "selected");
$("#ddlCity option:contains(" + row[0].City + ")").attr("selected", "selected");
$("#txtSalary").val(row[0].Salary);
}
}
function DeleteData(id) {
var ans = confirm("Are You Sure To Delete???");
if (ans) {
var empDetail = {};
empDetail.id = id;
GetAjaxcall("ControlMaster.asmx/IUDEMP", "{ empDetail: " + JSON.stringify(empDetail) + ", type: 'Delete'}", "", "");
}
GetAjaxcall("ControlMaster.asmx/GetEmployee", "{ PerPage: " + $('#ddlPage').val() + ", searchKey: '" + $('#txtSearch').val() + "', type: 'EMP' }", bindEmpolyee, "");
}
// Comman Combo fill function
function bindCombo(data, para) {
var setdata = '';
$('#ddl' + para).html('');
var row = (data.d.lstCombo);
setdata = "<option value='0'> -- Select " + para + "-- </option>";
for (var i = 0 ; i < row.length; i++) {
setdata += "<option value=" + row[i].id + ">" + row[i].name + "</option>";
}
$('#ddl' + para).html(setdata);
}
// Comman Empolyee Reord fill function
function bindEmpolyee(data) {
var setgrid = '';
$("#tblGrid").html('');
var row = (data.d.lstEmp);
if (row.length > 0) {
setgrid = "<table id='example' class='table table-striped table-bordered' cellspacing='0' width='100%'>
<tr>";
setgrid += "<th>NAME <i class='fa fa-sort' ></i></th>";
setgrid += "<th>COUNTRY <i class='fa fa-sort' aria-hidden='true'></i></th>";
setgrid += "<th>STATE <i class='fa fa-sort' aria-hidden='true'></i></th>";
setgrid += "<th>CITY <i class='fa fa-sort' aria-hidden='true'></i></th>";
setgrid += "<th>SALARY <i class='fa fa-sort' aria-hidden='true'></i></th>";
setgrid += "<th colspan='2'>COMMAND</th></tr>
";
for (var i = 0 ; i < row.length; i++) {
setgrid += "<tr><td>" + row[i].Name + "</td><td>" + row[i].Country + "</td><td>" + row[i].State + "</td><td>" + row[i].City + "</td><td>" + row[i].Salary + "</td>";
setgrid += "<td><input type='button' value='Edit' id='btnEdit' onclick='EditData(" + row[i].Id + ");' /></td>";
setgrid += "<td><input type='button' value='Delete' id='btnDelete' onclick='DeleteData(" + row[i].Id + ");' /></td></tr>
";
}
setgrid += "</table>
";
} else { setgrid = 'Record Not Found!'; }
$("#tblGrid").html(setgrid);
}
// Comman Ajax Call Function
function GetAjaxcall(url, data, func, para) {
$.ajax({
type: 'POST',
dataType: 'json',
contentType: 'application/json; charset=utf-8',
async: false,
url: url,
data: data,
success: function (data) {
if (func != "") {
if (para != "")
func(data, para);
else
func(data);
} else {
alert(data.d);
GetAjaxcall("ControlMaster.asmx/GetEmployee", "{ PerPage: " + $('#ddlPage').val() + ", searchKey: '" + $('#txtSearch').val() + "' , type: 'EMP'}", bindEmpolyee, "");
}
},
failure: function (response) {
}
});
}
</script>
</head>
<body>
<form id="form1" runat="server">
<div>
<div id="divform">
<table>
<tr>
<td>Name : </td>
<td>
<input id="txtName" type="text" />
</td>
</tr>
<tr>
<td>Country : </td>
<td>
<select id="ddlCountry">
</select>
</td>
</tr>
<tr>
<td>State : </td>
<td>
<select id="ddlState">
</select>
</td>
</tr>
<tr>
<td>City : </td>
<td>
<%--<input id="txtCity" type="text" />--%>
<select id="ddlCity">
</select>
</td>
</tr>
<tr>
<td>Salary : </td>
<td>
<input id="txtSalary" type="text" />
</td>
</tr>
<tr>
<td colspan="2">
<input id="hdnId" type="hidden" />
<input id="btnSave" type="button" value="Save" />
<input id="btnCancel" onclick="clearedata();" type="button" value="Cancel" />
</td>
</tr>
</table>
<br />
</div>
<div id="divGrig">
<div style="text-align: left;">
Search :
<input id="txtSearch" type="text" />
</div>
<table id="tblGrid">
</table>
<div style="text-align: left;">
<select id="ddlPage">
<option value="1">1</option>
<option value="2">2</option>
<option selected="selected" value="10">10</option>
<option value="15">15</option>
<option value="20">20</option>
<option value="25">25</option>
<option value="20">30</option>
</select>
</div>
</div>
</div>
</form>
</body>
</html>

</div>

===========================
.asmx page (web service)
===========================

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using System.Data.SqlClient;

namespace WebApplication1
{
/// <summary>
/// Summary description for ControlMaster
/// </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 ControlMaster : System.Web.Services.WebService
{
SqlConnection con = new SqlConnection(@"Data Source=PCSUPPLY-PC;Initial Catalog=BillAndEmpMst;User ID=sa;Password=123456");
//SqlConnection con = new SqlConnection(@"Data Source=SANMANSURAT2-PC;Initial Catalog=EmpMst;User ID=sa;Password=123456");

[WebMethod]
public ReturnList GetCombo(string type)
{
ReturnList rl = new ReturnList();
List<comboData> lstCombo = new List<comboData>();

SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
if (con.State == ConnectionState.Broken) { con.Close(); }
if (con.State == ConnectionState.Closed) { con.Open(); }
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_GetData";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@type", type);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
for (int i = 0; i < dt.DefaultView.Count; i++)
{
comboData cd = new comboData();
cd.id = Convert.ToInt32(dt.DefaultView[i]["Id"]);
cd.name = Convert.ToString(dt.DefaultView[i]["Name"]);
lstCombo.Add(cd);
}
rl.msg = "";
rl.lstCombo = lstCombo;
}
catch (Exception)
{
rl.msg = "Data Not Found!";
rl.lstCombo = lstCombo;
throw;
}
finally
{
cmd.Connection = null;
cmd.Parameters.Clear();
cmd.Dispose();
if (con.State != ConnectionState.Closed) { con.Close(); }
}
return rl;
}

[WebMethod]
public ReturnList GetEmployee(int PerPage, string searchKey, string type)
{
ReturnList rl = new ReturnList();
List<EmpDetail> lstEmp = new List<EmpDetail>();

SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
if (con.State == ConnectionState.Broken) { con.Close(); }
if (con.State == ConnectionState.Closed) { con.Open(); }
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_GetData";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@type", type);
cmd.Parameters.AddWithValue("@RecordPerPage", PerPage);
cmd.Parameters.AddWithValue("@SearchField", searchKey);
DataTable dt = new DataTable();
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
//if (searchKey != "")
//dt.DefaultView.RowFilter = "Name Like '%" + searchKey + "%' Or Country Like '%" + searchKey + "%' Or State Like '%" + searchKey + "%' Or City Like '%" + searchKey + "%' Or Salary Like '%" + searchKey + "%'";
for (int i = 0; i < dt.DefaultView.Count; i++)
{
EmpDetail ed = new EmpDetail();
ed.Id = Convert.ToInt32(dt.DefaultView[i]["id"]);
ed.Name = Convert.ToString(dt.DefaultView[i]["name"]);
ed.Country = Convert.ToString(dt.DefaultView[i]["country"]);
ed.State = Convert.ToString(dt.DefaultView[i]["state"]);
ed.City = Convert.ToString(dt.DefaultView[i]["city"]);
ed.Salary = Convert.ToInt32(dt.DefaultView[i]["salary"]);
lstEmp.Add(ed);
}
rl.msg = "";
rl.lstEmp = lstEmp;
}
catch (Exception)
{
rl.msg = "Data Not Found!";
rl.lstEmp = lstEmp;
throw;
}
finally
{
cmd.Connection = null;
cmd.Parameters.Clear();
cmd.Dispose();
if (con.State != ConnectionState.Closed) { con.Close(); }
}
return rl;
}

[WebMethod]
public string IUDEMP(EmpDetail empDetail, string type)
{
string msg = "";
EmpDetail ed = new EmpDetail();
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
if (con.State == ConnectionState.Broken) { con.Close(); }
if (con.State == ConnectionState.Closed) { con.Open(); }
try
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_IUDData";
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@Etype", type);
cmd.Parameters.AddWithValue("@Eid", empDetail.Id);
if (!string.IsNullOrEmpty(empDetail.Name)) cmd.Parameters.AddWithValue("@Ename", empDetail.Name);
if (!string.IsNullOrEmpty(empDetail.Country)) cmd.Parameters.AddWithValue("@Ecountry", empDetail.Country);
if (!string.IsNullOrEmpty(empDetail.State)) cmd.Parameters.AddWithValue("@Estate", empDetail.State);
if (!string.IsNullOrEmpty(empDetail.City)) cmd.Parameters.AddWithValue("@Ecity", empDetail.City);
if (!string.IsNullOrEmpty(Convert.ToString(empDetail.Salary))) cmd.Parameters.AddWithValue("@Esalary", empDetail.Salary);
cmd.ExecuteNonQuery();
msg = "Successfully Opration.";
}
catch (Exception)
{
msg = "";
throw;
}
finally
{
cmd.Connection = null;
cmd.Parameters.Clear();
cmd.Dispose();
if (con.State != ConnectionState.Closed) { con.Close(); }
}
return msg;
}

#region " Class "
public class comboData
{
public int id;
public string name;
}
public class ReturnList
{
public string msg;
public List<EmpDetail> lstEmp;
public List<comboData> lstCombo;
}
public class EmpDetail
{
public int Id;
public string Name, Country, State, City;
public decimal Salary;
}
#endregion
}
}

==========================
Store Procedure
==========================
-----------------------------------------
1.) Insert/Update/Delete (sp_IUDData)
-----------------------------------------

ALTER PROCEDURE [dbo].[sp_IUDData]
@Etype varchar(50) = '',
@Eid int = 0,
@Ename varchar(80) = '',
@Ecountry varchar(80) = '',
@Estate varchar(80) = '',
@Ecity varchar(80) = '',
@Esalary int = 0,
@Edate varchar(50)=''
AS
BEGIN
SET NOCOUNT ON;
if @Etype = 'Insert'
begin
Insert into Emp_Mast(name, country, [state], city, salary, currentdate)
values (@Ename,@Ecountry,@Estate,@Ecity,@Esalary,GETDATE())
end
else if @Etype = 'Update'
begin
Update Emp_Mast set name=@Ename, country=@Ecountry, [state]=@Estate, city=@Ecity,
salary=@Esalary where id=@Eid
end
else if @Etype = 'Delete'
begin
Delete from Emp_Mast where id=@Eid
end
else if @Etype = 'Save'
begin
if Exists(Select 1 from dbo.Emp_Mast Where id=@Eid)
begin
Update Emp_Mast set name=@Ename, country=@Ecountry, [state]=@Estate, city=@Ecity,
salary=@Esalary,currentdate=GETDATE() where id=@Eid
end
else
begin
Insert into Emp_Mast(name, country, [state], city, salary, currentdate)
values (@Ename,@Ecountry,@Estate,@Ecity,@Esalary,GETDATE())
end
end
END

----------------------------------
2). Select/Get Data (sp_GetData)
----------------------------------

ALTER PROCEDURE [dbo].[sp_GetData]
@type varchar(50) = '',
@RecordPerPage INT = 0,
@SearchField varchar(50) = ''
AS
BEGIN
SET NOCOUNT ON;
if @type = 'EMP'
begin
DECLARE @CurrentPage INT SET @CurrentPage = 1
DECLARE @From INT SET @From = (@CurrentPage -1) * @RecordPerPage + 1
DECLARE @TO INT SET @TO = @CurrentPage * @RecordPerPage;

WITH EmpList as (
select ROW_NUMBER() over (order by id ) as [row] , * from Emp_Mast
where id like '%' + @SearchField + '%' Or name like '%' + @SearchField + '%'
Or country like '%' + @SearchField + '%' Or [state] like '%' + @SearchField + '%'
Or city like '%' + @SearchField + '%' Or salary like '%' + @SearchField + '%'
)
select * from EmpList where row between @From And @TO
end
else if @type = 'CON'
begin
Select couid As Id, country As Name from Country_Mast
end
else if @type = 'STA'
begin
Select stateid As Id, state As Name from State_Mast
end
else if @type = 'CIT'
begin
Select cid As Id, city As Name from City_Mast
end
END

Hitesh Patel

Friday, July 8, 2016

Fully JQuery Crud Operation / Insert ,Update, Delete Demo (Using Ajax And WebService)

No comments:

Post a Comment

Demo for Repository Pattern in ASP.Net

Search This Blog