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.


In next post will see how to add ,edit delete records.

Happy coding.