We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date

SfDataPager with database-side filtering and sorting

I would like to create a paged and editable grid with database-server-side filtering and sorting.

The database table I'm presenting holds millions of records which can't all be sent to the client device. Yet I would like the end user to be able to quickly filter and sort the full dataset.
The ideal solution would be a table sortable by column, with a filter row, where the filter row and sorting applies to the full database table. Not just the locally fetched records.

Can this be achieved with the SfDataPager and SfDataGrid? 
Are there any samples available which demonstrate the client-side functionality?

6 Replies

JG Jai Ganesh S Syncfusion Team May 9, 2016 12:47 PM UTC

Hi Jasper, 
You can achieve your requirement for sort and filter the database records in DataPaging. We have a Datapaging and OndemandPaging in SfDataGrid.  In DataPaging, you can load the entire source and perform operations but in OndemadPaging, you can load the records at on demand for each page. 
You can sort the complete collection with ‘OnDemandPaging’ by using SfDataGrid.SortColumnChangingevent. In this event, you can sort the complete underlying collection instead of sorting current page alone by resetting the caches. 
Could you please let us know, in which database you are using in your application? This would be more helpful for us to create the simple sample to achieve your requirements. 
Jai Ganesh S 

JA Jasper May 12, 2016 10:21 AM UTC

Hi Jai,

I implemented the database sorting with the help documentation you provided.

However, filter rows and column filtering don't seem to work":
  • UI filtering is not supported when using on-demand paging by setting UseOnDemandPaging to true.
    Source: http://help.syncfusion.com/wpf/sfdatagrid/filtering#ui-filtering

  • FilterRow is not supported in SfDataGrid.
    Source: http://help.syncfusion.com/wpf/sfdatagrid/paging
Can you tell me if it's possible to achieve a filter row or column filtering with ondemand paging?

JA Jasper May 12, 2016 10:32 AM UTC

If I enable the filterrow in the grid it is displayed, but as soon as you choose a filter mode such as "equals", the application will crash with a NullReferenceException

If i enable gridfiltering, no filter options appear in the grid header.

JG Jai Ganesh S Syncfusion Team May 13, 2016 11:59 AM UTC

Hi Jasper, 
We regret to inform you that, UI filtering and Filter row is not supported in On-demand paging. This is the limitation to use Undemand paging. However, you can apply the filter at runtime in On-demand paging and please find the following UG for more details, 
UG Link: 
Jai Ganesh S 

JA Jasper May 14, 2016 09:27 AM UTC

Yea I figured as much.

I was looking for a solution to use the filter row in a mode which only shows the filter row, but doesnt actually actively filter anything inside the control dataset.

With for example an OnFilterChanged event I could then translate the filters applied filters to the database backend.

This however doesn't seem to be possible because setting the filterrow triggers a Null exception.

JG Jai Ganesh S Syncfusion Team May 17, 2016 02:48 PM UTC

Hi Jasper, 
In On Demand paging , the records are populated dynamically and we are not getting all the records. So we cannot perform the UI filtering in this case. Hence we suggested to use filtering the records at runtime. Could you please find the sample for this from the UG link as we already provided. 
Jai Ganesh S 

Live Chat Icon For mobile
Up arrow icon