jqGrid get data from MVC Controller

So ever since MVC for asp.net started becoming all the rage, and for good reason! Many people have bemoaned the loss of the venerable DataGrid from WebForms. Well there are a few options out there. You have Teleriks Kendo UI suite which is great but costs money. There are a few other options but the best jqGrid a jQuery plugin.

Now this grid can be a bit tricky to use. I personally find the documentation a bit short to put it politely. I've done enough grids now I'm getting the hang of it and want to share some pointers for other ASP.net MVC developers out there. Now let's assume in your View you have a nice jqGrid pointed to a controller action (I'll make another post about this as well). jqGrids are very powerful and support paging, grouping, filtering, sorting, etc. Now the bad part is you have to handle all that in your controller. This controller action will show you have to sort, page, and generally retrieve data.

Now for the jqGrid to work correctly it needs to have a very unique signature.

We need 4 parameters, these names must BE AS SHOWN, if not you won't get the values to fully sort, etc.

sidx -  Sorting index, the column name being sorted

sord - Sorting direction this will DESC or ASC

page - The page number the grid is on

rows - The number of items per page

 public ActionResult Get( string sidx, string sord, int page, int rows )  
     {  
       // check if we have a sort, null means nothing  
       sord= ( sord== null ) ? "" : sord;  

       // get the page index into an int (which page we're on)  
       int pageIndex = Convert.ToInt32( page ) - 1; 
 
       // get the number of items per page  
       int pageSize = rows;  

       // get some data from an Entity Framework source  
       // you can get it from anywhere  
       var users = _db.Users.Select(  
         user => new UsersGridModel  
         {  
           Id = user.Id,  
           Username = user.UserName,  
           EmailAddress = user.EmailAddress,  
           Name = user.FirstName + " " + user.LastName,  
           Created = user.Created  
         } );  

       // get the total count of records to displau  
       int totalRecords = users.Count();  
       
       // get the total number of pages to display  
       var totalPages = (int)Math.Ceiling( (float)totalRecords / (float)rows );  

       // check which direction if any we're sorting  
       if( sord.ToUpper() == "DESC" )  
       {  
         // you need to look at the sidx (sorting index) this will be a column name  
         // use the correct item in your source to sort the column correctly  
         users = users.OrderByDescending( t => t.Id );  
         users = users.Skip( pageIndex * pageSize ).Take( pageSize );  
       }  
       else  
       {  
         // you need to look at the sidx (sorting index) this will be a column name  
         // use the correct item in your source to sort the column correctly  
         users = users.OrderBy( t => t.Id );  
         users = users.Skip( pageIndex * pageSize ).Take( pageSize );  
       }  

       // we need to send back JSON in a very specific format  
       var jsonData = new  
       {  
         total = totalPages,  
         page,  
         records = totalRecords,  
         rows = users  
       };  

       // send it back, the AllowGet isn't required since MVC 4 or 5 but it's good practice  
       return Json( jsonData, JsonRequestBehavior.AllowGet );  

     }  


Now if you have a lot of columns that sorting section can get hairy, I would move it into another function. In fact you could modularize this and try and make it pretty generic but this is a one off example. But this is a basic way to get data to a jqGrid from an MVC Controller.

Comments

Popular Posts