Flat Data - Lazy Load - Dot Net Sample

Among the most powerful features of the grid is the building support for lazy loading of large datasets. For flat data this is done via the use of the filterPageSortMode property. This is a key property to understand. There are two values for this property, “server” and “client”. Let us assume you are working on a Human Resource Management Application. You have a DataGrid that shows a list of Employees. You have a few hundred Employees to show. This can be easily accomplished by getting the list of employees from the server, and setting the data provider property of the grid to an Array that contains this list of Employees. As you need, you enable filters, footers, paging, export, print, etc, and all is well and good. You will be up and running in no time with filterPageSortMode=client (The default setting). The grid will take care of paging, filtering and cross page sorting for you. However, now consider a scenario where you have to display a time sheet search page. Each employee creates hundreds of time-sheets each year, and multiply that by the number of employees, you are looking at thousands, even hundreds of thousands of records of data. Although it may be possible to load thousands of records in any DataGrid (including ours) with no noticeable drag, this is not recommended, and often unnecessary. What we suggest in this scenario is you use the filterPageSortMode =”server”. What the product does, in this setup, is it assumes that the current Array is a part of a much larger record-set, and you are showing just the current page of data. So if there are 100,000 records in the database, and the pageSize is 50, the grid will show the 50 records, and provide paging UI for the user to navigate to any record in the result-set. At any point in time, no more than 50 records will be loaded on client memory. This setup, will require a little more effort in terms of configuration, but it will be considerably easier to do this with our grid as opposed to building it yourself, because the product is architected to cater to a scenario like this.

filterPageSortMode=client

When this property is set to client mode, there's very little work that you have to do. Just give us an array of objects and the gird takes care of paging sorting, filtering, export and everything.. We are able to do this because we have all the data on the client in memory. so when your filter we can run through the entire data set and get you the records that you're filtering on. The same applies to the sort. Since all the pages of data are re loaded in memory all we have to do is to navigate the grid to the page that your request when you click on the paging buttons. similarly when you click on the print or the export buttons we have the entire dataset loaded in memory so were able to export or print all the data without any interaction from the server.

filterPageSortMode=server

However in server mode, things are a lot different. At any point in time we only have the current page of data in memory. This means when you click on the paging buttons we have to go back to the server to get the requested page of data. When you click on the sort headers we have to run the sort on the server and get only the current page of data according to the updated sort criteria. Along the same lines when you run a filter, we have to go back to the server and run the filter on the server potentially converting it into a SQL statement and returning the results of that SQL statement on basis of the current page filter and sort criteria. Finally when you click on the train or the export buttons and choose to export the entire data set as opposed to the current page we have to go back to the server and get the entire data set for the print or the export. There are a few things you have to do to make this happen. In this document, we are only going to focus on the client side of things (the server side is technology dependent, and we provide samples of this independent of this document.)

filterPageSortMode : Needless to say, this needs to be set to "server".
The filterPageSortChange Event: You have to wire up the "filterPageSortChange" event. This is the event that get dispatched when the user user clicks on the sort header on any of the columns, or request a change using either the page navigation buttons or the page navigation drop down in the toolbar, or runs a filter with in any of the columns. This event has 2 properties that are of interest:

event.filter: This object contains all the information that you would potentially need to construct a SQL statement on the backend. Full documentation on this object can be found here:

http://www.flexicious.com/resources/docs29/com/flexicious/grids/filters/Filter.html

event.cause - This can be one of the three values:

public static const FILTER_CHANGE:String = filterChange

public static const PAGE_CHANGE:String = pageChange

public static const SORT_CHANGE:String = sortChange

The printExportDataRequest Event: If you want to support exporting of the entire dataset (all pages), then you also have to wire up the printExportDataRequest event. This is the same as fiterPageSortChange event, in that it has a event.filter object, that in addition to the filter, also contains printExportOptions object.
The selectedKeyField: Also, another key aspect - is the selectedKeyField. You have to wire this up if you want to maintain selection across pages. The selectedKeyField is a property on the object that identifies the object uniquely. Similar to a surrogate key, or a business key, as long as it uniquely identifies the object. When this property is set, the selected keys returns the ID values of the objects that were selected. When a row is selected in the grid, we store the selectedKeyField property of the selected object in this array collection. This allows for multiple pages of data that comes down from the server and not maintained in memory to still keep track of the ids that were selected on other pages. If you use Flexicious in filterPageSortMode=client, this really does not apply to you, but in server mode, each page of data potentially represents a brand new dataprovider. Let's assume you have a Database table of 100,000 records, with the pageSize property set on Flexicious to 50. You load page 1, select a few items, and move on to page 2. The grid exposes a property called selectedItems, which will be lost when the new page of data is loaded. This is why we have the selectedObjects and selectedKeys property, that is, to keep the selection that was loaded in memory on prior pages of data. Now, in most LOB applications, each record can be identified by a surrogate key (or some unique identifier). This surrogate key is then used to uniquely identify different instances of at the same Object. For example, when the page 1 is loaded for the first time, there is a Employee OBJECT with EmployeeId=1. When the user selects this record, navigates to a different page, and switches back to page 1, the Employee with ID 1 is still there, and need to stay selected, but it could be an altogether different INSTANCE of the same record in the database. This is why we have the selectedKeyField property, which would in this case, be "EmployeeID" so we can uniquely identify the selection made by the user across multiple pages.
filterComboBoxDataProvider where filterControl="MultiSelectComboBox" or "ComboBox": These have lookup based filters. Since at any time, we only load the top level filter, we need to query the database for all possible values for this pickers. This is not a problem with filterPageSortMode=client, because we load up the entire dataset and run a distinct on it to figure out the values for the picker. However with server based filterPageSortMode, we need to query the server to get the entire list of possible values to pick from.

