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.
