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)

as 

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

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

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title></title>

    <script src="JQGridReq/jquery-1.9.0.min.js" type="text/javascript"></script>

    <link rel="stylesheet" href="http://code.jquery.com/ui/1.10.3/themes/redmond/jquery-ui.css" />

    <script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>

    <script src="JQGridReq/grid.locale-en.js" type="text/javascript"></script>

    <script src="JQGridReq/jquery.jqGrid.js" type="text/javascript"></script>

    <link href="JQGridReq/ui.jqgrid.css" rel="stylesheet" type="text/css" />

    <script src="Scripts/json2.js" type="text/javascript"></script>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <div id="topList">

        </div>

        <table id="list"></table> <%--for grid--%>

        <div id="pager"> </div> <%--for paging--%>

    </div>

    </form>

    <script type="text/javascript">

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

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;

        }

    }

}

And our output will be

add in jqGrid

edit in jqGrid

Delete record in jqgGrid

                

jqGrid with Add,Edit and Delete options in asp.net

jqGrid is a powerful plugin to display data as grid layout and Add,edit ,delete records.I had some free time so I thought to explore some features of jqGrid.

Our final Output will be :

Ok,Lets assume We have to show employee data in jqGrid  using webmethod from database (sql server)

First create a employee table :

 Table :employeeTable

Column Name	Data Type
employeeId	int
name	nvarchar(500)
email	nvarchar(200)
pwd	nvarchar(8)
phone	nchar(10)
nationalityId	int
dateOfBirth	date

Another table is :nationalTable

Column Name	Data Type
nationalId	int
natiionName	nvarchar(200)

Now create a procedure to get data from database to show the employee records:

CREATE PROC [dbo].[fetchemployeeData]

AS

 SELECT employeeId,name,email,pwd,phone                                  ,B.nationalId,CONVERT(VARCHAR(10),

         dateOfBirth,103)AS dateOfBirth,B.natiionName  FROM       employeeTable

  A JOIN nationalTable B ON A.nationalityId=B.nationalId  ORDER BY name 

Now lets work on jqGrid.

On the aspx page first add the jqGrid references as below inside the head tag:

<script src="JQGridReq/jquery-1.9.0.min.js" type="text/javascript"></script>

<link rel="stylesheet" href="http://code.jquery.com/ui/1.10.3/themes/redmond/jquery-ui.css" />

    <script src="http://code.jquery.com/ui/1.10.3/jquery-ui.js"></script>

    <script src="JQGridReq/grid.locale-en.js" type="text/javascript"></script>

    <script src="JQGridReq/jquery.jqGrid.js" type="text/javascript"></script>

    <link href="JQGridReq/ui.jqgrid.css" rel="stylesheet" type="text/css" />

    <script src="Scripts/json2.js" type="text/javascript"></script>

Inside the form take table and div as below:

<table id="list"></table> <%--for grid--%>

<div id="pager"> </div> <%--for paging--%>


Now In the script block copy paste the below code

<script type="text/javascript">

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

                        rownumbers: true,

                        caption: "Employee Data"                           

                    });

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

                   {

                       edit: true,

                       add: true,

                       del: true,

                       search: false,

                       searchtext: "Search",

                       addtext: "Add",

                       edittext: "Edit",

                       deltext: "Delete",

                       cloneToTop: true

                         });

                        

/* to put search option in column toolbar*/

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

/* move add ,edit ,delete button on top */

                    var grid = $("#list");

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

                    $("#" + grid[0].id + "_toppager_center", topPagerDiv).remove();                         $(".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>

In the above grid national Names are in dropdown list ,so to fill the dropdown list dynamically use the below 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;

            }

Now time to create web method.

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

        }

Don't forget to add newtonsoft ref to serialize your data in json format. To add newtonsoft dll follow the below steps
GOTO-Tools->Library Package manager->Package manager console
PM> Install-Package Newtonsoft.Json
Here are some properties of jqGrid:
data: set the data to bind the grid
colNames:   defines the columns Name as Header
colModel:   bind column value in rows
stype:      defines the search type (text or select)
sortable:   It can be sort or not
editable:   make it editable
edittypes:  types of control at the time of editing(textbox,select,checkbox etc)
editrules:  some validations and datatypes can be defined 
editoptions: to bind the dynamic data inside the controls
formoptions : defines form layout at the time of generating form for edit,add

edit: to show the edit button,

add: to show the add button,

del: to show the delete button,

search: to show the search button,

searchtext: search button text,

addtext: Add  button text,

edittext: edit button text,

deltext: delete button text,
pager: div id to set the paging
multiselect: for select multiple rows
hoverows: shows tooltip on mouse hover
rownumbers :shows the row number

Any confusion contact to mkumar.soft@gmail.com.