KoGrid with server side paging, sorting and filtering

Here is the code for implementing server side paging, sorting and filtering in koGrid (a.k.a. knockout grid). To see how to implement lazy loading in koGrid follow the link.


  • HTML for kogrid and cell templates of grid
    • ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    <div class="col-lg-12 col-md-12 col-xs-12" style="padding-right: 0px;">
        <div class="panel panel-default">
            <div >
                <div class="row">
                    <div class="col-lg-3 col-md-3 col-xs-3 pull-right">
                        <div id="filtertext" class="input-group" style="margin-bottom:10px">
                            <span class="input-group-btn">
                                <button type="button" class="btn btn-default" data-bind="click: applyFilter">
                                    <span class="glyphicon glyphicon-filter"></span>
                                </button>
                            </span>
                            <input  type="text" class="form-control" placeholder="Column:value" data-bind="textinput: filterText">
                        </div>
                    </div>
                </div>
     
                <div class="row">
                    <div class="col-lg-12 col-md-12 col-xs-12">
                        <div class="ko-gridStyle" id="grid" data-bind="koGrid: gridOptions" ></div>
                    </div>
                </div>
            </div>
        </div>
    </div>
     
    <script type="text/template" id="editDeleteCellTemplate">
        <div style="padding-top: 2px;">
            <button class="btn btn-sm btn-info editbtn" data-bind="click: function() { $parent.$userViewModel.edit($parent.entity); }">
                <span class="glyphicon glyphicon-edit">Edit</span>
            </button>
        </div>
    </script>
     
    <script type="text/template" id="CellTemplate">
        <div class="kgCellText"  data-bind="text: $parent.entity[$data.field], attr: { title: $parent.entity[$data.field] }"></div>
    </script>

  • CSS style for grid
    • ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    .ko-gridStyle {
      border: 1px solid rgb(212, 212, 212);
      width: 100%;
      min-width: 200px;
      height: 650px;
      font-size: 0.8em;
      padding-left: 6px;
      padding-right: 4px;
    }

  • Javascript: View model to bind to grid and ajax call to get data from server
    • ?
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    113
    114
    115
    116
    ListVM = function () {
        var self = this;
        this.myData = ko.observableArray([]);
     
        this.filterOptions = {
            filterText: ko.observable(""),
            useExternalFilter: true,
            applyFilter: function () {
                self.getPagedDataAsync();
            }
        };
     
        ko.applyBindings(self.filterOptions, $('#filtertext')[0]);
     
        this.sortInfo = ko.observable({ column: { 'field': '' }, direction: '' });
     
        this.pagingOptions = {
            pageSizes: ko.observableArray([5, 10, 15]),
            pageSize: ko.observable(15),
            totalServerItems: ko.observable(0),
            currentPage: ko.observable(1)
        };
     
        this.setPagingData = function (data, page, pageSize) {
            //Update items and total count in the grid
            self.myData(data.items);
            self.pagingOptions.totalServerItems(data.itemsCount);
        };
     
        this.getPagedDataAsync = function () {
            //Get items from the server
            GetItems(self, function (data, viewModel) {
                viewModel.setPagingData(data, viewModel.pagingOptions.currentPage(), viewModel.pagingOptions.pageSize());
            });
        };
     
        self.edit = function (item) {
            //Your edit code
        };
     
        self.sortInfo.subscribe(function (data) {
            self.getPagedDataAsync();
        });
     
        self.pagingOptions.pageSize.subscribe(function (data) {
            self.getPagedDataAsync();
        });
        
        self.pagingOptions.currentPage.subscribe(function (data) {
            self.getPagedDataAsync();
        });
     
        self.getPagedDataAsync(self.pagingOptions.pageSize(), self.pagingOptions.currentPage());
        this.gridOptions = {
            data: self.myData,
            columnDefs: [
                { field: 'NAME', displayName: 'Name', resizable: false, cellTemplate: $('#CellTemplate').html() },         
                { field: 'DESCRIPTION', displayName: 'Description', resizable: false, cellTemplate: $('#CellTemplate').html() },     
                { field: 'ID', displayName: ' ', cellTemplate: $('#editDeleteCellTemplate').html(), sortable: false, resizable: false }         
            ],
            enablePaging: true,
            pagingOptions: self.pagingOptions,
            filterOptions: self.filterOptions,
            sortInfo: self.sortInfo,
            displaySelectionCheckbox: false,
            footerVisible: true,
            showColumnMenu: false,
            showFilter: false,
            headerRowHeight: 40,
            rowHeight: 35,
            canSelectRows: false,
            useExternalSorting: true //
        };
    };
     
    GetItems = function (viewModel, callback) {
        //send request to server to get filtered data
        var sort = null;
     
        if (!(viewModel.sortInfo == null)) {
            sort = viewModel.sortInfo().column.field + ' ' + viewModel.sortInfo().direction;
            if (sort == ' ') {
                sort = null;
            }
        }
     
        var filter;
        if (!(viewModel.filterOptions == null)) {
            filter = viewModel.filterOptions.filterText();
            if (filter.trim() === '') {
                filter = null;
            }
        }
     
        $.ajax({
            url: WEBSERVICEURL,
            type: 'get',
            data: {
                'page': viewModel.pagingOptions.currentPage(),
                'pageSize': viewModel.pagingOptions.pageSize(),
                'sort': sort,
                'filter': filter
            },
            contentType: 'application/json; charset=utf-8',
     
            success: function (data) {
                if (callback != undefined) callback(data, viewModel);
            }
        });
    }
     
    $(document).ready(function () {
        var vm = new ListVM();
     
        ko.applyBindings(vm, $('#grid')[0]);
    });

  • Model whose object is returned as response of ajax request sent for getting filtered data
    • ?
    1
    2
    3
    4
    5
    public class ItemsModel
    {
        public long itemsCount { get; set; }
        public List<item> items { get; set; }
    }

  • SQL Query to get filtered data from DB
    • ?
    1
    2
    .
    select ID, NAME, DESCRIPTION from ITEMS WHERE ISNULL({@FilterValue},'' '') = '' '' OR  (( CAST(CHARINDEX(LTRIM(RTRIM({@FilterValue})), NAME) AS int)) > 0 OR ( CAST(CHARINDEX(LTRIM(RTRIM({@FilterValue})), DESCRIPTION ) AS int)) > 0)  ORDER BY {@OrderByCond} OFFSET {@Offset} ROWS FETCH NEXT {@RowCount} ROWS ONLY

  • SQL Query to get total count of filtered records
    • ?
    1
    2
    .
    select Count(ID) from ITEMS WHERE ISNULL({@FilterValue},'' '') = '' '' OR  (( CAST(CHARINDEX(LTRIM(RTRIM({@FilterValue})), NAME) AS int)) > 0 OR ( CAST(CHARINDEX(LTRIM(RTRIM({@FilterValue})), DESCRIPTION ) AS int)) > 0)
Note: In the above SQL queries replace the text enclosed in curly braces ({}) with appropriate values sent in ajax request as data (the ajax request is sent in "GetItems" method in javascript code).



Labels: , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)