Performing CRUD operations in ASP.NET MVC using jqGrid (Explanation with example)

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace jQGridMVC.Controllers
{
    public class JqgridController : Controller
    {
        // GET: Jqgrid
        public ActionResult Index()
        {
            return View();
        }
    }
}

In the above index method, right click & select "Add View" and place the code as below

@{
    ViewBag.Title = "jQGrid List";
}
<link href="~/Content/themes/base/jquery-ui.css" rel="stylesheet" />
<link href="~/Content/jquery.jqGrid/ui.jqgrid.css" rel="stylesheet" />
<h2>jQGrid List</h2>
<div>
    <table id="grid"></table>
    <div id="pager"></div>
</div>  

@section scripts{  
<script src="~/Scripts/jquery-ui-1.10.0.min.js"></script>
    <script src="~/Scripts/i18n/grid.locale-en.js"></script>
    <script src="~/Scripts/jquery.jqGrid.min.js"></script>
    <script src="~/Scripts/script.js"></script>
}

In the above code please check if URL of scripts and CSS matches with your project URL for each file

Step 7: Add code in controller to send data to view as JSON, so now complete code of controller will be as below

using jQGridMVC.Models;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace jQGridMVC.Controllers
{
    public class JqgridController : Controller
    {
        // GET: Jqgrid
        public ActionResult Index()
        {
            return View();
        }

        StudentsEntities db = new StudentsEntities();
        public JsonResult GetValues(string sidx, string sord, int page, int rows) //Gets the todo Lists.  
        {
            int pageIndex = Convert.ToInt32(page) - 1;
            int pageSize = rows;
            var Results = db.StudentTables.Select(
                a => new
                {
                    a.Id,
                    a.Name,
                    a.StudentClass,

                });
            int totalRecords = Results.Count();
            var totalPages = (int)Math.Ceiling((float)totalRecords / (float)rows);
            if (sord.ToUpper() == "DESC")
            {
                Results = Results.OrderByDescending(s => s.Id);
                Results = Results.Skip(pageIndex * pageSize).Take(pageSize);
            }
            else
            {
                Results = Results.OrderBy(s => s.Id);
                Results = Results.Skip(pageIndex * pageSize).Take(pageSize);
            }
            var jsonData = new
            {
                total = totalPages,
                page,
                records = totalRecords,
                rows = Results
            };
            return Json(jsonData, JsonRequestBehavior.AllowGet);
        }



        // TODO:insert a new row to the grid logic here  
        [HttpPost]
        public string Create([Bind(Exclude = "Id")] StudentTable obj)
        {
            string msg;
            try
            {
                if (ModelState.IsValid)
                {
                    db.StudentTables.Add(obj);
                    db.SaveChanges();
                    msg = "Saved Successfully";
                }
                else
                {
                    msg = "Validation data not successfull";
                }
            }
            catch (Exception ex)
            {
                msg = "Error occured:" + ex.Message;
            }
            return msg;
        }
        public string Edit(StudentTable obj)
        {
            string msg;
            try
            {
                if (ModelState.IsValid)
                {
                    db.Entry(obj).State = EntityState.Modified;
                    db.SaveChanges();
                    msg = "Saved Successfully";
                }
                else
                {
                    msg = "Validation data not successfull";
                }
            }
            catch (Exception ex)
            {
                msg = "Error occured:" + ex.Message;
            }
            return msg;
        }
        public string Delete(int Id)
        {
            StudentTable list = db.StudentTables.Find(Id);
            db.StudentTables.Remove(list);
            db.SaveChanges();
            return "Deleted successfully";
        }
    }
}

In the above method, we are

• Using GetValues JSON method we are binding the db data to the jqgrid in the format of JSON (javascript object notation).
• Here parameters sidx means serial index; sort variable for sorting order; page numbers is for pages; and row parameter takes the numbers rows in jqgrid.
• Finally, we are binding data using JSON() method as a JSON object.

Step 8: Create jquery code for JQGrid to load data and perform CRUD operations as below

    $(function()  
      {  
        debugger;  
        $("#grid").jqGrid  
        ({  
            url: "/Jqgrid/GetValues",  
            datatype: 'json',  
            mtype: 'Get',  
            //table header name
            colNames: ['Id', 'Name', 'StudentClass'],  
            //colModel takes the data from controller and binds to grid   
            colModel: [  
              {  
                key: true,  
                hidden: true,  
                name: 'Id',  
                index: 'Id',  
                editable: true  
            }, {  
                key: false,  
                name: 'Name',  
                index: 'Name',  
                editable: true  
            }, {  
                key: false,  
                name: 'StudentClass',  
                index: 'StudentClass',  
                editable: true  
            }
            ],  
      
            pager: jQuery('#pager'),  
            rowNum: 10,  
            rowList: [10, 20, 30, 40],  
            height: '100%',  
            viewrecords: true,  
            caption: 'Jq grid sample Application',  
            emptyrecords: 'No records to display',  
            jsonReader:   
            {  
                root: "rows",  
                page: "page",  
                total: "total",  
                records: "records",  
                repeatitems: false,  
                Id: "0"  
            },  
            autowidth: true,  
            multiselect: false  
                //pager-you have to choose here what icons should appear at the bottom  
                //like edit,create,delete icons  
        }).navGrid('#pager',   
        {  
            edit: true,  
            add: true,  
            del: true,  
            search: false,  
            refresh: true  
        }, {  
            // edit options  
            zIndex: 100,  
            url: '/Jqgrid/Edit',  
            closeOnEscape: true,  
            closeAfterEdit: true,  
            recreateForm: true,  
            afterComplete: function(response) {  
                if (response.responseText) {  
                    alert(response.responseText);  
                }  
            }  
        }, {  
            // add options  
            zIndex: 100,  
            url: "/Jqgrid/Create",  
            closeOnEscape: true,  
            closeAfterAdd: true,  
            afterComplete: function(response) {  
                if (response.responseText) {  
                    alert(response.responseText);  
                }  
            }  
        }, {  
            // delete options  
            zIndex: 100,  
            url: "/Jqgrid/Delete",  
            closeOnEscape: true,  
            closeAfterDelete: true,  
            recreateForm: true,  
            msg: "Are you sure you want to delete this student?",  
            afterComplete: function(response)  
          {  
                if (response.responseText)  
                {  
                    alert(response.responseText);  
                }  
            }  
        });  
    });