Using JQGrid with ASPNET MVC for paging and sorting

Published on September 17, 2009 by Toran Billups

When I first started working with the aspnet MVC framework I found that the only real feature missing out of the box was the ability to sort and apply pagination to tabular data. In the webforms world you had a ton of server controls that would do this for you, so it feels like a missing feature for anyone new to MVC. But I couldn't see myself walking away from all the goodness of this new framework just because I couldn't sort a grid.

JQGrid to the rescue! I looked at several sorting options and at the time the MVC Contrib project had a grid but it only did pagination, not sorting. I needed something that would allow me to do both. One of the biggest reasons I went with JQGrid was that it's built around the fabulous jQuery javascript library. And the fact that MVC has support for jQuery out of the box made this a perfect match.

During a project earlier this year I decided to leverage JQGrid 3.5 for a few reasons. First it was the most extensible grid control in the jQuery space, and I knew this project would require heavy modification of anything on the client side. Second, it came with amazing support for the jQuery theme roller. This was also a big win because I'm growing tired of building custom skins for projects that must be IE6 friendly.

Recently JQGrid 3.5.3 was released and I thought it was a good time to integrate it with MVC and write a short blog post about my experience for someone else who might want a sample to get started.

The first step would be to visit the JQGrid blog and pull down the latest build. I'm using 3.5.3 in this post but anything in the 3.5.x should work. In addition you will need a few dependencies, including a jQuery Theme (included here if you don't want to download it manually from the jQuery theme roller website). Also, for the search functionality you will need the dynamic LINQ assembly.

If you don't want to pull down these files manually, I have them included in the sample project that is built during this post.

Once you have these files download, go to the master page and make sure you reference them in the head of your html document as shown below.

1
2
3
4
5
6
7
    <link href='../../Content/Site.css' rel='stylesheet' type='text/css' />
    <link href='../../Content/ui.jqgrid.css' rel='Stylesheet' type='text/css' />
    <link href='../../themes/redmond/jquery-ui-1.7.1.custom.css' rel='Stylesheet' type='text/css' />
    <link href='../../themes/redmond/jquery-ui.css' rel='Stylesheet' type='text/css' />
    <script src='/js/jquery-1.3.2.min.js' type='text/javascript'></script>
    <script src='/js/i18n/grid.locale-en.js' type='text/javascript'></script>
    <script src='/js/jquery.jqGrid.min.js' type='text/javascript'></script>

Next make sure to add the files referenced above in a similar directory structure. I would drag the themes folder and js folder into the root of the MVC project. Along with this be sure you add the 'ui.jqgrid.css' file to the css folder for your project. I'm using Content as MVC has the 'Site.css' file in this location by default.

Once you add these files to your project be sure you add a reference to the dynamic LINQ assembly.

Next we need to add the html elements that will be populated by JQGrid. I added these to the index view under the home directory in this example.

1
2
    <table id='list' cellpadding='0' cellspacing='0'></table>
    <div id='pager' style='text-align: center;'></div>

Also in the index view add the javascript to create the grid by calling the jqGrid method via jQuery 1.3.2.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
    <script type='text/javascript'>
        $(document).ready(function() {
            jQuery('#list').jqGrid({
                url: 'http://localhost:56553/Home.aspx/GetUserList/',
                datatype: 'json',
                colNames: ['Id', 'FirstName', 'LastName', 'Address', 'City', 'State', 'Zip', 'Phone'],
                colModel: [
   		            { name: 'id', index: 'id', hidden: true },
   		            { name: 'FirstName', index: 'FirstName', width: 90, sortable: true },
   		            { name: 'LastName', index: 'LastName', width: 90, sortable: true },
   		            { name: 'Address', index: 'Address', width: 90, sortable: true },
   		            { name: 'City', index: 'City', width: 90, sortable: true },
   		            { name: 'State', index: 'State', width: 90, sortable: true },
   		            { name: 'Zip', index: 'Zip', width: 90, sortable: true },
   		            { name: 'Phone', index: 'Phone', width: 150, sortable: true }
   	            ],
   		        rowNum: 20,
   		        width: 960,
                height: 'auto',
                rowList: [10, 20, 50],
                imgpath: '/themes/redmond/images',
                pager: jQuery('#pager'),
                sortname: 'id',
                hidegrid: false,
                viewrecords: true,
                sortorder: 'asc',
                caption: 'User Collection'
            }).navGrid('#pager', { edit: false, add: false, del: false });
        }); 
    </script>

Next modify the home controller to look something like this.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
Imports JQGridSample.Entity
Imports System.IO
Imports System.Linq
Imports System.Linq.Dynamic

