JQGrid Loading lots of data

Question

SITUATION

I am using Trirand JQGrid for MVC[server side] in my proj.

I've got more than 5 hundred thousand records in a single table.

I load the data by calling this piece of code. this is what gives 500000 records collection.

IEnumerable<myIndexViewModel> myviewmodel= _allincidents.Select(x => new myIndexViewModel
            {

                IncidentRequestStatus = x.RequestStatus,
                RequestByUserName = x.RequestByUserName,
                Subject = x.Subject

            });     
gridModel.JqGrid.DataBind(myviewmodel.AsQueryable());

JQgrid handles the json based ajax requests very nicely for every next page i click.

PROBLEM

I dont want to load 5 hundred thousand records all together on the page load event as it kills jqgrid.

If i write a stored procedure in the DB for requesting a specific page to be displayed then its gonna load only that page in the myviewmodel collection.

How do i get pages on the fly from the DB when the next page is clicked. is this even possible in jqgrid?

SITUATION 2

Based on the answers from VIJAY and MARK the approach they have shown is absolutely correct but over here the JQGRID for MVC sets up the DATAURL property for making the method call. In this case its the IncidentGridRequest.

How do i send in the page number when the grid next page or previous page is clicked?

incidentModel.IncidentGrid.DataUrl = Url.Action("IncidentGridRequest")

 public JsonResult IncidentGridRequest()
        {
         }

Hewlett

I dont have this component installed but if you can firebug the client side request coming in and view the headers when you go to page 2. The request will reveal the variable names it sends in and you might have to refill the totalpages, totalrecords etc of the result to send it to client. mvc will bind the names if they are same with request variables so you can just use them for any grid. – Perhaps

Answer 1

The Trirand jqGrid for ASP.NET MVC is using IQueryable interface inside the JqGrid.DataBind() method to implement pagin, sorting and filtering.

So the key here is to use datasource, which handle these types of operations at the database level (by crafting SQL queries to the database in such a way that only the data required is fetched). All major ORMs have this support, this includes: LINQ-2-SQL, Entity Framework, NHbiernate, LLBLGen.

You just need to use one of this technologies, and past the required context directly to JqGrid.DataBind() method (without extracting the data manually like you do it in your sample).

Confabulate

You are right about the fact that ORMS have this support and thats what trirand jqgrid for mvc showcase in their examples. But its just that all the business requirement scenarios are different and they dont necessarily need ORM's. I am sure Jqgrid developers might have given a thought about this as people are gonna use custom datasources. – Hewlett

@PareenVatani First of all you can't confuse things. jqGrid is jQuery plugin (client side library), Trirand jqGrid for ASP.NET MVC is only wrapper around it. There are other wrappers you can test, for example Lib.Web.Mvc (a little advertisement). If it comes to Trirand jqGrid for ASP.NET MVC this is the only way it provides. If you try to limit results earlier it will try to interpret that and will claim that there is only one page of data - the 2 above answers can't be used with Trirand jqGrid for ASP.NET MVC, but in case when you use no server side wrapper. – Confabulate

hmmmm you were right that the grid interpreted only one page. I am still looking for answers from these guys if its possible – Hewlett

@PareenVatani Those guys are not giving you answers related to the Trirand jqGrid for ASP.NET MVC wrapper (I'm afraid you can't solve your issue with this particular wrapper). Did you check the link I gave you? There are also two sample projects there, that you might want to try: jqGrid in ASP.NET MVC 3 and Razor and jqGrid in ASP.NET MVC - Strongly typed helper. Please remember that jqGrid itself is a free jQuery plugin and you are not force to use any particular wrapper on server side. – Confabulate

@PareenVatani: I can confirm what tpeczek wrote before. jqSuite from trirand is just one more wrapper to open source jqGrid written in JavaScript only. So if you can choose any other solution which you like. I personally don't use and so don't know Trirand JQGrid for MVC. So you should know the main jqGrid JavaScript product. To speed up productivity you can choose any wrapper which you like inclusive one which tpeczek shared with other people for example. It's your personal choose. – Fanion

