SfDataPager with database-side filtering and sorting

[Jasper_E]

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?

[Jai Ganesh S]

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. 

 

UG Link: http://help.syncfusion.com/wpf/sfdatagrid/paging?cs-save-lang=1&cs-lang=csharp#sorting-complete-collection 

 

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. 

 

UG Link: http://help.syncfusion.com/wpf/sfdatagrid/paging 

 

Regards, 

Jai Ganesh S 

[Jasper]

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?

[Jasper]

PS. 

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.

[Jai Ganesh S]

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: 

http://help.syncfusion.com/wpf/sfdatagrid/paging#changing-pagecount-at-run-time-while-filtering 

 

Regards, 

Jai Ganesh S 

 

[Jasper]

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.

[Jai Ganesh S]

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. 

 

Regards, 

Jai Ganesh S