Enable pager in jQgrid if the grid has loadonce: false

Question

I create a new list data with jQgrid, and filter the data with two date values after the initial grid data loading. Please see my code below.

.cshtml

@{
ViewBag.Title = "AdvertiseList";
Layout = "~/Views/Shared/_LayoutAdmin.cshtml";
}
<style type="text/css">
 .img_div {
    width: 13%;
 }
 </style>
 <script src="~/Scripts/jquery-1.9.1.js"></script>
 <script src="~/Scripts/jquery-ui-1.10.4.js"></script>
 <script src="~/Scripts/jquery.jqGrid.src.js"></script>
 <link href="~/jquery-ui-1.10.4.custom/css/ui-darkness/jquery-ui-1.10.4.custom.css"   rel="stylesheet" />
 <script src="~/Scripts/i18n/grid.locale-pt.js"></script>
 <script src="~/jquery-ui-1.10.4.custom/development-bundle/ui/i18n/jquery.ui.datepicker-pt.js"></script>
 <script type="text/javascript">
jQuery.jgrid.no_legacy_api = true;
</script>
<link href="~/Content/jquery.jqGrid/ui.jqgrid.css" rel="stylesheet" />
<script src="~/dist/JS/bootstrap-tooltip.js"></script>
<style>
.well {
    min-height: 500px;
    height: auto;
    margin-top: 40px;
}
</style>
<div class="well">
<h4 style="color: black;">Lista Anuncie</h4>
<br clear="all" />
<div class="form-inline">
    <div class="form-group">
        <input type="text" class="form-control" id="fromdate" placeholder="De Data">
    </div>
    <div class="form-group">
        <input type="text" class="form-control" id="todate" placeholder="Para Data">
    </div>
    <button id="btnSearch" type="submit" class="btn btn-default">Search</button>
</div>

<br clear="all" />
<div><b>Total de usuários: @ViewBag.Advertisecount</b></div>
<table id="jQGridDemo">
</table>
<div id="jQGridDemoPager">
</div>
<table id="search"></table>
<div id="filter"></div>
<script type="text/javascript">

    $(document).ready(function () {
        $("#fromdate").tooltip({ 'trigger': 'focus', 'title': 'De Data' });
        $("#todate").tooltip({ 'trigger': 'focus', 'title': 'Para Data' });
    });
    jQuery("#jQGridDemo").jqGrid({
        url: '@Url.Action("FillUsers", "Admin")',
        postData: {
            fromDate: function () {
                return $("#fromdate").val();
            },
            toDate: function () {
                return $("#todate").val();
            }
        },
        datatype: "json",
        mtype: "POST",
        colNames: ["Id", "Nome", "Email", "Senha", "Descrição", "Telefone",               "Endereço", "Data", "View Details"],
        colModel: [
             { name: "Id", width: 100, key: true, formatter: "integer", sorttype: "integer", hidden: true },
            { name: "Name", width: 200, sortable: true, editable: true, editrules: { required: true } },
            { name: "Email", width: 250, sortable: true, editable: true, editrules: { required: true } },
            { name: "Password", width: 200, sortable: true, editable: true, editrules: { required: true }, hidden: true },
            { name: "Description", width: 200, sortable: true, editable: true, editrules: { required: true }, hidden: true },
            { name: "Phone", width: 200, sortable: true, editable: true, editrules: { required: true }, hidden: true },
            { name: "Address", width: 350, sortable: true, editable: true, editrules: { required: true } },
            { name: "Date", width: 150, align: "center", formatter: "date", formatoptions: { srcformat: "ISO8601Long", newformat: "d-m-Y" }, sorttype: "date", datefmt: 'dd-mm-yy' },
            { name: "Edit",width:100,align: "center",editable: true,formatter:                    BuildAdvertiseUrl }
        ],
        rowNum: 500,
        gridview: true,
        autoencode: true,
        loadonce: false,
        height: "auto",
        rownumbers: true,
        prmNames: { id: "Id" },
        rowList: [5, 10, 20, 30],
        pager: '#jQGridDemoPager',
        emptyrecords: "Não há registros para exibir",
        sortname: 'id',
        sortorder: "asc",
        viewrecords: true,
        caption: "Lista de Usuários",
        width: 1000
    });
    jQuery("#jQGridDemo").jqGrid('navGrid', '#jQGridDemoPager',
    {
        del: false,
        edit: false,
        add: false,
        search: false
    },
    {//EDIT
    },
    {//ADD
    },
    {//DELETE
    },
    {//SEARCH 
    });

    $("#fromdate").datepicker({ dateFormat: 'dd-mm-yy' });
    $("#todate").datepicker({ dateFormat: 'dd-mm-yy' });

    $("#btnSearch").click(function () {
        $("#jQGridDemo").trigger("reloadGrid", [{ page: 1 }]);
    });

    function DeleteAdvertisementPopup(id) {

        $("#dialog-message").dialog({
            modal: true,
            buttons: {
                Ok: function () {

                    var url = "@Url.Action("DeleteAdd", "Admin")"

                    $.ajax({
                        type: "POST",
                        url: url,
                        data: "{ Id : " + id + " }",
                        contentType: "application/json; charset=utf-8",
                        dataType: "json",
                        success: function (data) {
                            $("#jQGridDemo").trigger("reloadGrid", [{ current: true }]);
                        }
                    });

                    $(this).dialog("close");
                },
                Cancelar: function () {
                    $(this).dialog("close");
                }
            }
        });
    }

    function BuildAdvertiseUrl(cellvalue, options, rowObject) {
        var url = "/Admin/EditAdvertise?Id=" + rowObject[0];
        var advertiseId = rowObject[0];
        return "<a href=" + url + "><img src='../Images/viewdetails.png'/></a><a onclick=\"javascript:DeleteAdvertisementPopup(" + advertiseId + ");\">&nbsp;<img width='16' height='16' src='../Images/button_delete.png'/></a>";
    }
