Introduction
My article, Client and Server-Side Data Filtering, Sorting, and Pagination with Angular NgExTable, describes the base structures and uses of the custom data grid tool. This is the second article of the NgExTable series, which details the multiple column sorting operations and workflow with this Angular UI tool, API data services, and the database stored procedure. Audiences who use the Microsoft Visual Studio and C# can download sources and play the code with all scenarios in the front-end, middle-tier data services, and database back-end. Audiences who download the source code for any platform other than using Microsoft technologies can still look into how the multiple column sorting works with the Angular data grid tool and the logic to generate client-side source data list or server-side mock data result sets. If you need to run the sample application with the previous Angular versions (8, 9, or 10), please see the details in the History section by end of the article.
The sample application demonstrates these unique features regarding the data list sorting:
- Providing different approaches to switch between single and multiple column sorting types
- Displaying multiple column sorting UI styles only when more than one sorted column is present
- Sorting sequence for any sortable column can be changed with free and easy selections
- Switching existing sorting sequence numbers intelligently with the position-down rule (see details in the Sorted Column and Sequence Selections section)
- Dynamically showing and hiding the multiple sorting command panel for using the on-submit pattern of the data access to avoid repeated and redundant calls for the data, especially for the server-side pagination operations
- Optional display of row grouping lines based on the first sorted column data
- Table-hosting component level configurations for different column sorting types and related options so that the settings can be different for the grid tool used on different pages in an application
- Code examples for source data list processed for multiple column sorting in the client-side JavaScript, API data services in C#, and the SQL Server database stored procedure
Set Up and Run Sample Application
NOTE: This section may repeat most of the setup steps in the first article for the NgExTable. The project and file structures are the same as in the source code if you have already downloaded from that article. Only the sorting configuration settings are different in the client-paging.component.ts and server-paging.component.ts files under the AppDev\src\app\NgExTableDemo folder.
The downloaded sources contain two different Visual Studio solution/project types. Please pick up one or both you would like and do the setup on your local machine. You also need the node.js (recommended version 14.x LTS or above) and Angular CLI (recommended version 11.x or above) installed globally on the local machine. Please check the node.js and Angular CLI documents for details.
You may check the available versions of the TypeScript for Visual Studio in the C:\Program Files (x86)\Microsoft SDKs\TypeScript folder. Both ASP.NET and Core types of the sample application set the version of TypeScript for Visual Studio to 4.0 in the TypeScriptToolsVersion node of SM.NgExTable.Web.csproj file. If you don't have the version 4.0 installed, download the installation package from the Microsoft site or install the Visual Studio 2019 version 16.8.x which includes the TypeScript 4.0.
NgExTable_AspNetCore_Cli
-
You need to use the Visual Studio 2019 (version 16.8.x) on the local machine. The .NET Core 5.0 SDK is included in the Visual Studio installation.
-
Download and unzip the source code file to your local work space.
-
Go to physical location of your local work space, double click the npm_install.bat and ng_build.bat (or ng_build_local.bat if not installing the Angular CLI globally) files sequentially under the SM.NgExTable.Web\AppDev folder.
NOTE: The ng build command may need to be executed every time after making any change in the TypeScript/JavaScript code, whereas the execution of npm install is just needed whenever there is any update with the node module packages. I do not enable the CLI/Webpack hot module replacement since it could break source code mapping for the debugging in the Visual Studio.
-
Open the solution with the Visual Studio 2019, and rebuild the solution with the Visual Studio.
-
Click the IIS Express toolbar command (or press F5) to start the sample application.
NgExTable_AspNet_Cli
-
Download and unzip the source code file to your local work space.
-
Go to physical location of your local work space, double click the npm_install.bat and ng_build.bat (or ng_build_local.bat if not installing the Angular CLI globally) files sequentially under the SM.NgExTable.Web\ClientApp folder (also see the same NOTE for setting up the NgTable_AspNetCore_Cli project).
-
Open the solution with Visual Studio 2017 or 2019 and rebuild the solution with the Visual Studio.
-
Click the IIS Express toolbar command (or press F5) to start the sample application.
The NgTable_Ng11_Scripts_AnyPlatform.zip file contains only the client script source code folders and files. If you use the systems other than the Microsoft platform, you can copy the pure Angular source code files to your own website project, perform the necessary settings using the npm and Angular CLI commands, and then run the sample application on your systems.
Since the data grid loaded with the client-side pagination sets the single column sorting by default, to look at the page using the multiple column sorting, you can click the Server-side Pagination menu item and then click Go button on the search panel.
You don't have to set up the server data source from the data-providing service application for the Server-side Pagination demo. By default, the sample application uses the server-mock-data.service.ts to simulate the server-side data request and response patterns and results. This also benefits the audiences who add the NgExTable_Scripts_AnyPlatform code files into their website project and run for the NgExTable example on their non-Microsoft platforms.
If you run the sample application with Visual Studio and would like to call the real server data source for the Server-side Pagination demo, I recommend performing these steps:
-
Go to my other article, ASP.NET Core: A Multi-Layer Data Service Application Migrated from ASP.NET Web API and set up the Core API data services following the instructions. Any version of the data service application is a compatible data source provider. The source code files for the data services can also be downloaded on the beginning of this article.
-
Start the data service application with the Visual Studio and keep the running solution with the IIS Express on the background.
-
In the ../app/NgExTableDemo/app.config.ts file, change the ServerPagingDataSource from mock to server.
export const ServerPagingDataSource:
string = 'server';
-
Edit and enable the active service URL if it's not correct for your settings.
export const WebApiRootUrl: string = "http://localhost:7200/api/";
-
Rebuild the Angular CLI by executing the ng_build.bat or ng_build_local.bat.
-
Press F5 to run the NgExTable demo application, select the Server-side Pagination left menu item, and click the Go button on the Search Products panel.
TIP: You can modify the source data to make the same value for columns to be sorted in several rows so that the multiple column sorting feature is meaningfully displayed.
-
The client data source file locations:
- NgExTable_AspNetCore_Cli: SM.NgExTable.Web/wwwroot/ClientApp/local-data-products.json
- NgExTable_AspNet_Cli: SM.NgExTable.Web/ClientApp/local-data-products.json
- NgExTable_Scripts_AnyPlateform: ClientApp/local-data-products.json
-
The server data source seeding file location in the data services:
- SM.Store.CoreApi (any version): SM.Store.Api.DAL\DataContext\StoreDataInitializer.cs
- SM.Store.WebApi: SM.Store.Api.DAL\StoreDataContextInitializer.cs
Or you can directly change the data values in the SQL Server database using the SQL Server Management Studio or query commands for the server data source.
UI Structures for Data Sorting
The below diagram illustrates the major components used for UI data sorting processes and their relationships.
These structures are added into the table-hosting HTML template based on the data sorting needs.
<table class="table table-condensed table-striped bottom-border"
[table-main]="pagingParams" (tableChanged)="onChangeTable($event)">
<thead>
<tr class="option-board-tr">
<th colspan="6">
<options (optionChanged)="onChangeOptions($event)"></options>
</th>
</tr>
<tr #trHead class="table-header-tr">
<th>Product Name<column-sort sortBy="ProductName"
sortDirection="asc" sequence="3"></column-sort></th>
<th>Category ID<column-sort sortBy="CategoryId"></column-sort></th>
<th>Category Name<column-sort sortBy="CategoryName"></column-sort></th>
<th>Unit Price<column-sort sortBy="UnitPrice"></column-sort></th>
<th>Status<column-sort sortBy="StatusDescription"
sortDirection="asc" sequence="2"></column-sort></th>
<th>Available Since<column-sort sortBy="AvailableSince"
sortDirection="desc" sequence="1"></column-sort></th>
</tr>
<tr>
<th colspan="6" class="sort-box-th">
<multi-sort-command (multiSortChanged)="onChangeTable($event)">
</multi-sort-command>
</th>
</tr>
</thead>
- - -
</table>
Here are some key points regarding the sorting-related directive and component structures.
-
TableMainDirective: This attribute directive for the HTML table is a component without the HTML template. It acts as the local service privately for the current table. It can be imported and injected into any child component that is only declared within the current table scope. Thus, in the sample application, those child components, OptionsComponent, ColumnSortingComponent, and MultiSortingCommandComponent, can call the tableMainDirective as a central point for communications.
-
OptionsComponent: It is placed within the HTML table header. The component and its HTML template are pre-defined in the NgExTable library packages so that the code in the client calling side (the NgExTableDemo section of the sample application) would be very simple. The component transfers the data and submits changes via the tableMainDirective.
-
ColumnSortingComponent: It is used to display and handle sorting icons and sequence numbers. Multiple instances of the component can be defined for the sortable columns as shown in the above HTML code. Thus, one-to-many relationship exists between the parent TableMainDirective and child ColumnSortingComponent instances. If executing child code pieces initiated from the parent, then the same code in all child instances will be iterated.
-
MultiSortingCommandComponent: It renders the multiple column sorting command panel for submitting or cancelling the sorting request with the “on-submit” pattern. This component also only communicates with the TableMainDirective through which the input and output data operations are performed. The UI panel is dynamically rendered and displayed based on the required multiple column sorting operations (see details in the later section, Multiple Column Sorting Command Panel).
Sorting Types and Configurations
Although the multiple column sorting feature is available, sometimes a single column sorting of the data option may still be practical in many cases. The sample application can be configured to run either single column sorting type only or combination of single and multiple column sorting types. Those configurations must be in the table-hosting component, not the global, level since multiple data grids with different settings may be used in an application.
Single Column Sorting Only
This is the traditional operation mode for the data grid display. You can use this type if the multiple column sorting is not needed by the design or the multiple column sorting option is not available from the data sources.
To run the application with the single column sorting mode only, set this line in the ngOnInit method of the table-hosting component class:
this.tableMainDirective.sortingRunMode = 0;
In the sample application, the Client-side Pagination demo page loads the data grid with the single-column sorting type only. You can comment out this configuration line or set the value to 1 to enable the switchable single and multiple column sorting types for this page.
Switchable Single and Multiple Column Sorting
With this setting, both single and multiple column sorting types can co-exist for the data grid. But the question is how to easily and intelligently switch between the single and multiple column sorting types. We will discuss this topic in details with two approaches, dropdown selection and Ctrl (or Shift) key operation, in the next section.
To run the application with the switchable, set this line in the ngOnInit method of the table-hosting component class. You can also omit this line since it's the default setting.
this.tableMainDirective.sortingRunMode = 1;
By the default, the sortingTypeSwitch is set to using the dropdown selection. See the details in the next section for using the Ctrl/Shift key approach.
If using the dropdown selection, you may also to set the default loading type for the single or multiple column sorting. Otherwise, the default setting is to load the single column sorting as active selected type.
this.tableMainDirective.sortingOption = 'multiple';
In the sample application, all configuration items for column sorting are defined with comments in the TableMainDirective controller class.
sortingRunMode: number = 1;
sortingTypeSwitch: number = 0;
sortingOption: string = 'multiple';
enableOptionBoard: string = '';
showOptionBoardContent: string = '';
showGroupingLines: string = '';
All configuration items are also listed with comments in the ngOnInit method of the ClientPagingComponent and ServerPagingComponent classes to demonstrate how to use or overwrite the defaults. Feel free to enable, disable, or change the item lines for testing the data sorting results and display after you have known all details of the sample application.
Switching between Single and Multiple Column Sorting Options
The sample application demonstrates two different approaches for switching between single and multiple column sorting types, each with its pros and cons. For your real application development, you can implement one of the approaches based on your preference and business requirements.
Selecting Sort Type from Dropdown List
This approach is configured as using the dropdown selection when the value of TableMainDirective.sortingTypeSwitch value is 1.
sortingTypeSwitch = 1;
The OptionsComponent and its template are created for the dropdown list and selection display. In the OptionsComponent, the dropdown selected value is assigned to the TableMainDirective.sortingOption. When the option is changed, the switchSortingOption method is then called to process the changes in sorting logic and data results. Since the TableMainDirective is injected into the contract of the OptionsComponent class, the properties and methods in the TableMainDirective can directly be accessed in the OptionsComponent.
onSortingOptionChange($event: any) {
this.tableMainDirective.sortingOption = this.sortingOption;
this.tableMainDirective.switchSortingOption();
}
The option panel and column headers, when loaded with multiple column sorting by default, look like the below:
When changing to the single column sorting, the process automatically takes the first sorted column from the previous multiple column sorting.
With the explicit selection, the user can clearly know the current sorting type and the consequences of conducted actions. However, this approach needs an additional component and space for the dropdown display on the page.
Clicking Sorting Icons with Ctrl or Shift Key
This approach or feature is not loaded by default in the sample application. You need to enable it by setting the value of TableMainDirective.sortingTypeSwitch value to 0 in the ngOnInit method of the table-hosting component controller class.
sortingTypeSwitch = 0;
With this approach, regularly clicking any sorting icon on any sortable column always proceeds with the single column sorting type. When clicking any sorting icon on the second sortable column while pressing the Ctrl or Shift key, the process will go to the multiple column sorting workflow. The toggelSort method in the ColumnSortingComponent checks such user actions.
toggleSort(obj: any, $event: any) {
if (this.config.sortingTypeSwitch == 0 && ($event.ctrlKey || $event.shiftKey) &&
!this.tableMainDirective.baseSequenceOptions.find(a => a.value > 1)) {
if (this.config.sortingOption == 'single') {
this.tableMainDirective.sortingOption = 'multiple';
}
}
- - -
}
At this point, any user action will follow the multiple column sorting scenarios, the same as those used with selecting the multiple column sorting from the dropdown list. No explicit structure for any selection is needed though. The downsides of the approach are also obvious.
- Users may not clearly know the operation with the Ctrl or Shift key so that it's better to provide additional instructions.
- For switching back to the single column sorting type, it's weird if still using the Ctrl or Shift key. Thus, some other ways need to be considered.
You can remove the sorted columns by clicking the x button from the sequence dropdowns until only one sorted column is left on the data grid, which then becomes the single column sorting type. This, however, is certainly a cumbersome way for the purpose. To quickly switch to the single column sorting type, a button, Go to Single Column Sorting, is added onto the multi-sorting command panel. The details of the panel will be described in later section but here just shows the panel with the button which is rendered on the panel only for Ctrl or Shift key activation pattern. Clicking the button will switch immediately to the single column sorting type, leaving the previously first sorted column there. You can also notice that no sorting type dropdown is displayed.
Column and Sequence Selections
The multiple column sorting feature is implemented with complex processing logic for selections of sorted columns and sequences. Audiences may not have to know the internal code details when using the feature if no modification for the logic is needed, but below are outlined the essential points of implementations and practices.
-
The ColumnSortingComponent object instance is created one for each sortable column. The instances set the sorting icons and sequence display with the dropdown list on the column headers.
-
The parent TableMainDirective object governs multiple instances of the ColumnSortingComponent by the data members sortableList and baseSequenceOptions arrays. For example, the sortableItem is created in the ColumnSortingComponent and then added into the sortableList array in the TableMainDirective.
In the ColumnSortingComponent:
this.sortableItem = {
sortBy: this.sortBy,
sortDirection: this.sortDirection || '',
sequence: this.sequence || -1
};
this.tableMainDirective.initSortableList(this.sortableItem);
In the TableMainDirective:
initSortableList(sortableItem: SortableItem) {
- - -
this.sortableList.push(sortableItem);
- - -
}
-
The toggleSort method in the ColumnSortingComponent handles the changes in sorted column selections and updates the sorting direction for the corresponding sortableItem.
toggleSort() {
- - -
switch (this.sortableItem.sortDirection) {
case "asc":
this.sortableItem.sortDirection = "desc";
break;
case "desc":
this.sortableItem.sortDirection =
this.toggleWithOriginalDataOrder ? "" : "asc";
break;
default:
this.sortableItem.sortDirection = "asc";
break;
}
- - -
}
-
When the sorting sequence changes, the code in the onSequenceChange method of the ColumnSortingComponent needs to call the parent TableMainDirective for re-arranging the sequence values and refreshing overall sorting settings since any change in a column will affect behaviors of other columns.
onSequenceChange(event: any) {
let oldSeq: number = this.sortableItem.sequence;
this.sortableItem.sequence = event.value;
this.tableMainDirective.rearrangeSequence
(oldSeq, this.sortableItem.sequence, this.sortBy);
this.tableMainDirective.refreshSortSettings();
- - -
}
-
The sorting sequence arrangements are based on these rules:
-
The next available sequence number is automatically assigned to any newly added column for the sorting.
-
The maximum available sequence number is dynamically updated with only the number of active sorted columns. For example, if three sorted columns are selected, then the sequence dropdown list only contains 1, 2, and 3.
-
Changing any column sequence by selecting the number from the dropdown list will automatically re-arrange the sequence numbers towards the larger number side. For example, if changing the sequence number from 4 to 2, the previous 2 will be 3, and previous 3 be 4. The rearrangeSequence method in the TableMainDirective implements the desired rules and logic.
rearrangeSequence(oldSeq: number, newSeq: number, sortBy: string) {
if (oldSeq > 0 && newSeq == -1) {
this.sortableList.forEach((item: SortableItem, index: number) => {
if (item.sequence > oldSeq) {
item.sequence--;
}
});
}
else {
this.sortableList.forEach((item: SortableItem, index: number) => {
if (item.sortBy != sortBy) {
if (item.sequence >= newSeq && item.sequence < oldSeq) {
item.sequence++;
}
else if (item.sequence <= newSeq && item.sequence > oldSeq) {
item.sequence--;
}
}
});
}
-
In the TableMainDirective, any escalated changes in sorted column selections and/or sequences will be transferred to the pagingParams.sortList array that can be sent to the table-hosting component for refreshing the data grid. If current sorting option is single column type, the pagingParams.sortList is populated with only the first sortableItem from the sortableList.
updatePagingParamsWithSortSettings() {
this.pagingParams.sortList = [];
for (let num: number = 1; num <= this.sortableList.length; num++) {
for (let idx: number = 0; idx < this.sortableList.length; idx++) {
if (this.sortableList[idx].sequence == num) {
let sortItem: SortItem = {
sortBy: this.sortableList[idx].sortBy,
sortDirection: this.sortableList[idx].sortDirection
};
this.pagingParams.sortList.push(sortItem);
break;
}
}
if (this.sortingOption == 'single' && num == 1) {
break;
}
}
}
Multiple Column Sorting Command Panel
For the multiple column sorting, most Angular data grid tools that could be found across the Internet and software market use the "on-change" data access pattern in which any change action on the sorted column selection or sequencing would reload the data items to the grid, no matter if it's an intermediate step. As a result, any designated sorting operation, if consisting of multiple steps, would call for the data and refresh the grid multiple times.
To resolve the issue, the NgExTable introduces the MultiSortingCommandComponent and its HTML template with the "on-submit" data access pattern. The panel can dynamically be expended and collapsed directly under the table header.
-
Add the <multi-sort-command> tag into the second row of the <thead> in the table-hosting component template.
<thead>
<tr #trHead>
<!--
- - -
</tr>
<tr>
<th colspan="6" class="sort-box-th">
<multi-sort-command (multiSortChanged)="onChangeTable($event)">
</multi-sort-command>
</th>
</tr>
</thead>
-
The command panel would automatically be shown when any set of below conditions is met.
Condition set #1:
- The current sorting option is in the multiple column type mode with at least one existing sorted column.
- Clicking the sorting icon on any other column to turn on sorting or change the sorting direction.
Condition set #2:
- There are already at least two sorted columns.
- Clicking any sequence dropdown and having made a sequence number change.
Below shows the command panel opened with the dropdown selection option switching type.
-
The user can comfortably and repeatedly make any desired change in column and sequence selections when the command panel is open. The sorting request will only be submitted if clicking the OK button. Alternatively, the user can cancel the changes or clear all sorting settings for getting a non-sorted data set.
-
The panel will automatically be closed whenever clicking the OK, Cancel, or Clear Column Settings button, or any action outside the panel if the panel is open. The command panel is not the modal type so that any action outside the panel will be treated as an operation of cancellation.
-
Showing and hiding the panel is controlled by the value of the showMultiSortPanel variable in the MultiSortingCommandComponent. Requests of getting and setting the variable values are sent from other components through the subscription approaches.
-
Setting the showMultiSortPanel variable to false directly in the MultiSortingCommandComponent when clicking any button on the command panel. The panel will then be closed with this action after the corresponding operation is done.
-
Since most top-level processes of multiple column sorting are performed in the TableMainDirective, and calls from this parent to the child component is needed, the methods for getting and setting the showMultiSortPanel value are added into the TableMainDirective. These methods can be called from multiple places of the TableMainDirective itself and the ColumnSortingComponents.
getShowMultiSortPanelFlag(): boolean {
let subjectParam: NameValueItem = {
name: 'getShowMultiSortPanelFlag',
value: undefined
}
this.multiSortCommandComponent.next(subjectParam);
return subjectParam.value;
}
setShowMultiSortPanelFlag(flagValue: boolean) {
let subjectParam: NameValueItem = {u
name: 'setShowMultiSortPanelFlag',
value: flagValue
}
this.multiSortCommandComponent.next(subjectParam);
}
-
The method calls are subscribed in the constructor of the MultiSortingCommandComponent. When a calls comes from the parent TableMainDirective, it actually gets and sets the showMultiSortPanel variable value in the MultiSortingCommandComponent.
let pThis: any = this;
this.tableMainDirective.multiSortCommandComponent$.subscribe(
(subjectParam: NameValueItem) => {
if (subjectParam.name == "setShowMultiSortPanelFlag") {
pThis.showMultiSortPanel = subjectParam.value;
}
else if (subjectParam.name == "getShowMultiSortPanelFlag") {
subjectParam.value = pThis.showMultiSortPanel;
}
}
);
Default Settings for Sorting Parameters
With the NgExTable, the default values of sorting parameters can be specified in one of either two places.
-
Add the sortDirection and sequence attributes and values into the column-sort tags in the table-hosting HTML template.
<tr>
<th>Product Name<column-sort sortBy="ProductName"
sortDirection="asc" sequence="3"></column-sort></th>
<th>Category ID<column-sort sortBy="CategoryId"></column-sort></th>
<th>Category Name<column-sort sortBy="CategoryName"></column-sort></th>
<th>Unit Price<column-sort sortBy="UnitPrice"></column-sort></th>
<th>Status<column-sort sortBy="StatusDescription"
sortDirection="asc" sequence="2"></column-sort></th>
<th>Available Since<column-sort sortBy="AvailableSince"
sortDirection="desc" sequence="1"></column-sort></th>
</tr>
-
Add elements into the pagingParams.sortList array in the ngOnInit() of the table-hosting component. The element orders determine the column sorting sequences.
this.pagingParams = {
pageSize: pageSize !== undefined ? pageSize : 10,
pageNumber: 1,
sortList: [{
sortBy: 'AvailableSince',
sortDirection: 'desc'
}, {
sortBy: 'StatusDescription',
sortDirection: 'asc'
}, {
sortBy: 'ProductName',
sortDirection: 'asc'
}],
changeType: TableChange.init
}
If no default item and value exist, the grid will initially be populated without any column sorting of the data.
Display Enhancement with Sorted Data
Many display options for the sorted data list can be added into the page by changing the styles of the nativeElement items in the AfterViewInit event handler or after the data loading. The sample application shows an example of adding row grouping lines into the grid display based on the first sorted column. The main method, setRowGroupLines, is created in the TableMainDirective to dynamically process the DOM based style settings. Audiences can look into the code details in the table-main.directive.ts file.
Such style settings are done in the library, rather than in the UI, which is just an example to save the code in the UI parts. Passing the DOM elements from the table-hosting component to the TableMainDirective is still needed. Thus, two ViewChild objects are defined in the table-hosting component, where the trHead and trItems are created for template variables, #trHead and #trItems, respectively.
@ViewChild('trHead', { static: true }) trHead: ElementRef;
@ViewChildren('trItems') trItems: QueryList<any>;
After the data loading, the code then calls the setRowGroupLines method by passing two ViewChild object instances.
this.tableMainDirective.setRowGroupLines(this.trHead, this.trItems);
The showGroupingLines option can also be enable and disabled using the checkbox on the option switchboard. You may want to turn off the grouping lines if rows sorted by the column are pretty unique, in which the grouping lines may not make much more sense.
The below screenshot shows the row grouping lines in action with the AvailableSince as the first sorted column.
Various other display styles can also be enhanced using the described approach above, such as changes in row background color, text font type and color, merging cells, or even adding sub-rows. Feel free to experiment by yourselves.
Sorting Source Data for Client-side Pagination
The TypeScript code in the NgExTable/client-pagination.service.ts performs the sorting logic for the client-side pagination data list. In the sample application, the same native JavaScript array.sort function and associated processes are also used for the server-mock data provider.
changeSort(pagingParams: PagingParams, data: Array<any>): Array<any> {
let pThis: any = this;
let rtnArr: any = data.sort((previous: any, current: any) => {
let idx: number = 0;
while (idx < pagingParams.sortList.length) {
if (current[pagingParams.sortList[idx].sortBy]
!== previous[pagingParams.sortList[idx].sortBy]) {
return pThis.doSort(previous, current, pagingParams.sortList, idx);
}
idx++;
}
return 0;
});
return rtnArr;
}
private doSort(previous: any, current: any,
sortList: Array<SortItem>, idx: number): number {
if (previous[sortList[idx].sortBy] === null) {
return 1;
}
else if (current[sortList[idx].sortBy] === null) {
return -1;
}
else if (previous[sortList[idx].sortBy] > current[sortList[idx].sortBy]) {
return sortList[idx].sortDirection === 'desc' ? -1 : 1;
}
else if (previous[sortList[idx].sortBy] < current[sortList[idx].sortBy]) {
return sortList[idx].sortDirection === 'asc' ? -1 : 1;
}
return 0;
}
Here are the important points of the code:
-
The code handles both single and multiple column sorting scenarios.
-
Within the data.sort method that is actually an outer loop, the inner while loop is used for searching the sortable columns with the sequence in the sortList array.
-
The doSort comparing function called from the while loop conducts the real data sorting for each iterated column group.
-
The logic sorts the null value to the last, no matter what sorting direction is. If you apply different rules for the null value, you may change the return index numbers in the doSort function, for example, switching between -1 and 1 in the first if and else if conditions to have null value always sorted the first.
Sorting Source Data for Server-side Pagination
The sample application presents the examples of the server-side data sorting management with both the LINQ Expression and the database stored procedure. These are implemented with the Microsoft technologies so that the audiences need to open the source code projects using the Visual Studio tool to see the demo results. Setting up the Visual Studio API data service projects locally is detailed in the Set Up and Run Sample Application section. Or, you can directly see the instructions in the article ASP.NET Core: A Multi-Layer Data Service Application Migrated from ASP.NET Web API.
LINQ Expression
The LINQ Expression approach is a choice for some simple requests without complex business rules, many table joins, or multiple database instances. The essence of this approach is to construct the LINQ Expression tree nodes with the passed sortList collection as the single or multiple column sorting parameter. The logic is processed by the AsSortedQueryable method in the SM.Store.CoreApi_[version]\SM.Store.Api.Common\Classes\GenericMultiSorterPager.cs. The comment lines explain the operations performed by the code.
public static IOrderedQueryable<t> AsSortedQueryable<t>
(this IQueryable<t> source, List<sortitem> sortList)
{
if (sortList.Count == 0)
{
return null;
}
var param = Expression.Parameter(typeof(T), string.Empty);
var property = Expression.PropertyOrField(param, sortList[0].SortBy);
var sort = Expression.Lambda(property, param);
MethodCallExpression orderByCall = Expression.Call(
typeof(Queryable),
"OrderBy" + (sortList[0].SortDirection == "desc" ? "Descending" : string.Empty),
new[] { typeof(T), property.Type },
source.Expression,
Expression.Quote(sort));
if (sortList.Count > 1)
{
for (int idx = 1; idx < sortList.Count; idx++)
{
var item = sortList[idx].SortBy;
param = Expression.Parameter(typeof(T), string.Empty);
property = Expression.PropertyOrField(param, item);
sort = Expression.Lambda(property, param);
orderByCall = Expression.Call(
typeof(Queryable),
"ThenBy" + (sortList[idx].SortDirection == "desc" ?
"Descending" : string.Empty),
new[] { typeof(T), property.Type },
orderByCall,
Expression.Quote(sort));
}
}
return (IOrderedQueryable<t>)source.Provider.CreateQuery<t>(orderByCall);
}
If you would like to test the LINQ Expression approach, enable the line in the NgExTableDemo\services\app.config.ts of the Angular sample application. You may adjust the API data service URL if it’s different from the downloaded original.
export const ServerPagingDataSource: string = 'server';
export const WebApiRootUrl: string = 'http://localhost:7200/api/';
Database Stored Procedure
Using a stored procedure is the preferred approach for retrieving the paginated, filtered, and sorted data list, especially when multiple column sorting, multiple instances of databases, or complex business rule processing are needed. The ASP.NET Core API data service sample application contains the code to create the stored procedure, GetPagedProductList, in the SM.Store.CoreApi_[version]\SM.Store.Api.DAL\DataContext\StoreDataInitializer.cs file. When running the data service application, the stored procedure can automatically be added into the SQL Server database specified in the appsettings.json configuration file. The stored procedure may not be working if using the in-memory database setting.
Doing multiple column sorting code with the stored procedure is rather simple and straightforward.
-
From the Angular sample application code, include the sortList in the paginationRequest object for the AJAX call (see details in the ../SM.NgExTable.Web/ClientApp/app/NgExTableDemo/server-paging.component.ts).
getProductListRequest(): any {
let req: any = {
- - -
paginationRequest: {
sortList: []
},
};
- - -
if (this.pagingParams.sortList.length > 0) {
req.paginationRequest.sortList = this.pagingParams.sortList;
}
return req;
};
-
The data service controller method, Post_GetPagedProductListSp, converts the sortList generic List to the sortString with the format for the SQL ORDER BY clause.
if (request.PaginationRequest.SortList != null &&
request.PaginationRequest.SortList.Count > 0)
{
foreach (var item in request.PaginationRequest.SortList)
{
if (sortString != " ") sortString += ", ";
sortString += item.SortBy + " " + item.SortDirection;
}
}
-
The sortString is passed to the GetPagedProductList stored procedure and embedded in the dynamic query there.
IF @SortString != ''
SET @Query = @Query + ' ORDER BY ' + @SortString
If interested, audiences can also look into the entire script of the stored procedure in the included file, StoreCF8.sql, downloaded from the ApiDataServices.zip.
Summary
The multiple column sorting feature is a nice add-on to a web application with the data grid display. This article and sample application can be helpful resources for how to implement such a feature in both client UI and server processing sides.
History
- 28th September, 2019
- Original post with Angular 8 CLI
-
20th December, 2020
- Updated source code with the Angular 11 CLI
- Updated website project with the ASP.NET Core 5.0
- Edited article text in some sections for the updates
- If you need to run the sample application with previous Angular version 8, 9, or 10, you can download the package.json file for the application, Package.json_Ng8-9-10.zip, replace the package.json file in the existing application with the version you would like, than do the same based on the instructions in the Set Up and Run Sample Application section. The Angular 11 source code of the sample application is fully compatible with the Angular version 8, 9, and 10 without major breaking changes.
Shenwei is a software developer and architect, and has been working on business applications using Microsoft and Oracle technologies since 1996. He obtained Microsoft Certified Systems Engineer (MCSE) in 1998 and Microsoft Certified Solution Developer (MCSD) in 1999. He has experience in ASP.NET, C#, Visual Basic, Windows and Web Services, Silverlight, WPF, JavaScript/AJAX, HTML, SQL Server, and Oracle.
Submit an article or tip
