Wijmo Wednesday: Server Side Grid Magic

In a previous post, we discussed how to add a whole bunch of awesome features to a standard HTML table and transform it into a “grid”.  What I’d like to do today is show you how to move a lot of this functionality over to the server.

By harnessing the server to do a lot of the “heavy lifting”, we can give our users a smooth experience.  What heavy lifting would need to be done?  Assume you have a data set of 1 million rows.  That’s a lot of data to send to the client, and then sort/page/filter.  If we ask the server to sort/page/filter the data before sending it to the client, we can quickly reduce the overhead of the client.

I’m going to be using the example from my webcast, which you can access here.

The Server

For data, I’m using the Chinook database.  It’s quick and easy to set up, and gives me good data to use in my examples.  I’m using ASP.NET MVC 3 for my examples, and we’ll only be implementing sorting and filtering.

        public JsonResult GetAlbumList()
        {
            int pageSize = Request.Params["paging[pageSize]"] != null ? Convert.ToInt32(Request.Params["paging[pageSize]"]) : 0;
            int pageIndex = Request.Params["paging[pageIndex]"] != null ? Convert.ToInt32(Request.Params["paging[pageIndex]"]) : 0;

            string sortColumn = Request.Params["sorting[0][dataKey]"];
            string sortDirection = Request.Params["sorting[0][sortDirection]"];

            if (string.IsNullOrEmpty(sortColumn)) sortColumn = String.Empty;
            if (string.IsNullOrEmpty(sortDirection)) sortDirection = String.Empty;

            using (var entity = new ChinookEntities())
            {
                var allAlbums = from al in entity.Albums
                                join ar in entity.Artists on al.ArtistId equals ar.ArtistId
                                select new AlbumResult()
                                           {
                                               AlbumName = al.Title,
                                               ArtistName = ar.Name
                                           };

                var totalRowCount = allAlbums.Count();

                if (pageSize == 0)
                    pageSize = totalRowCount;

                if (sortColumn.ToLower() != "album")
                    allAlbums = sortDirection.ToLower() == "descending"
                                    ? allAlbums.OrderByDescending(p => p.ArtistName).Skip(pageSize*pageIndex).Take(pageSize)
                                    : allAlbums.OrderBy(p => p.ArtistName).Skip(pageSize*pageIndex).Take(pageSize);
                else
                    allAlbums = sortDirection.ToLower() == "descending"
                                    ? allAlbums.OrderByDescending(p => p.AlbumName).Skip(pageSize*pageIndex).Take(pageSize)
                                    : allAlbums.OrderBy(p => p.AlbumName).Skip(pageSize*pageIndex).Take(pageSize);

                var result = new WijmoGridResult { Items = allAlbums.ToList(), TotalRowCount = totalRowCount };

                return Json(result, JsonRequestBehavior.AllowGet);
            }
        }

Let’s walkthrough what this code does, because it does a lot.  The first six lines pulls out Request parameters that Wijmo will send.  Because it is possible for these values to be null, a little bit of self checking needs to take place.

Starting at the using statement, we’re opening a connection to our database with Entity Framework.  We use the idea of lazy loading to create a structured query so our round trip to SQL Server only happens once.  The first query creates a query for all the albums in the database.  This is needed to get the total row count.  Wijmo will use that to determine the paging requirements.

For sorting, you’ll probably be drawn to the epic IF..ELSE statement.  All this is doing is determining what column we want to sort by and how we want to sort it (ascending or descending), and apply the correct LINQ filter.  These calls are also using the Skip() and Take() technique for pagination. 

Finally, you might notice I don’t have the definitions for AlbumResult and WijmoGridResult.  Here is the code for those classes:

    public class AlbumResult
    {
        public string AlbumName { get; set; }
        public string ArtistName { get; set; }
    }

    public class WijmoGridResult
    {
        public List Items { get; set; }
        public int TotalRowCount { get; set; }
    }

The Client

Now that the server is configured, we need to change our Wijmo configuration a little bit to use the new settings.

	$(document).ready(function () {

		var dataReader = new wijarrayreader([
				{ name: "Artist", mapping: "ArtistName" },
				{ name: "Album", mapping: "AlbumName" }
			]);

		var dataSource = new wijdatasource({
			proxy: new wijhttpproxy({
				url: "@Url.Action("GetAlbumList")",
				dataType: "json"
			}),
			dynamic: true,
			reader: {
				read: function (datasource) {
					var count = datasource.data.TotalRowCount;
					datasource.data = datasource.data.Items;
					datasource.data.totalRows = count;
					dataReader.read(datasource);
					}
			}
		});

		$("#remoteTable").wijgrid({
			pageSize: 15,
			data: dataSource,
			allowPaging: true,
			allowSorting: true

		});
	});

At the top, we’re going to declare a new datareader for the JSON coming from the server.  This tells Wijmo how to map JSON properties to Columns in the grid. 

Next is the datasource.  This is a proxy for a URL.  Wijmo will do a GET on this URL to pull information.  The dynamic keyword tells Wijmo that the server will accept sorting, filtering, and paging requests.  If this is false or not set, Wijmo will not send the request parameters we mentioned earlier.  The reader tells WIjmo how to interpret the data coming in, and decipher row counts and then finally apply the datareader.

The last call is the actual WijGrid creation.  This shouldn’t be too different from what you’ve seen in the past.  The only thing that makes Wijmo use the server to gather data is the datasource. 

I hope this serves as an introduction to doing server side calls with Wijmo.  If you have any questions at all, feel free to post them to the comments or email me!

Kevin Griffin
keving@componentone.com

Follow Me On Twitter