Introduction

Yesterday I wrote about Nancy and jtable and how to get it working and showing some data. Today I want to add serverside paging and sorting to that.

Paging

This is where the Post is actually sending some data along, namely the paging data. Of course they could have used a Get and some urlparameters to make this work but they didn’t and I can live with that.

So you will “need” a class to put the pagingparameters in so you can Bind to those.

C#
1
2
3
4
5
public class PagingParameters
    {
        public int jtStartIndex { get; set; }
        public int jtPageSize { get; set; }
    }
public class PagingParameters
    {
        public int jtStartIndex { get; set; }
        public int jtPageSize { get; set; }
    }

So the Post will give you two fields jtStartIndex which is the number of the first row it will want to show and the jtPageSize which is the size of the page. Most databases will find this sufficient. This is how limit works in mysql and skip/take in linq.

And a slight update to my module so that I can page through my data.

C#
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
public class PlantsModule:NancyModule
    {
        public PlantsModule()
        {
            Get["/"] = parameters => View["Plants"];
            Post["/plants"] = parameters =>
                {
                    var paging = this.Bind<PagingParameters>();
                    var plantsModel = new PlantsModel
                        {
                            Result = "OK",
                            Records = new List<PlantModel>(),
                            TotalRecordCount = 25
                        };
                    plantsModel.Records = GetPlantModels().Skip(paging.jtStartIndex).Take(paging.jtPageSize).ToList();
                    return Response.AsJson(plantsModel);
                };
        }
 
        private IList<PlantModel> GetPlantModels()
        {
            var plantModels = new List<PlantModel>();
            for (var i = 1; i <= 25; i++)
            {
                var j = i.ToString("000");
                plantModels.Add(new PlantModel()
                {
                    Id = i,
                    Name = "name" + j,
                    Genus = "genus" + j,
                    Species = "Species" + j
                });
            }
            return plantModels;
        }
    }
public class PlantsModule:NancyModule
    {
        public PlantsModule()
        {
            Get["/"] = parameters => View["Plants"];
            Post["/plants"] = parameters =>
                {
                    var paging = this.Bind<PagingParameters>();
                    var plantsModel = new PlantsModel
                        {
                            Result = "OK",
                            Records = new List<PlantModel>(),
                            TotalRecordCount = 25
                        };
                    plantsModel.Records = GetPlantModels().Skip(paging.jtStartIndex).Take(paging.jtPageSize).ToList();
                    return Response.AsJson(plantsModel);
                };
        }

        private IList<PlantModel> GetPlantModels()
        {
            var plantModels = new List<PlantModel>();
            for (var i = 1; i <= 25; i++)
            {
                var j = i.ToString("000");
                plantModels.Add(new PlantModel()
                {
                    Id = i,
                    Name = "name" + j,
                    Genus = "genus" + j,
                    Species = "Species" + j
                });
            }
            return plantModels;
        }
    }

As you can see I also had to add a property to my PlantsModel. Namely TotalRecordCount so jtable can figure out how many pages there will be.

So this is now PlantsModel.

C#
1
2
3
4
5
6
7
8
9
10
11
using System.Collections.Generic;
 
namespace NancyJTable.Models
{
    public class PlantsModel
    {
        public string Result { get; set; }
        public IList<PlantModel> Records { get; set; }
        public int TotalRecordCount { get; set; }
    }
}
using System.Collections.Generic;

namespace NancyJTable.Models
{
    public class PlantsModel
    {
        public string Result { get; set; }
        public IList<PlantModel> Records { get; set; }
        public int TotalRecordCount { get; set; }
    }
}

And of course we also need to tell our view that it should use paging from now on.

Which is nothing special.

HTML
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
@using Nancy
<!DOCTYPE html>
 
<html>
    <head>
        <title>Plants</title>
        <link href="@Url.Content("~/Content/Scripts/jtable/themes/lightcolor/gray/jtable.min.css")" rel="stylesheet" type="text/css" />
        <script src="@Url.Content("~/Content/Scripts/jquery-1.9.0.min.js")" type="text/javascript"></script>
        <script src="@Url.Content("~/Content/Scripts/jquery-ui-1.10.0.min.js")" type="text/javascript"></script>
        <script src="@Url.Content("~/Content/Scripts/jtable/jquery.jtable.min.js")" type="text/javascript"></script>
        <script type="text/javascript">
            $(document).ready(function () {
                $('#PlantsTableContainer').jtable({
                    title: 'Table of plants',
                    paging: true, 
                    pageSize: 10,
                    actions: {
                        listAction: '/plants',
                    },
                    fields: {
                        Id: {
                            title: 'Id',
                            key: true,
                            list: true,
                            width: '10%',
                        },
                        Name: {
                            title: 'Name',
                            width: '30%'
                        },
                        Genus: {
                            title: 'Genus',
                            width: '30%',
                        },
                        Species: {
                            title: 'Species',
                            width: '30%',
                        }
                    }
                });
                $('#PlantsTableContainer').jtable('load');
            });
