2. Adding simple Criteria, sort, and data paging

Description

This example extends sample 1 (Handling a fetch request from RestDataSource using .NET+NHibernate), but will have support for pagination, filtering and sorting built onto it.

(Note: although this is based on sample 1, a new solution has been built for this from the ground up, so the namespace will be App2.xxx, not App1.xxx  Other than this small change, the application being built is exactly the same).

Changes to client side code

Firstly, Change the DataSource so, instead of using the URL parameters for sending data to the back-end (filter, etc), send parameters as a JSON payload to the POST method. Additionally, configure the ListGrid to let the back-end do the pagination and filtering.

For this, remove the 'dataFetchMode:"local"' attribute on the grid. This will cause the grid to send criteria and pagination information to the back-end:

isc.ListGrid.create({
    ID: "suppyItem",
    width: 700, height: 224, alternateRecordStyles: true,
    dataSource: suppyItem,
    showFilterEditor: true,
	autoFetchData:true,
    dataPageSize:20
});

Please note:, Grid filters have also been enabled with showFilterEditor:true. This allows for easier testing later. Also note that the dataPageSize has been set to 20, to show pagination working.

Additionally, change the DataSource definition to use POST instead of the default GET HTTP method used by normal fetch operations. This will post the JSON as a payload to the server instead of passing it the variables in a URL. To do this, add an operationBinding definition to the DataSource. The DataSource should now look like this:

isc.RestDataSource.create({
	ID:"suppyItem",
	fields:[
		{name:"itemID", type:"sequence", hidden:"true", primaryKey:"true"},
        {name:"itemName", type:"text", title:"Item", length:"128", required:"true"},
        {name:"SKU", type:"text", title:"SKU", length:"10", required:"true"},
        {name:"description", type:"text", title:"Description", length:"2000"},
        {name:"category", type:"text", title:"Category", length:"128", required:"true", foreignKey:"supplyCategory.categoryName"},
        {name:"units", type:"enum", title:"Units", length:"5",
            valueMap:["Roll", "Ea", "Pkt", "Set", "Tube", "Pad", "Ream", "Tin", "Bag", "Ctn", "Box"]
        },
        {name:"unitCost", type:"float", title:"Unit Cost", required:"true",
            validators:[
                {type:"floatRange", min:"0", errorMessage:"Please enter a valid (positive) cost"},
                {type:"floatPrecision", precision:"2", errorMessage:"The maximum allowed precision is 2"}
            ]
        },

        {name:"inStock", type:"boolean", title:"In Stock"},
        {name:"nextShipment", type:"date", title:"Next Shipment"}
	],

        dataFormat: "json",
        criteriaPolicy:"dropOnChange",
        operationBindings:[
            {
                operationType: "fetch", dataProtocol: "postMessage", dataURL: "/RequestHandler/fetch"
            }
        ]
});

As an additional change, the criteriaPolicy  is now set to "dropOnChange", which will force the DataSource to send requests to the back-end each time the criteria changes.

Changes on the back-end

If the sample,was run at this stage the payload sent to the server will look similar to this:

{
    "dataSource":"suppyItem",
    "operationType":"fetch",
    "startRow":0,
    "endRow":20,
    "textMatchStyle":"substring",
    "componentId":"suppyItem",
    "data":{
        "units":"Pkt",
        "unitCost":"a"
		...
    },
    "oldValues":null
}

Define an object which is created by de-serializing from JSON and which mimics the previous JSON. This should be called DSRequest:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace App2.Utils
{
    public class DSRequest
    {
        public string dataSource { get; set; }
        public string operationType { get; set; }
        public int startRow { get; set; }
        public int endRow { get; set; }
        public string textMatchStyle { get; set; }
        public string componentId { get; set; }

        public Dictionary<string, string> data { get; set; }
        public string[] sortBy { get; set; }

        public Dictionary<string, string> oldValues { get; set; }
    }
}

Also, change the controller to handle the JSON payload the front-end is sending with the POST call:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.IO;

using NHibernate;
using App2.Utils;
using App2.Dao;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;

