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:
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).
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">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.
$(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>
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(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:
'#jqgpProducts',
//enabling buttons
{ add: false, del: true, edit: false, search: false },
//edit options
{ },
//add options
{ },
//delete options
{ url: '/Home/DeleteProduct/' });
/// <summary>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):
/// 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);
}
$('#jqgProducts').navGrid(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:
'#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/' });
/// <summary>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:
/// 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);
}
$('#jqgProducts').setGridParam({Form editing works the same as adding. We just enable the button and then grid constructs the form based on colModel:
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);
}
});
$('#jqgProducts').navGrid(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:
'#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/' });
/// <summary>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.
/// 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);
}