Sorting and Paging
Sorting and paging are two distinct features. However, when implementing those in projects, developers get confronted to the same main technical issue: both turn out to be really complicated when working on big amounts of data. Furthermore, those features can be combined with one another. A good example of those features are web search engines like Microsoft Bing or Google Search: they provide to the end user a potentially huge number of search results, that's why they can't be displayed at once for obvious performance and readability reasons. Therefore, the search results are paged since we don't want to load all them but just a chunk of them, this way assuring an acceptable loading time. Moreover, those results are sorted by rank, and this rank isn't by page - which wouldn't be relevant at all in this scenario - but a rank on the full search result. This last notion implies that all results were read to be sorted, hence the prudence when adding sorting to applications.
CodeModeler provides all the needed pieces to implement such capabilities in a coherent and performant way, and this is what we'll describe in this section: how a developer can leverage those features in his application.
When displaying large amounts of data, it's often best to only display a portion of the data, allowing the user to step through the data ten or so records at a time. Additionally, the end user's experience can be enhanced if they are able to sort the data by one of the columns, a topic which is discussed in the Dynamic Sorting article.
To allow you to read only a page of data, CodeModeler automatically generates a paged version of all load, search and raw methods. For instance, the default generated LoadAll method has an equivalent PageLoadAll method, and if you create, say, a new LoadByStatus method in your model, you'll automatically get a PageLoadByStatus method as well.
As an example, here's the signature of the PageLoadAll method:
public static Sample.CustomerCollection PageLoadAll(int pageIndex, int pageSize, CodeModeler.Runtime.PageOptions pageOptions)-
pageIndex corresponds to the index of the current page to load (0 for the first page, 1 for the second, etc.),
-
pageSize corresponds to the size of a page: it's equivalent to the number of lines composing a page,
-
pageOptions is an object principally used for dynamic sorting, another feature which is described in the Dynamic Sorting section.
The LoadAll method actually uses that method with a pageIndex at int.MinValue, a pageSize set at int.MaxValue, and null as pageOptions.
Paging by pages of 20 lines and loading only the third page (records from 41 to 60) would be done by setting the pageIndex to 2, the pageSize to 20, and pageOptions to null.
Here's an example illustrating how to retrieve the third page programmatically:
var customers = CustomerCollection.PageLoadAll(2, 20, null);Two types of sorting are possible, and both are fully supported by CodeModeler: static sorting (or sorting, explained in this section) and dynamic sorting (explained in the next section).
Static sorting refers to loading a collection which will always be sorted by the specified column(s). The column(s) to sort by are defined at development time and can't be changed at runtime. On the other hand, dynamic sorting refers to the exact opposite: the sorted collection results of a sort on (a) column(s) defined at runtime. For instance, a user selects a property on an entity and asks for a sorted output. If the concerned entity contains several properties and could be potentially sorted on user demand by all of them: this would require dynamic sorting. On the other hand, if an entity could be sorted by only a couple of properties, static sorting should be used.
All static sorting capabilities are implemented in the data layer since databases provide highly efficient sorting capabilities. To retrieve a sorted collection, you need to declare the corresponding method and add the order by CodeModeler Query Language (CMQL) keyword. It's then possible to specify the order direction in the CMQL body.
Static sorting refers to loading a collection which will always be sorted by the specified columns. The columns to sort by are defined at development time and can't be changed at runtime. All static sorting capabilities are implemented in the data layer since databases provide highly efficient sorting capabilities. To retrieve a sorted collection, you need to declare the corresponding method and add the "order by" CMQL keyword. It's then possible to specify the order direction in the CMQL body.
Here's a sample model that has a method that can load all orders of a customer sorted by their date:
Note: By default, the “order by” CMQL keyword orders by ascending order, however you can specify to order by descending order by declaring the desc keyword after the column to order by (after Date in this sample).
Multi-column sorting is also supported:
The defined method will generate a stored procedure which will load all orders for a customer, sorted in ascending order, by their date and code. A LoadByCustomer method in the OrderCollection class in the BOM will be generated which will use the stored procedure. All upper layers using the BOM and the LoadByCustomer method will automatically get collections sorted by the Date and Code properties.
Dynamic sorting refers to sorting a collection based on columns defined externally at runtime. For instance, a user selects a property on an entity and asks for a sorted output. If the concerned entity contains several properties and could be potentially sorted on user demand by all of them: this would require dynamic sorting. Thus, there are two types of dynamic sorting: server side, and client side.
Server-side sorting is supported and is an out of the box feature provided by CodeModeler, whereas regarding client-side sorting, even though everything necessary is provided, an actual method doing the sort is not generated, but can easily be done as will see further.
Server-side sorting should be preferred in almost all cases to client-side sorting, since server-side sorting is handled in the data layer making it very performant. Moreover, the fact that all the sorting logic is done in the data layer, all upper layers benefit from it. This way, modifying the sorting logic will automatically update all upper layers.
By default, all dynamic server-side sorting is disabled since it's quite infrequent compared to static sorting, it's usually a very specific need in a business application and that it complicates the generated code. Enabling dynamic sorting can be done:
-
At the project level with the “Default Sortable” attribute: setting it to True will enable dynamic sorting for properties of all entities,
-
At the entity level with the “Default Sortable” attribute: setting it to True will enable dynamic sorting for all properties on the current entity,
-
At the property level with the “Is Sortable” attribute: setting it to True will enable dynamic sorting on the current property.
Technically when enabling dynamic sorting, it will change the generated stored procedure so that it uses an @_orderBy variable containing the name of the column to order by, and an @_orderByDirection indicating whether it should be ascending or descending. Then depending on the value stored in the variable, the returned collection will be ordered by a column or another.
Client-side sorting is necessarily dynamic and should be used with care, since performance issues could be encountered when sorting big collections. Server-side sorting should be preferred in most cases since it is done in the data layer, and will be more performant; plus, when implemented on the server side, all clients can benefit from it.
CodeModeler provides a Sort method to handle client-side sorting. This Sort method has two signatures:
-
public void Sort()
-
public virtual void Sort(System.Generic.Collections.IComparer<[EntityName]> comparer)
The first Sort uses the second one, passing null as a parameter. By doing so the Sort method will sort based on the CompareTo method which all CodeModeler-generated entities have since they all implement the IComparable interface. The CompareTo method of an entity compares by the property marked as comparer in the model, and if none is defined, the CompareTo method uses the entity key (EntityKey).
However, if ever a developer wants to sort an entity collection on another property than the comparer property, he should use the second version of the Sort method and provide an IComparer implementing the custom logic. Furthermore, CodeModeler provides a default comparer that can be used in those cases: the DataBinderComparer (and its generic counterpart) contained in the CodeModeler.Runtime.Utilities.
static void Main(string[] args)
{
var rand = new Random();
var orders = new OrderCollection();
for (int i = 0; i < 10; i++)
{
var order = new Order();
order.Id = rand.Next(1000);
order.Code = "C" + i.ToString();
orders.Add(order);
}
// Sort uses the comparer property which is the entity key by default (Id in our case).
orders.Sort();
Console.WriteLine("Sort by the Id property in ascending order:");
var index = 1;
foreach (var order in orders)
{
Console.WriteLine("Order #" + index++ + ": Id=" + order.Id + " Code=" + order.Code);
}
// Sort will sort by the Code property in ascending order.
orders.Sort(new DataBinderComparer<Order>("Code", ListSortDirection.Ascending));
Console.WriteLine("Sort by the Code property in ascending order:");
index = 1;
foreach (var order in orders)
{
Console.WriteLine("Order #" + index++ + ": Id=" + order.Id + " Code=" + order.Code);
}
Console.ReadKey();
}The DataBinderComparer can sort only by property types implementing the IComparable interface.