One additional caveat: With filterPageSortMode=server grid, the automatic calculations built into the Grid cannot evaluate the footer values. So for example, we have no way of calculating what the "total" of all records is - because, we do not have all the records loaded. So, we have to provide this information separately. This example does not cover this scenario, but we demonstrate this in the

Fully Lazy Loaded example in the demo console.

In the following example we are going to look at the intricacies involved in this use case.

You can find the example running below here : http://www.sqledt.com/DotNetSample

The source code for this example can be downloaded from : http://www.htmltreegrid.com/demo/dotnetsample.zip

Lets review the sample code that demonstrates this:

<div id="gridContainer" fd-grid="" ng-model="gridOptions" style="height: 300px;width: 100%;"

xicreation-complete="onGridCreationComplete"

xienable-export="true"

xienable-copy="true"

xiforce-pagerrow="true"

xipage-size="20"

xienable-multi-column-sort="true"

xienable-filters="true"

xino-data-message=""

xihorizontal-scroll-policy="auto"

xienable-footers="false">

<level xienable-paging="true" xipage-size="25" xienable-filters="true" xiselected-key-field="employeeId"

xienable-footers="false"

xireuse-previous-level-columns="true" xifilter-page-sort-change="filterPageSortChangeHandler" xifilter-page-sort-mode="server">

@*<column xidata-field="employeeId" xiheader-text="id" xiheader-align="middle" xitext-align="left" xifilter-control="TextInput" xifilter-operation="Contains" xisort-case-insensitive="true"></column>*@

<column xidata-field="department.departmentId" xiheader-text="Department" xiheader-align="middle" xifilter-control="MultiSelectComboBox" xifilter-combobox-build-from-grid="false"

xifilter-combobox-width="150" xilabel-function="getDepartment"></column>

@*<column xidata-field="departmentId" xiheader-text="department id" xiheader-align="middle" xifilter-control="TextInput" xifilter-operation="Contains"></column>*@

<column xidata-field="hireDate" xiheader-text="Hired" xifilter-control="DateComboBox"

itemeditor="flexiciousNmsp.DatePicker" xicolumn-width-mode="fitToContent"></column>

<column xiheader-align="middle" xisort-numeric="true" xidata-field="annualSalary" xiheader-text="Annual Salary"

xifilter-control="NumericRangeBox" xilabel-function="flexiciousNmsp.UIUtils.dataGridFormatCurrencyLabelFunction"></column>

</columns>

</level>

</div>

@section css{

href="http://htmltreegrid.com/demo/external/css/adapter/jquery/jquery-ui-1.9.1.custom.min.css"

type="text/css" />

}

@section scripts{

src="http://htmltreegrid.com/demo/external/js/adapters/jquery/jquery-1.8.2.js"></script>

src="http://htmltreegrid.com/demo/external/js/adapters/jquery/jquery-ui-1.9.1.custom.min.js"></script>

src="http://htmltreegrid.com/demo/external/js/adapters/jquery/jquery.maskedinput-1.3.js"></script>

src="http://htmltreegrid.com/demo/external/js/adapters/jquery/jquery.watermarkinput.js"></script>

src="http://htmltreegrid.com/demo/external/js/adapters/jquery/jquery.ui.menu.js"></script>

src="http://htmltreegrid.com/demo/external/js/adapters/jquery/jquery.toaster.js"></script>

We are using the angular integration in this example, so setup a module.

angular.module('app', ['fdGrid'])

.factory('localStorage', function () {

return {};

})

.factory('baseRequest', function ($q, $http) {

return function (api, param, verb) {

verb = verb || 'post';

var defer = $q.defer();

$http[verb]('/api/' + api, param)

.then(function (response, status) { defer.resolve(response, status); },

function (response, status) { defer.reject(response, status); }

);

return defer.promise;

}

})

In this grid, we have a couple of columns - Departments and States. These have lookup based filters. Since at any time, we only load the top level filter, we need to query the database

for all possible values for this pickers.

This is not a problem with filterPageSortMode=client, because we load up the entire dataset and run a distinct on it to figure out the values for the picker.

However with server based filterPageSortMode, we need to query the server to get the entire list of possible values to pick from.

.service('lookUps', function (baseRequest) {

return {

departments: function () {//get all departments from server

return baseRequest('Departments', null, 'get')

.then(function (r) { return r.data; });

states: function () {//get all states from server

return baseRequest('States', null, 'get')

.then(function (r) { return r.data; });

}

})

This is the call for the actual data to render in the grid. This will be called in the following situations:

1) On inital load - we will send an empty filter to get the first page of data (no filters, default sort)

2) On page change : When the user changes the page via the paging buttons or the page dropdown

3) On filter change : When the user changes the value of any of the filter controls within the columns.