</script>
    </head>
    <body>
        <div>
            <div id="PlantsTableContainer"></div>
        </div>
    </body>
</html>
@using Nancy
<!DOCTYPE html>

<html>
    <head>
        <title>Plants</title>
        <link href="@Url.Content("~/Content/Scripts/jtable/themes/lightcolor/gray/jtable.min.css")" rel="stylesheet" type="text/css" />
        <script src="@Url.Content("~/Content/Scripts/jquery-1.9.0.min.js")" type="text/javascript"></script>
        <script src="@Url.Content("~/Content/Scripts/jquery-ui-1.10.0.min.js")" type="text/javascript"></script>
        <script src="@Url.Content("~/Content/Scripts/jtable/jquery.jtable.min.js")" type="text/javascript"></script>
        <script type="text/javascript">
            $(document).ready(function () {
                $('#PlantsTableContainer').jtable({
                    title: 'Table of plants',
                    paging: true, 
                    pageSize: 10,
                    actions: {
                        listAction: '/plants',
                    },
                    fields: {
                        Id: {
                            title: 'Id',
                            key: true,
                            list: true,
                            width: '10%',
                        },
                        Name: {
                            title: 'Name',
                            width: '30%'
                        },
                        Genus: {
                            title: 'Genus',
                            width: '30%',
                        },
                        Species: {
                            title: 'Species',
                            width: '30%',
                        }
                    }
                });
                $('#PlantsTableContainer').jtable('load');
            });
</script>
    </head>
    <body>
        <div>
            <div id="PlantsTableContainer"></div>
        </div>
    </body>
</html>

As you can see I added a paging attribute and set it to true and a pageSize that I set to 10.

And here is the proof that it works.

Sorting

Of course our users probably also want to sort. And I (their master) will let them do this on name.

So I have to change the javascript in the view a little more.

Javascript
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
$(document).ready(function () {
                $('#PlantsTableContainer').jtable({
                    title: 'Table of plants',
                    paging: true, 
                    pageSize: 10,
                    sorting: true, 
                    defaultSorting: 'Name ASC', 
                    actions: {
                        listAction: '/plants',
                    },
                    fields: {
                        Id: {
                            title: 'Id',
                            key: true,
                            list: true,
                            width: '10%',
                            sorting: false
                        },
                        Name: {
                            title: 'Name',
                            width: '30%'
                        },
                        Genus: {
                            title: 'Genus',
                            width: '30%',
                            sorting: false
                        },
                        Species: {
                            title: 'Species',
                            width: '30%',
                            sorting: false
                        }
                    }
                });
                $('#PlantsTableContainer').jtable('load');
            });
$(document).ready(function () {
                $('#PlantsTableContainer').jtable({
                    title: 'Table of plants',
                    paging: true, 
                    pageSize: 10,
                    sorting: true, 
                    defaultSorting: 'Name ASC', 
                    actions: {
                        listAction: '/plants',
                    },
                    fields: {
                        Id: {
                            title: 'Id',
                            key: true,
                            list: true,
                            width: '10%',
                            sorting: false
                        },
                        Name: {
                            title: 'Name',
                            width: '30%'
                        },
                        Genus: {
                            title: 'Genus',
                            width: '30%',
                            sorting: false
                        },
                        Species: {
                            title: 'Species',
                            width: '30%',
                            sorting: false
                        }
                    }
                });
                $('#PlantsTableContainer').jtable('load');
            });

So I added sorting is true and set the defaultsorting to name and ascending and I turned off the sorting for all the columns except name.

Now I just have to adapt my module a bit.

Just change this line

plantsModel.Records = GetPlantModels().Skip(paging.jtStartIndex).Take(paging.jtPageSize).ToList();

to this

C#
1
plantsModel.Records = paging.jtSorting == "Name ASC" ? GetPlantModels().OrderBy(x => x.Name).Skip(paging.jtStartIndex).Take(paging.jtPageSize).ToList() : GetPlantModels().OrderByDescending(x => x.Name).Skip(paging.jtStartIndex).Take(paging.jtPageSize).ToList();
plantsModel.Records = paging.jtSorting == "Name ASC" ? GetPlantModels().OrderBy(x => x.Name).Skip(paging.jtStartIndex).Take(paging.jtPageSize).ToList() : GetPlantModels().OrderByDescending(x => x.Name).Skip(paging.jtStartIndex).Take(paging.jtPageSize).ToList();

To get a sorted list.

And add the jtSorting property to our PagingParameters class.

C#
1
2
3
4
5
6
public class PagingParameters
    {
        public int jtStartIndex { get; set; }
        public int jtPageSize { get; set; }
        public string jtSorting { get; set; }
    }
public class PagingParameters
    {
        public int jtStartIndex { get; set; }
        public int jtPageSize { get; set; }
        public string jtSorting { get; set; }
    }

And you’re done.

Just look at thos cute little arrows in the columnheader of the name column.

Ascending

Descending

Conclusion

Paging and sorting is something you do a lot with these kinds of grids and with jtable it is made pretty easy for you to do.