<HandleError()> _
Public Class HomeController
    Inherits JQGridHelperBase

    Function Index() As ActionResult
        ViewData('Message') = 'Welcome to ASP.NET MVC!'

        Return View()
    End Function

    Function GetUserList(ByVal sidx As String, ByVal sord As String, ByVal page As Integer, ByVal rows As Integer) As ActionResult
        Dim UserCollection As IEnumerable(Of User) = SetupFakeUserCollection()

        Dim json As JsonResult = MyBase.ProcessThePagingAndSortingRequest(UserCollection, sidx, sord, page, rows)

        If json IsNot Nothing Then
            Return json
        End If

        Return Me.Content('{'total':0,'page':1,'records':0,'rows':[]}', 'text/json')
    End Function

    Function SetupFakeUserCollection() As IEnumerable(Of User)
        Dim UserCollection As New List(Of User)

        For i = 0 To 100
            UserCollection.Add(New User With {.ID = i, .FirstName = 'John', .LastName = 'Doe', .Address = '400 SW 8th', .City = 'Des Moines', .State = 'IA', .Zip = '50309', .Phone = '5152843369'})
        Next

        Return UserCollection
    End Function

    Public Overrides Function BuildJSON(ByVal collection As System.Collections.IEnumerable) As Object
        If collection Is Nothing Then
            Return Nothing
        End If

        Dim values(collection.AsQueryable.Count() - 1)

        For i = 0 To collection.AsQueryable.Count - 1
            values(i) = New With {.id = collection(i).ID, .cell = MyBase.GetArray(collection(i).ID.ToString(), collection(i).FirstName, collection(i).LastName, collection(i).Address, collection(i).City, collection(i).State, collection(i).Zip, collection(i).Phone)}
        Next

        Return values
    End Function
End Class

Notice we are using a base class to do the heavy lifting here. This looks something like the below.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
Imports System.IO
Imports System.Linq
Imports System.Linq.Dynamic
Imports System.Web
Imports System.Web.Mvc

Public MustInherit Class JQGridHelperBase
    Inherits System.Web.Mvc.Controller

    Public Sub New()

    End Sub

    Public Function ProcessThePagingAndSortingRequest(ByVal collection As IEnumerable, ByVal sidx As String, ByVal sord As String, ByVal page As Integer, ByVal rows As Integer) As ActionResult
        Dim Search, SearchField, SearchOper, SearchString

        If collection IsNot Nothing Then
            If Request.QueryString('_search') = 'true' Then
                Search = True
                SearchField = Request.QueryString('searchField')
                SearchOper = Request.QueryString('searchOper')
                SearchString = Request.QueryString('searchString')
            End If

            Return SortAndPageResults(collection, sidx, sord, page, rows, Search, SearchField, SearchOper, SearchString)
        End If

        Return Nothing
    End Function

    Public Function SortAndPageResults(ByVal collection As IEnumerable, ByVal sidx As String, ByVal sord As String, ByVal page As Integer, ByVal rows As Integer, ByVal search As Boolean, ByVal searchField As String, ByVal searchOper As String, ByVal searchString As String) As ActionResult
        Dim pageIndex As Integer = Convert.ToInt32(page) - 1
        Dim pageSize As Integer = rows
        Dim totalRecords As Integer = collection.AsQueryable.Count
        Dim totalPages As Integer = CInt(Math.Ceiling(CSng(totalRecords) / CSng(pageSize)))
        Dim FilteredAndSortedList

        If search = True Then
            Const strpredicateFormat As String = '{0}.ToString().StartsWith(@0)'
            Dim searchExpression = New System.Text.StringBuilder()
            Dim orPart As String = [String].Empty

            searchExpression.Append(orPart)
            searchExpression.AppendFormat(strpredicateFormat, searchField, searchString)

            FilteredAndSortedList = collection.AsQueryable().Where(searchExpression.ToString(), searchString)
        Else
            FilteredAndSortedList = collection.AsQueryable().OrderBy(sidx + ' ' + sord).Skip(pageIndex * pageSize).Take(pageSize)
        End If

        Dim jsonData = New With { _
            .total = totalPages, _
            .page = page, _
            .records = totalRecords, _
            .rows = BuildJSON(FilteredAndSortedList) _
        }

        Return Json(jsonData)
    End Function

    Public MustOverride Function BuildJSON(ByVal collection As IEnumerable) As Object

    Public Function GetArray(Of T)(ByVal ParamArray values() As T) As T()
        Return values
    End Function
End Class

The basic idea is that you get a collection of data to populate your grid. Ask the base class to take this collection along with the information that JQGrid adds to the ajax call including: sort id, sort order, page requested and the number of rows to return.

The base class will also look to see if this is a search or just a sort/page change request. If you do have a search request the dynamic LINQ assembly goes to work in the .WHERE(searchExpression, searchString) section of the SortAndPageResults method to match the expression and return only that result set.

Notice that if you inherit from this class you need to implement a method called 'BuildJSON'. This method is custom to each grid and thus should be a place that you can tweak the actual JSON structure that gets sent to the client.

As always, the source code for this sample is available for download.