Improving Performance of JQGrid for millions of records : Part2

This article will address more from ASP.NET point of view assuming you have already read Part 1

Now that you have all the back-end available lets get on with developing the website.

Start with the usual steps

1. Create an ASP.NET website
2. Add the required java scripts (JQuery, JQueryUI, JQGrid plugin)
3. Now add a new web service to the project and name it InvoicingService

InvoicingService.cs should look like

[WebMethod, ScriptMethod(ResponseFormat = ResponseFormat.Json)]
public string GetInvoiceData(int? page, int? rows, string sidx, string sord, bool _search, string searchField, string searchOper, string searchString)
{
// code to run stored procedure and get the relavent data from database 
// need to pass the search condition, page and rows to stored procedure
}

refer to call a stored procedure .

Now modify default.aspx to have the following :-

Create a new script file common.js with following code

$(function () {

  $("#grid_invoices_tbl").jqGrid({
        url: "InvoicingService.asmx/GetInvoiceData",
        datatype: "json",
        mtype: 'POST',
        ajaxGridOptions: { contentType: 'application/json; charset=utf-8' },
        colNames:
            ['INVOICEID',
            'CUSTOMER NAME',
            'PRODUCT NAME',
            'UNIT PRICE',
            'QUANTITY',
            'TOTAL PRICE'],
        colModel: [
        { name: 'INVOICEID', index: 'INVOICEID', align: 'left'},
  { name: 'CUSTOMERNAME', index: 'CUSTOMERNAME', align: 'left'},
  { name: 'PRODUCTNAME', index: 'PRODUCTNAME', align: 'left'},
  { name: 'UNITPRICE', index: 'UNITPRICE', align: 'left'},
  { name: 'QUANTITY', index: 'QUANTITY', align: 'left'},
  { name: 'TOTALPRICE', index: 'TOTALPRICE', align: 'left'}
  ],
        rowNum: 10,
        rowList: [5, 10, 20, 50],
        pager: '#grid_invoices_pager',
        sortname: 'INVOICEID',
        viewrecords: true,
        sortorder: "asc",
        caption: 'Invoice Details',
        serializeGridData: function (postData) {
            if (postData.searchField === undefined) postData.searchField = null;
            if (postData.searchString === undefined) postData.searchString = null;
            if (postData.searchOper === undefined) postData.searchOper = null;            
            return JSON.stringify(postData);
        },
        loadError: function (xhr, status, error) {
            alert(status);
            alert(xhr);
            alert(error);
        },
        loadComplete: function (data) {
           // alert(data);
        },
        jsonReader: {
            root: function (obj) { return obj.d.rows; },
            page: function (obj) { return obj.d.page; },
            total: function (obj) { return obj.d.total; },
            records: function (obj) { return obj.d.records; }
        },             
        autowidth: true
    }).jqGrid('navGrid', '#grid_invoices_pager', { edit: false, add: false, del: false, search: true });

});

Hope this should have helped you to handle huge amounts of data.


Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
~Andy Rooney