Aug 26, 2012

Add, Edit, Delete In jqGrid With ASP.NET Web API

This post explains how to implement a simple CRUD (Create, Read, Update, and Delete) in jqGrid with ASP.NET MVC 4 Web API. If you are beginner to Web API, read this post to create a web API that supports CRUD operations. We will use GET, POST, PUT and DELETE HTTP methods for Select, Insert, Update and Delete operations respectively.

Model:

I am using same model and repository structure as in this post:


 public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Category { get; set; }
        public decimal Price { get; set; }
    }

Repository Interface:


 interface IProductRepository
    {
        IEnumerable<Product> GetAll();
        Product Get(int id);
        Product Add(Product item);
        void Remove(int id);
        bool Update(Product item);
    }

Repository:


 public class ProductRepository : IProductRepository
    {
        private List<Product> products = new List<Product>();
        private int _nextId = 1;

        public ProductRepository()
        {
            Add(new Product { Name = "Tomato soup", Category = "Groceries", Price = 1.39M });
            Add(new Product { Name = "Yo-yo", Category = "Toys", Price = 3.75M });
            Add(new Product { Name = "Hammer", Category = "Hardware", Price = 16.99M });
            Add(new Product { Name = "Network Cards", Category = "Electronics", Price = 6.59M });
            Add(new Product { Name = "Spotting Scopes", Category = "Optics", Price = 25.99M });
            Add(new Product { Name = "Biometric Monitors", Category = "Health Care", Price = 100.0M });
            Add(new Product { Name = "Perfume", Category = "Cosmetics", Price = 10.99M });
            Add(new Product { Name = "Hair Coloring", Category = "Personal Care", Price = 16.99M });
        }

        public IEnumerable<Product> GetAll()
        {
            return products;
        }

        public Product Get(int id)
        {
            return products.Find(p => p.Id == id);
        }

        public Product Add(Product item)
        {
            item.Id = _nextId++;
            products.Add(item);
            return item;
        }

        public void Remove(int id)
        {
            products.RemoveAll(p => p.Id == id);
        }

        public bool Update(Product item)
        {
            int index = products.FindIndex(p => p.Id == item.Id);
            if (index == -1)
            {
                return false;
            }
            products.RemoveAt(index);
            products.Add(item);
            return true;
        }
    }

Web API Controller:

Add an empty API controller in your project and add a field that holds an IProductRepository instance:


public class ProductsController : ApiController
{
    static readonly IProductRepository repository = new ProductRepository();
}

let’s take a look at the JSON format expected by the grid:


{ 
  total: "xxx", 
  page: "yyy", 
  records: "zzz",
  rows : [
    {id:"1", cell:["cell11", "cell12", "cell13"]},
    {id:"2", cell:["cell21", "cell22", "cell23"]},
      ...
  ]
}

To display product list in jqGrid, we implement the above format. See following method:


  public dynamic GetProducts(string sidx, string sord, int page, int rows)
        {
            var products = repository.GetAll() as IEnumerable<Product>;
            var pageIndex = Convert.ToInt32(page) - 1;
            var pageSize = rows;
            var totalRecords = products.Count();
            var totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);
            products = products.Skip(pageIndex * pageSize).Take(pageSize);
            return new
            {
                total = totalPages,
                page = page,
                records = totalRecords,
                rows = (
                    from product in products
                    select new
                    {
                        i = product.Id.ToString(),
                        cell = new string[] {
                           product.Id.ToString(),
                           product.Name,
                           product.Category,
                           product.Price.ToString()  
                        }
                    }).ToArray()
            };
        }

To add new product, HTTP POST request is used:


 public HttpResponseMessage PostProduct(Product item)
        {
            item = repository.Add(item);
            var response = Request.CreateResponse<Product>(HttpStatusCode.Created, item);
            string uri = Url.Link("DefaultApi", new { id = item.Id });
            response.Headers.Location = new Uri(uri);
            return response;
        }

To update existing product, HTTP PUT is used:


 public void PutProduct(int id, Product item)
        {
            item.Id = id;
            if (!repository.Update(item))
            {
                throw new HttpResponseException(new HttpResponseMessage(HttpStatusCode.NotFound));
            }
        }

To delete a product:


		public HttpResponseMessage DeleteProduct(int id)
        {
            repository.Remove(id);
            return new HttpResponseMessage(HttpStatusCode.NoContent);
        }

View:

For simplicity, We are going to use same app to consume Web API.

1. First we include jqGrid library in our project. To install jQuery.jqGrid from NuGet, run the following command in the Package Manager Console

Install-Package jQuery.jqGrid

2. include jQuery UI and jqGrid css files


 @Styles.Render("~/Content/themes/base/css", "~/Content/css") 
 <link href="~/Content/jquery.jqGrid/ui.jqgrid.css" rel="stylesheet" type="text/css" />

OR include ui.jqgrid.css in existing bundle.

3. include javascript files:


    <script src="~/Scripts/jquery-1.6.2.js" type="text/javascript"></script>
    <script src="~/Scripts/jquery-ui-1.8.23.js" type="text/javascript"></script>
    <script src="~/Scripts/i18n/grid.locale-en.js" type="text/javascript"></script>
    <script src="~/Scripts/jquery.jqGrid.min.js" type="text/javascript"></script>

OR

you can add grid.locale-en.js and jquery.jqGrid.min.js in the bundle and use it. Make sure, the sequence must be same.

HTML Structure:

4. You have to add HTML table for jqGrid and a div for pager in body.


	<table id="gridMain">
        </table>
        <div id="pagernav">
        </div>

That's it.

5. To create basic jqGrid structure:



 var API_URL = "api/products/";
 jQuery("#gridMain").jqGrid({
            url: API_URL,
            datatype: 'json',
            mtype: 'GET',
            pager: '#pagernav',
            sortable: true,
            height: 200,
            viewrecords: true,
            colNames: ['Id', 'Name', 'Category', 'Price'],
            colModel: [{ name: 'Id', index: 'Id', width: 40, sorttype: "int" },
             { name: 'Name', index: 'Name', editable: true, edittype: 'text', width: 70 },
             { name: 'Category', index: 'Category', editable: true, edittype: 'text', width: 70 },
             { name: 'Price', index: 'Price', editable: true, edittype: 'text', width: 50, align: "right", sorttype: "float", formatter: "number" }
            ],
            caption: "CRUD With ASP.NET Web API",
            autowidth: true
            
        });

6. By default, jqGrid uses POST HTTP method for adding and editing record. But we have to use different HTTP methods for different operations. It's the most tricky part of this post. I created a method takes HTTP Method type and return parameter for editing.


 function updateDialog(action) {
            return {
                url: API_URL
                , closeAfterAdd: true
                , closeAfterEdit: true
                , afterShowForm: function (formId) { }
                , modal: true
                , onclickSubmit: function (params) {
                    var list = $("#gridMain");
                    var selectedRow = list.getGridParam("selrow");
                    rowData = list.getRowData(selectedRow);
                    params.url += rowData.Id;
                    params.mtype = action;
                }
                , width: "300"
            };
        }


  jQuery("#gridMain").jqGrid('navGrid', '#pagernav',
        { add: true, edit: true, del: true },
        updateDialog('PUT'),
        updateDialog('POST'),
        updateDialog('DELETE')
);		
		
jqgrid crud

Now, In navigation bar, click on add, edit or delete icon to insert, update or delete product respectively.

Hope, It helps. Share your opinion, suggestion or queries in comment box.