Friday, August 1, 2014

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,
addto show the add button,
delto show the delete button,
searchto show the search button,
searchtext: search button text,
addtextAdd  button text,
edittextedit button text,
deltextdelete 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.




15 comments:

  1. nice code ,i want source code ,my email id is mvpatel.patel7@gmail.com
    thanks in advance.

    ReplyDelete
    Replies
    1. Thnx ,In the above post you can find the source code.

      Delete
  2. This comment has been removed by the author.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. sir,pls help me to do inline editing. I am trying to find a solution for the past one week.any help will be really helpful.....

    ReplyDelete
  5. nice code, but have one doubt where Package Newtonsoft.Json is third parties tool or not?

    please make me clear

    ReplyDelete
  6. nice code, but have one doubt whether Package Newtonsoft.Json is third parties tool or not?

    please make me clear

    ReplyDelete
  7. Thanx a lot sir....!!!

    ReplyDelete
  8. Thanks a lot for helping with this code...I was badly looking for this one and implemented in my code..Its working fine..

    ReplyDelete
  9. Hi Kumar,

    I've successfully loaded the data in to JQgrid (it's having inline navigattion). Now, i want to save the data (add, edit, delete) into DB.
    I'm using VS2010, ASP.net Webforms.
    i've written functions as webmethods in aspx.cs file but unable make it as work. Could you please provide a sample for the same.

    ReplyDelete
    Replies
    1. Dear Naresh,
      Kindly view the post that has been posted after this post .Post Title is "Add edit delete in Jqgrid in asp.net" .Hope it will solve your problem.

      Delete
  10. Mr Manoj your demo was most helpful, i just can not begin to count all the troubles you have saved me from. You are certainly the bomb and God bless you richly for your effort.

    ReplyDelete
  11. When someone writes an piece of writing he/she retains the thought of a user in his/her brain that how a user can understand it. Therefore that's why this paragraph is perfect. Thanks!

    ReplyDelete
  12. I don't even know how I ended up here, but I thought this post was great. I don't know who you are but definitely you're going to a famous blogger if you are not already ;) Cheers!

    ReplyDelete
  13. Pretty! This has been a really wonderful article. Many thanks for providing this info.

    ReplyDelete

Thanks for your valuable comments

Convert Html to Pdf in azure function and save in blob container

 In this post  I am going to create an azure function ( httpTrigger ) and send html content  which will be converted into PDF and save in bl...