namespace App2.Controllers
{
    public class RequestHandlerController : Controller
    {
        public ActionResult fetch()
        {
            // get request as stream and read the json payload
            var sr = new StreamReader(Request.InputStream);
            var requestBody = sr.ReadToEnd();

            // convert the payload to our DSRequest object
            DSRequest req = JsonConvert.DeserializeObject<DSRequest>(requestBody);

            using (ISession session = NHibernateHelper.OpenSession())
            {
                // build up the query
                string hql = "from supplyItem s where ";
                string hqlCount = "select count (*) from supplyItem s where ";

                if (req.data.Keys.Count != 0)
                {
                    foreach(string key in req.data.Keys)
                    {
                        // condition will be something like key = :key (:key being parameter placeholder)
                        hql = hql + "s." + key + "=" + ":" + key + " and ";
                        hqlCount = hqlCount + "s." + key + "=" + ":" + key + " and ";
                    }
                }

                // quick'n dirty way to make sure we have a condition all the time (because of the were we started with
                hql = hql + " 1=1 ";
                hqlCount = hqlCount + " 1=1 ";

                if (req.sortBy != null)
                {
                    // set the orderBy
                    hql = hql + " order by ";

                    // we start to build a coma separated list of items. First item won't have coma
                    // but every possible next will do
                    string separator = "";
                    foreach (string column in req.sortBy)
                    {
                        // if column name is with -, then ordering is descending, otherwise ascending
                        if (column.StartsWith("-"))
                        {
                            hql = hql + separator + column.Substring(1) + " DESC";
                        }
                        else
                        {
                            hql = hql + separator + column + " ASC";
                        }

                        separator = ",";
                    }
                }

                // create the query with the hql string we built
                var query = session.CreateQuery(hql);
                var queryCount = session.CreateQuery(hqlCount);

                // update the parameters we got from the request
                if (req.data.Keys.Count != 0)
                {
                    foreach (string key in req.data.Keys)
                    {
                        query.SetParameter(key, req.data[key]);
                        queryCount.SetParameter(key, req.data[key]);
                    }
                }

                // set start row and number of rows on the query itself
                query.SetMaxResults(req.endRow - req.startRow);
                query.SetFirstResult(req.startRow);

                // run the query and get all the data
                var products = query.List<supplyItem>();

                // create a response object
                DSResponse dsresponse = new DSResponse();

                // set total rows using the count query
                dsresponse.totalRows = (int)queryCount.UniqueResult<long>();

                // set the response data
                dsresponse.data = products;
                dsresponse.startRow = req.startRow;
                dsresponse.endRow = dsresponse.startRow + products.Count();

                // sanity check, if no rows, return 0
                if (dsresponse.endRow < 0)
                {
                    dsresponse.endRow = 0;
                }

                dsresponse.status = 0;

                // convert it to JSON

                JsonNetResult jsonNetResult = new JsonNetResult();
                jsonNetResult.Formatting = Formatting.Indented;
                jsonNetResult.SerializerSettings.Converters.Add(new IsoDateTimeConverter());
                jsonNetResult.SerializerSettings.NullValueHandling = NullValueHandling.Ignore;

                jsonNetResult.Data = dsresponse;

                // return it to front-end
                return jsonNetResult;
            }
        }
    }
}

Although the code looks a little more complicated than in the previous sample, it is actually very straight forward:

1. Create a stream to read the JSON payload as a string,The string is then used to de-serialize the DSRequest object from.

2. Using this object, create a query for getting the records, and another for getting the total count of records. Also, based on the request object, set up the sorting criteria of the records to send back.

3. Create a DSResponse object, run the queries and fill the fields of the response object and send it back to the front-end, as in the previous sample.

To support testing, add the following code to create more records in the database, to allow for better testing of pagination and filtering. Simply change the Global.asax.cs  function Application_Start() method as follows:

protected void Application_Start()
        {
            Configuration cfg = new Configuration();
            cfg.Configure();

            cfg.AddAssembly(typeof(Dao.supplyItem).Assembly);

            AreaRegistration.RegisterAllAreas();

            RegisterRoutes(RouteTable.Routes);

			// Add 100 objects in the database
            using (ISession session = NHibernateHelper.OpenSession())
            {
                for (int i = 0; i < 100; i++)
                {
                    supplyItem itm = new supplyItem();
                    itm.itemName = "item-" + i;
                    itm.SKU = "sku-" + i;
                    itm.unitCost = 0.3;
                    itm.units = "Ea";
                    itm.inStock = true;
                    itm.category = "Inserted by global.asax";
                    session.Save(itm);
                }
            }

			// end code adding 100 objects in the database
        }

Note: This will add 100 records into the database every time the application is started, so it may be sensible after the first run to comment out this code or completely remove it, to avoid 100 records being added every time the application is started or restarted.

Restart the application and view the grid, Note that each time the filter or sort criteria is changed, a request is sent up to the server with the new values. Additionally, as you scroll down (assuming there are more than 20 rows in the database with this filter criteria) you will see that the grid will send up a new request to the server to retrieve the subsequent pages.

A Visual Studio solution with the complete source code can be downloaded from here.