Answer 2

Your controller action that will provide your grid with results can accept some extra information from jqGrid.

    public ActionResult GetGridData(string sidx, string sord, int page, int rows, bool _search, string filters)

The main parts you are interested in is the page, rows (sidx is for column sorting, sord for the sorting order, _search if there was a search done on the grid, and if so filters contains the search information)

When you generate your results you should be able to then

IEnumerable<myIndexViewModel> myviewmodel = allincidents.Select(x => new myIndexViewModel
            {

            IncidentRequestStatus = x.RequestStatus,
            RequestByUserName = x.RequestByUserName,
            Subject = x.Subject

        }).Skip((page - 1) * rows).Take(rows)

PS. I'm not sure if you using IEnumberable will be moving a large amount of data from your DB but you might want to use IQueryable when you generate this subset of data for the jqGrid.

Edit: To deal with your paging issues, You should be calculating the number of total records in your query and passing that value to the grid, Ex

int totalRecords = myviewmodel.Count();

and then later you would pass that to your grid as a jSon value. Ex

    var jsonData = new
    {
        total = (totalRecords + rows - 1) / rows,
        page = page,
        records = totalRecords,
        userdata = new {SearchResultsFound = searchResultsFound},
        rows = (
        ......

Toms

Thanks mark your approach is right but how do i pass in the page number from the grid request . Check the question for situation 2 – Hewlett

It should send it automatically as an Ajax request. Your MVC controller action that is responsible for responding to the request would then be able to take that data and take the correct "slice" of the data you requested and feed it back to the grid asynchronously. I would strongly suggest something like Firefox with Firebug or Chrome which would let you inspect this ajax request when you interact with the grid, it might make things clearer for you. – Toms

thanks mark i was able to get the parameters in the IncidentGridRequest method. Now the problem is that the number of rows requested fills up at the initial request and the next page option is disabled in the grid. How do i make the second request for bunch of rows ? – Hewlett

You must refer to the last comment of tpeczek at the bottom . Could that be related to the problem i am having of 1 page – Hewlett

Answer 3

Yes, for example if you are accepting the page number you want to turn to in a variable named page and the have the size of page in a variable pageSize then:

IEnumerable<myIndexViewModel> myviewmodel = allincidents.Select(x => new myIndexViewModel
            {

                IncidentRequestStatus = x.RequestStatus,
                RequestByUserName = x.RequestByUserName,
                Subject = x.Subject

            }).Skip((page-1)*pageSize).Take(pageSize));

will give you the records of size pageSize to you.

Johm

Thanks Vijay you are right on the approach but how do i pass in the page number from the grid request . Check the question for situation 2 – Hewlett

Please check the documentation at trirand.com/blog/jqgrid/jqgrid.html jqgrid sends the following query strings to the server page rows sord for the page number, page size and sorting direction respectively. – Johm

Hey Vijay that link really helped . I am able to take the page size and fetch the number of requested row. But the problem is that next page option within the jqgrid is disabled as the grid knows that those are only the rows in the DB . How do i make the second request? – Hewlett

You must refer to the last comment of tpeczek at the bottom . Could that be related to the problem i am having of 1 page – Hewlett

Answer 4

An easier approach by using PagedList library (from Nuget). There is a useful blog by Joseph Schrag

    public JsonResult Users(int PageNo, int Rows)
    {
        var UserList = db.Users.Select(t => new
        {
            t.UserId,
            t.Username,
            t.Firstname,
            t.Lastname,
            t.Designation,
            t.Country,
            t.Email
        }).OrderBy(t => t.UserId);

        var pagedUserList = UserList.ToPagedList(PageNo, Rows);

        var results = new
        {
            total = pagedUserList.PageCount, //number of pages
            page = pagedUserList.PageNumber, //current page
            records = UserList.Count(), //total items
            rows = pagedUserList
        };
        return new JsonResult() { Data = results, JsonRequestBehavior = JsonRequestBehavior.AllowGet };
    }

Fitz