4) On sort change : When the user clicks on the column header for any of the columns.

5) Programatically : If we call grid.processFilter()

All cases above internally will dispatch a filterPageSortChange event, below you will see that in scope.filterPageSortChangeHandler we basically call this method.

.factory('query', function (baseRequest) {

return function (f) {

var args = f.filterExpressions;

if (args) {

for (var i in args) {

delete args[i].filterControl; //we dont want to send a UIComponent to the server - not needed (and cannot serialize!)

}

var filter = { //just send the entire filter object for the server to convert to SQL Statement.

filterDescrption: f.filterDescrption,

arguments: f.filterExpressions || [],

pageIndex: f.pageIndex || 0,

pageSize: f.pageSize || 25,

sorts: f.sorts

};

return baseRequest('Employee', filter)

.then(function (r) { return r.data; });

}

})

.controller('myCtrl', function ($scope, query, lookUps) {

$scope.gridOptions = {

dataProvider: [],

delegate: $scope

};

$scope.getDepartment = function (item) {

return item && item.department ? item.department : ""; //labelFunction

}

$scope.getActive = function (item) {

return item.isActive ? 'Yes' : 'No'; //labelFunction

}

The filterPageSortChange Event: You have to wire up the "filterPageSortChange" event. This is the event that get dispatched when the user user clicks on the sort header on any of the columns, or request a change using either the page navigation buttons or the page navigation drop down in the toolbar, or runs a filter with in any of the columns. This event has 2 properties that are of interest:

event.filter: This object contains all the information that you would potentially need to construct a SQL statement on the backend. Full documentation on this object can be found here:

http://www.flexicious.com/resources/docs29/com/flexicious/grids/filters/Filter.html

event.cause - This can be one of the three values:

public static const FILTER_CHANGE:String = filterChange

public static const PAGE_CHANGE:String = pageChange

public static const SORT_CHANGE:String = sortChange

$scope.filterPageSortChangeHandler = function (evt1) {

var grid = evt1.target;

query(evt1.filter).then(function (data) {

grid.setPreservePager(true);

grid.setDataProvider(data.records);

grid.setTotalRecords(data.totalRecords);

grid.validateNow();

});

}

$scope.onGridCreationComplete = function (event) {

var grid = event.target;

// These have lookup based filters. Since at any time, we only load the top level filter, we need to query the database for all possible values for this pickers.

// This is not a problem with filterPageSortMode=client, because we load up the entire dataset and run a distinct on it to figure out the values for the picker.

// However with server based filterPageSortMode, we need to query the server to get the entire list of possible values to pick from.

//filterComboBoxDataProvider where filterControl="MultiSelectComboBox" or "ComboBox":

lookUps.departments().then(function (data) {

var filteredArray = flexiciousNmsp.UIUtils.filterArray(data, grid.createFilter(), grid, grid.getColumnLevel(), false);

var stateCol = grid.getColumnByDataField("department.departmentId");

stateCol.filterComboBoxLabelField = 'departmentName';

stateCol.filterComboBoxDataField = 'departmentId';

stateCol.filterComboBoxDataProvider = filteredArray;

grid.rebuildFilter();

});

////filterComboBoxDataProvider where filterControl="MultiSelectComboBox" or "ComboBox":

lookUps.states().then(function (data) {

var data2 = [];

data.forEach(function (a) {

var t = { label: a, data: a }

data2.push(t);

});

var filteredArray = flexiciousNmsp.UIUtils.filterArray(data2, grid.createFilter(), grid, grid.getColumnLevel(), false);

var stateCol = grid.getColumnByDataField("stateCode");

stateCol.filterComboBoxDataProvider = filteredArray;

grid.rebuildFilter();

});

//Initial data load with a blank filter.

query({ pageSize: grid.getPageSize() }).then(function (data) {

grid.setPreservePager(true);

grid.setDataProvider(data.records);

grid.setTotalRecords(data.totalRecords);

grid.validateNow();

});

};

})

</script>

}