Add edit delete in Jqgrid in asp.net

In the previous post ,we learnt how to bind data in jqGrid in asp.net.Now We are going to learn how to add ,edit and delete records in Jqgrid.
For this I am using same table and procedure from my previous post,you can check previous post.

$('#list').jqGrid('navGrid', '#pager',

{

edit: true,

add: true,

del: true,

search: false,

searchtext: "Search",

addtext: "Add",

edittext: "Edit",

deltext: "Delete",

cloneToTop: true

}

//For EDIT

, { recreateForm: true,

reloadAfterSubmit:false,

width: 500,

closeAfterEdit: true,

ajaxEditOptions: { contentType: "application/json" },

serializeEditData: function (postData) {

var postdata = { 'data': postData };

return JSON.stringify(postdata);

}

//for Add {

recreateForm: true,

beforeShowForm: function (form) {

$('#tr_employeeId', form).hide();

width: 500,

reloadAfterSubmit: false,

closeAfterAdd: false,

ajaxEditOptions: { contentType: "application/json" },

serializeEditData: function (postData) {

var postdata = { 'data': postData };

return JSON.stringify(postdata);

}

//for Delete

{

ajaxDelOptions: { contentType: "application/json" },

reloadAfterSubmit: false,

onclickSubmit: function (eparams) {

var retarr = {};

var sr = $("#list").getGridParam('selrow');

rowdata = $("#list").getRowData(sr);

retarr = { employeeId: rowdata.employeeId };

return retarr;

serializeDelData: function (data) {

var postData = { 'data': data };

return JSON.stringify(postData);

}

});

Define edit url like :

editurl: 'jqgridexample.aspx/EditUpdateDel'

create webmethod named :EditUpdateDel as

public static string EditUpdateDel(object data)

{

bl businessL = new bl();

string returnMessage = "";

returnMessage = businessL.insUpdete(data);

return returnMessage;

}

create class bl.cs ,add the below code:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.Data.SqlClient;

namespace calendarcoloring

{

public class bl

{

SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);

SqlCommand cmd = new SqlCommand();

public string insUpdete(object param)

{

string returnvalue = "";

try

{

Dictionary<string, object> editData = param as Dictionary<string, object>;

cmd.CommandText = "insUpdDelete";

cmd.CommandType = CommandType.StoredProcedure;

if (editData["oper"].ToString() == "del")

{

cmd.Parameters.AddWithValue("@empId", editData["employeeId"] == DBNull.Value ? 0 : editData["employeeId"]);

cmd.Parameters.AddWithValue("@Actiontype", editData["oper"].ToString());

}

else

{

cmd.Parameters.AddWithValue("@empId", editData["employeeId"] == DBNull.Value ? 0 : editData["employeeId"]);

cmd.Parameters.AddWithValue("@name", editData["name"].ToString());

cmd.Parameters.AddWithValue("@Email", editData["email"].ToString());

cmd.Parameters.AddWithValue("@pwd", editData["pwd"].ToString());

cmd.Parameters.AddWithValue("@phone", editData["phone"].ToString());

cmd.Parameters.AddWithValue("@nationality", Convert.ToInt32(editData["nationalId"]));

cmd.Parameters.AddWithValue("@dateofbirth", editData["dateOfBirth"].ToString());

cmd.Parameters.AddWithValue("@Actiontype", editData["oper"].ToString());

}

cmd.Connection = con;

con.Open();

int m = cmd.ExecuteNonQuery();

if (m > 0)

{

if (editData["oper"].ToString() == "add")

{

returnvalue = "Record Added successfully";

}

else if (editData["oper"].ToString() == "edit")

{

returnvalue = "Record Updated successfully";

}

else if (editData["oper"].ToString() == "del")

{

returnvalue = "Record deleted successfully";

}

else

{

throw new Exception("there is problem");

}

catch (Exception ex)

{

returnvalue = ex.Message.ToString();

}

finally

{

con.Close();

}

return returnvalue;

}

CREATE Proc as

CREATE proc [dbo].[insUpdDelete]

@empId int,

@name nvarchar(200)=null,

@Email nvarchar(200)=null,

@pwd nvarchar(100)=null,

@phone nvarchar(200)=null,

@nationality int=null,

@dateofbirth nvarchar(20)=null,

@Actiontype nvarchar(20)

begin

if(@Actiontype='add')

begin

insert into employeeTable (name,email,pwd,phone,nationalityId,dateOfBirth)

values (@name,@Email,@pwd,@phone,@nationality,convert(date,@dateofbirth,103))

end

else if(@Actiontype='edit')

begin

update employeeTable set name=@name,email=@email,

pwd=@pwd,nationalityId=@nationality,

dateOfBirth=convert(date,@dateofbirth,103)

where employeeId=@empId

end

else

begin

delete from employeeTable where employeeId=@empId

end

Thats it,now you can add,edit,and delete records using jqGrid.

Complete code:

aspx page

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="jqgridexample.aspx.cs"

Inherits="calendarcoloring.jqgridexample" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head>

<body>

<div>

</div>

</div>

</form>

$(document).ready(function () {

function getCountry() {

var country;

$.ajax({

type: "POST",

contentType: "application/json",

data: "{}",

async: false,

url: "jqgridexample.aspx/getNational",

dataType: "json",

success: function (data) {

country = data.d;

error: function (XMLHttpRequest, textStatus, errorThrown) {

debugger;

}

});

return country;

}

$.ajax({

type: "POST",

contentType: "application/json",

data: "{}",

url: "jqgridexample.aspx/getEmployee",

dataType: "json",

success: function (data) {

data = data.d;

$("#list").jqGrid({

datatype: "local",

colNames: ['Employee Id', 'Name', 'Email', 'Phone', 'Password', 'Nationality', 'Date of Birth'],

colModel: [

{ name: 'employeeId', index: 'employeeId', width: 55, stype: 'text', sortable: true, editable: true, formoptions: { rowpos: 1, colpos: 1 }, editrules: { integer: true} },

{ name: 'name', index: 'name', width: 90, stype: 'text', sortable: true, editable: true, editrules: { required: true }, formoptions: { rowpos: 2, colpos: 1} },

{ name: 'email', index: 'email', width: 100, stype: 'text', sortable: true, editable: true, editrules: { email: true, required: true }, formoptions: { rowpos: 2, colpos: 2} },

{ name: 'phone', index: 'phone', width: 80, align: "right", stype: 'text', sortable: true, editable: true, formoptions: { rowpos: 3, colpos: 1} },

{ name: 'pwd', index: 'pwd', width: 80, align: "right", stype: 'text', edittype: 'password', sortable: true, editable: true, formoptions: { rowpos: 3, colpos: 2} },

{ name: 'nationalId', index: 'nationalId', width: 80, align: "right", formatter: 'select',stype: 'select',

editable: true, edittype: "select", editoptions: { value: getCountry() }, formoptions: { rowpos: 4, colpos: 1 }

{ name: 'dateOfBirth', index: 'dateOfBirth', width: 80, align: "right",

edittype: 'text', editable: true,

editoptions: { dataInit: function (el)

{ $(el).datepicker({

dateFormat: "dd/mm/yy",

changeMonth: true,

changeYear: true, }); } },

formoptions: { rowpos: 4, colpos: 2 }

}

data: JSON.parse(data),

rowno: 10,

loadonce:true,

/* multiselect:true,*/

rowlist: [5, 10, 20],

pager: '#pager',

viewrecords: true,

gridview: true,

sortorder: 'asc',

toppager: true,

cloneToTop: true,

altrows: true,

autowidth: false,

hoverrows: true,

height:300,

/* toolbar: [true, "top"],*/

rownumbers: true,

caption: "Employee Data",

editurl: 'jqgridexample.aspx/EditUpdateDel',

/* ondblClickRow: function(rowid) {

$(this).jqGrid('editGridRow', rowid,

{recreateForm:true,closeAfterEdit:true,

closeOnEscape:true,reloadAfterSubmit:false, width: 500});

}*/

});

$('#list').jqGrid('navGrid', '#pager',

{

edit: true,

add: true,

del: true,

search: false,

searchtext: "Search",

addtext: "Add",

edittext: "Edit",

deltext: "Delete",

cloneToTop: true

}

, { recreateForm: true,

reloadAfterSubmit:false,

width: 500,

closeAfterEdit: true,

ajaxEditOptions: { contentType: "application/json" },

serializeEditData: function (postData) {

var postdata = { 'data': postData };

return JSON.stringify(postdata);

}

{

recreateForm: true,

beforeShowForm: function (form) {

$('#tr_employeeId', form).hide();

width: 500,

reloadAfterSubmit: false,

closeAfterAdd: false,

ajaxEditOptions: { contentType: "application/json" },

serializeEditData: function (postData) {

var postdata = { 'data': postData };

return JSON.stringify(postdata);

}

{

ajaxDelOptions: { contentType: "application/json" },

reloadAfterSubmit: false,

onclickSubmit: function (eparams) {

var retarr = {};

var sr = $("#list").getGridParam('selrow');

rowdata = $("#list").getRowData(sr);

retarr = { employeeId: rowdata.employeeId };

return retarr;

serializeDelData: function (data) {

var postData = { 'data': data };

return JSON.stringify(postData);

}

});

$("#list").jqGrid('filterToolbar', { stringResult: true, searchOnEnter: true });

var grid = $("#list");

var topPagerDiv = $('#' + grid[0].id + '_toppager')[0]; // "#list_toppager"

/* $("#edit_" + grid[0].id + "_top", topPagerDiv).remove(); // "#edit_list_top"

$("#del_" + grid[0].id + "_top", topPagerDiv).remove(); // "#del_list_top"

$("#search_" + grid[0].id + "_top", topPagerDiv).remove(); // "#search_list_top"

$("#refresh_" + grid[0].id + "_top", topPagerDiv).remove(); // "#refresh_list_top"*/

$("#" + grid[0].id + "_toppager_center", topPagerDiv).remove(); // "#list_toppager_center"

$(".ui-paging-info", topPagerDiv).remove();

var bottomPagerDiv = $("div#pager")[0];

$("#add_" + grid[0].id, bottomPagerDiv).remove();

$("#edit_" + grid[0].id, bottomPagerDiv).remove();

$("#del_" + grid[0].id, bottomPagerDiv).remove();

$("#refresh_" + grid[0].id, bottomPagerDiv).remove();

// "#add_list"

error: function (XMLHttpRequest, textStatus, errorThrown) {

debugger;

}

});

</script>

</body>

</html>

.cs Page:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using Newtonsoft;

using System.Web.Services;

using System.Web.Script.Serialization;

using System.Data;

using System.Data.SqlClient;

using System.Web.Script.Services;

namespace calendarcoloring

{

public partial class jqgridexample : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

}

[WebMethod]

public static string getEmployee()

{

SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "fetchemployeeData";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = con;

con.Open();

SqlDataAdapter da = new SqlDataAdapter(cmd);

con.Close();

DataSet ds = new DataSet();

da.Fill(ds);

return Newtonsoft.Json.JsonConvert.SerializeObject(ds.Tables[0]);

}

[WebMethod]

public static string getNational()

{ string returnvalue="";

SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "fetchNationality";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = con;

con.Open();

SqlDataAdapter da = new SqlDataAdapter(cmd);

con.Close();

DataSet ds = new DataSet();

da.Fill(ds);

returnvalue+=""+""+""+":All;";

for (var i = 0; i < ds.Tables[0].Rows.Count; i++) {

returnvalue += ds.Tables[0].Rows[i]["nationalId"].ToString().Trim() + ":" + ds.Tables[0].Rows[i]["natiionName"].ToString().Trim() + ";";

}

returnvalue = returnvalue.Remove(returnvalue.LastIndexOf(';'));

return returnvalue;

}

[WebMethod, ScriptMethod(ResponseFormat = ResponseFormat.Json)]

public static string EditUpdateDel(object data)

{

bl businessL = new bl();

string returnMessage = "";

returnMessage = businessL.insUpdete(data);

return returnMessage;

}

blclass: