jqGrid and ASP.NET MVC - CRUD

As promised, it's time to go ahead with a CRUD scenario for jqGrid.
Of course, we need to do some configuration first. We must define which columns are editable. For ones we will set type of edit field (through edittype parameter). We can also set some options for the edit field (editoptions) like maxlength for 'text' or dataUrl for 'select' (we will use the same actions for providing selects markup as in searching sample). Grid provides as also some validation support, which can be configured through editrules option. Be aware, that options we use in our example are only a small subset of what jqGrid allow us to configure, so I strongly advice reading the documentation.
Ok, let's initialize our grid:

<script type="text/javascript">
  $(document).ready(function() {
    $('#jqgProducts').jqGrid({
      //url from wich data should be requested
      url: '/Home/ProductsGridData/',
      //url for edit operation
      editurl: '/Home/EditProduct/',
      //type of data
      datatype: 'json',
      //url access method type
      mtype: 'GET',
      //columns names
      colNames: ['ProductID', 'ProductName', 'Supplier', 'Category', 'QuantityPerUnit', 'UnitPrice', 'UnitsInStock'],
      //columns model
      colModel: [
                  { name: 'ProductID', index: 'ProductID', align: 'left', width: 85, editable: false },
                  { name: 'ProductName', index: 'ProductName', align: 'left', width: 200, editable: true, edittype: 'text', editoptions: { maxlength: 40 }, editrules: { required: true } },
                  { name: 'Supplier', index: 'SupplierID', align: 'left', width: 225, formatter: supplierFormatter, unformat: supplierUnFormatter, editable: true, edittype: 'select', editoptions: { dataUrl: '/Home/SuppliersSelect' }, editrules: { required: true } },
                  { name: 'Category', index: 'CategoryID', align: 'left', width: 140, editable: true, edittype: 'select', editoptions: { dataUrl: '/Home/CategoriesSelect' }, editrules: { required: true } },
                  { name: 'QuantityPerUnit', index: 'QuantityPerUnit', align: 'left', editable: true, edittype: 'text', editoptions: { maxlength: 20 }, editrules: { required: true } },
                  { name: 'UnitPrice', index: 'UnitPrice', align: 'left', formatter: 'currency', formatoptions: { decimalSeparator: '.', thousandsSeparator: ',', decimalPlaces: 2, prefix: '$'}, editable: true, edittype: 'text', editrules: { required: true, number: true, minValue: 0 } },
                  { name: 'UnitsInStock', index: 'UnitsInStock', align: 'left', formatter: unitsInStockFormatter, unformat: unitsInStockUnFormatter, editable: true, edittype: 'text', editrules: { required: true, integer: true, minValue: 0, maxValue: 32767} }
                ],
      //pager for grid
      pager: $('#jqgpProducts'),
      //number of rows per page
      rowNum: 10,
      //initial sorting column
      sortname: 'ProductID',
      //initial sorting direction
      sortorder: 'asc',
      //we want to display total records count
      viewrecords: true
      //grid width
      width: 'auto'
      //grid height
      height: 'auto'
    });
  });
</script>