</script>
</div>
@*-------Dialog message for confirmation to delete the advertisement-------------------*@
<div id="dialog-message" style="font-size: 13px;" title="Excluir Anuncie">
<p>
    Você quer apagar este anúncio?
</p>
</div>
@*-------------Endhtml-----------------*@

Actions in Controller

    public ActionResult AdvertiseList()
    {
        Session["AdvertiserId"] = null;
        if (Session["admin"] == null)
            return RedirectToAction("SignUpAdvertiser", "Home");
        else
        {
            using (DBLockScreenAppEntities db = new DBLockScreenAppEntities())
            {
                var user = db.Advertisers.Count();
                ViewBag.Advertisecount = user;
            }
        }

        return View();
    }

    public JsonResult FillUsers(string fromDate, string toDate, string sidx, string sord, int page, int rows)
    {
        var users = new List<Advertiser>();
        using (DBLockScreenAppEntities db = new DBLockScreenAppEntities())
        {
            if (!string.IsNullOrEmpty(fromDate) && !string.IsNullOrEmpty(toDate))
            {
                DateTime FromDate = new DateTime(Convert.ToInt32(fromDate.Split('-')[2]), Convert.ToInt32(fromDate.Split('-')[1]), Convert.ToInt32(fromDate.Split('-')[0]), 0, 0, 0);
                DateTime ToDate = new DateTime(Convert.ToInt32(toDate.Split('-')[2]), Convert.ToInt32(toDate.Split('-')[1]), Convert.ToInt32(toDate.Split('-')[0]), 23, 59, 59);

                users = db.Advertisers.Where(e => e.Date >= FromDate && e.Date <= ToDate).OrderByDescending(e => e.Date).ToList();
            }
            else
            {
                users = db.Advertisers.OrderByDescending(e => e.Date).ToList();
            }
        }
        return Json((
                from user in users
                select new[]{
                    user.Id.ToString(System.Globalization.CultureInfo.InvariantCulture),
                    user.Name,
                    user.Email,
                    user.Password,
                    user.Description,
                    user.Phone,
                    user.Address,
                    user.Date == null ? "": ((DateTime) user.Date).ToString("o")
                }
            ).ToArray(), JsonRequestBehavior.AllowGet);
    }

Please see my screenshot below

I set loadonce: false, and datatype: "json", to reload the grid with correspondent dates. But now i want to add a pager in my grid. I found some configuration script to do this. Could you please suggest a method to do enable pager.

Answer 1

If you really need to follow loadonce: false scenario you will have to implement server side paging of data. You need use page and rows parameter which jqGrid sent to the server. The server code should also implement server side sorting of data too. So you have to use sidx and sord options. Additionally you have to change the format of the data returned from the server. Instead of array of items you should return the object

{
    total,   // total number of pages
    page,    // 1-based number of returned page
    records, // total number of rows in all pages
    rows     // the array of rows for the requested page only
}

So instead of returning users you need to use something like

var pageOfUsers = users.Skip (
    "it." + sidx + " " + sord,
    "@skip",
    new ObjectParameter ("skip", (page - 1) * rows)
).Top ("@limit", new ObjectParameter ("limit", rows));

and returnes

var totalRecords = users.Count(); // total number of users
return Json(new {
    total = (totalRecords + rows - 1) / rows,
    page,
    records = totalRecords,
    rows = (from item in pageOfUsers
            select new[] {
                user.Id.ToString(System.Globalization.CultureInfo.InvariantCulture),
                user.Name,
                user.Email,
                user.Password,
                user.Description,
                user.Phone,
                user.Address,
                user.Date == null ? "": ((DateTime) user.Date).ToString("o")
            }).ToList()
}, JsonRequestBehavior.AllowGet);

On the other side I would recommend you to consider alternative to use loadonce: true scenario with local paging. In the case you will don't change your server code at all and just set rowNum: 50 and loadonce: true. In case of loading 1000 or even 10000 rows it will be typically quickly enough. If you would use rowNum: 50 (to show 50 rows) then the grid will display only the first 50 rows from all 1000 returned from the server. The user could use local paging or sorting (by clicking on some column header). Moreover you can add call of filterToolbar which allows user to filter previously loaded data. You can also use search: true option of jqGrid to allows user to make very complex queries of loaded data (you can use multipleSearch:true and multipleGroup:true searching option in //SEARCH part of your code). The response time for local sorting, local paging and local filtering/searching of the data will be perfect in case of 1000 rows of total local data. So the user will be ably really to analyse the returned data and you will don't need to write any long code.

What you would do is rest datatype to "json" inside of $("#btnSearch").click and inside of beforeRefresh (see the answer):

$("#btnSearch").click(function () {
    $("#jQGridDemo").jqGrid("setGridParam", {datatype: "json"})
        .trigger("reloadGrid", [{ page: 1 }]);
});

In the way you will reload the data from the server based on "#fromdate" and "#todate" on click on the button "#btnSearch" or on the Refresh button of the grid. All other operation will be done locally with previously loaded data.