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

<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

 
.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

 
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

 
 public class ItemsModel
 {
     public long itemsCount { get; set; }
     public List<item> items { get; set; }
 }



• SQL Query to get filtered data from DB

 
.
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

 
.
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).