We are going to use a navigator as placeholder for add, edit and delete buttons, but it's not necessary. The buttons can be put anywhere on page, they just need to make proper API calls, which are well described in form editing documentation section.
For complete CRUD scenario, we need to prepare three operations. We will start with easiest one, which is deleting. All we have to do is enable delete button and set url for operation (in fact the url isn't necessary, all operations can use the editurl from initial configuration, but having seperate actions for each operation is a lot cleaner).

$('#jqgProducts').navGrid(
  '#jqgpProducts',
  //enabling buttons
  { add: false, del: true, edit: false, search: false },
  //edit options
  { },
  //add options
  { },
  //delete options
  { url: '/Home/DeleteProduct/' });

Delete request contains one parameter, which is row id value. In response we can send anything we want (or just nothing). If we sent something, we will receive it in afterSubmit event. Knowing that, we can prepare delete controller action:

/// <summary>
/// Deleting selected product
/// </summary>
/// <param name="id">product id</param>
/// <returns>json data</returns>
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult DeleteProduct(string id)
{
  //Deleting product
  bool success = _repository.DeleteProduct(Convert.ToInt32(id));

  //Returning data - we can hadle this data in form afterSubmit event
  return Json(success);
}

Let's move on to add operation. The client side part is pretty much the same (but remember, that jqGrid uses colModel options to build the form):

$('#jqgProducts').navGrid(
  '#jqgpProducts',
  //enabling buttons
  { add: true, del: true, edit: false, search: false },
  //edit options
  { },
  //add options
  { width: 'auto', url: '/Home/AddProduct/' },
  //delete options
  { url: '/Home/DeleteProduct/' });

On server side we receive a parameter for every editable field. We just need to make an object out of this and add it to repository:

/// <summary>
/// Adding product
/// </summary>
/// <param name="postData">postData collection</param>
/// <returns>json data</returns>
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult AddProduct(FormCollection postData)
{
  //Creating new product object based on postData
  Product product = new Product();
  product.ProductName = postData["ProductName"];
  product.SupplierID = Convert.ToInt32(postData["Supplier"]);
  product.CategoryID = Convert.ToInt32(postData["Category"]);
  product.QuantityPerUnit = postData["QuantityPerUnit"];
  product.UnitPrice = Convert.ToDecimal(postData["UnitPrice"].Replace(".", CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator));
  product.UnitsInStock = Convert.ToInt16(postData["UnitsInStock"]);
  product.Discontinued = false;

  //Adding product to repository
  bool success = _repository.AddProduct(product);

  //Returning data - we can hadle this data in form afterSubmit event
  return Json(success);
}

Last operation to implement is editing. There are two edit approaches to consider (actually there are three, but I'm going to skip cell editing): inline editing and form editing. In inline editing we are adding onSelectRow event handler in which we call editRow (for displaying edit controls) or saveRow (for posting new values to server) method:

$('#jqgProducts').setGridParam({
  onSelectRow: function(id){
    if(id && id != lastSel){
      //save changes in row
      $('#jqgProducts').saveRow(lastSel, false);
      lastSel=id;
    }
    //trigger inline edit for row
    $('#jqgProducts').editRow(id, true);
  }
});

Form editing works the same as adding. We just enable the button and then grid constructs the form based on colModel:

$('#jqgProducts').navGrid(
  '#jqgpProducts',
  //enabling buttons
  { add: true, del: true, edit: true, search: false },
  //edit options
  { width: 'auto' },
  //add options
  { width: 'auto', url: '/Home/AddProduct/' },
  //delete options
  { url: '/Home/DeleteProduct/' });

No matter which approach we choose, request looks the same. We are getting row id and values for all the editable fields. What we need to do is get object from repository and set new values:

/// <summary>
/// Editing product
/// </summary>
/// <param name="postData">postData collection</param>
/// <returns>json data</returns>
[AcceptVerbs(HttpVerbs.Post)]
public ActionResult EditProduct(FormCollection postData)
{
  //Editing product based on postData
  Product product = _repository.GetProduct(Convert.ToInt32(postData["id"]));
  product.ProductName = postData["ProductName"];
  product.SupplierID = Convert.ToInt32(postData["Supplier"]);
  product.CategoryID = Convert.ToInt32(postData["Category"]);
  product.QuantityPerUnit = postData["QuantityPerUnit"];
  product.UnitPrice = Convert.ToDecimal(postData["UnitPrice"].Replace(".", CultureInfo.CurrentCulture.NumberFormat.NumberDecimalSeparator));
  product.UnitsInStock = Convert.ToInt16(postData["UnitsInStock"]);

  //Sending changes back to repository
  bool success = true;
  try
  {
    _repository.SubmitChanges();
  }
  catch (Exception ex)
  {
    Debug.Write(ex.Message);
    success = false;
  }

  //Returning data - we can hadle this data in form afterSubmit event
  return Json(success);
}

That makes our CRUD scenario complete. Source code can be downloaded from usual location. To make deleting easy, I have changed FK_Order_Details_Products foreing key in Northwind database.