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. (Last updated on : Nov 16th 2018).
Unfortunately, activation email could not send to your email. Please try again.
Syncfusion Feedback

Server-side filtering, searching and sorting

Thread ID:

Created:

Updated:

Platform:

Replies:

130337 May 8,2017 06:55 AM UTC May 10,2017 12:33 PM UTC ASP.NET MVC 3
loading
Tags: Grid
Khurram Ali
Asked On May 8, 2017 06:55 AM UTC

I have a grid that has to show thousands of records from database, so i have implemented server side paging here.


@(Html.EJ().Grid<object>("Grid")
            .Datasource(ds => ds.URL(@Url.Action("GetJobOrders"))
                        .Adaptor(AdaptorType.UrlAdaptor))
            .Columns(col =>
            {
                col.Field("JobOrderId").HeaderText("Job Order #").Add();
                col.Field("JobOrderTypeName").HeaderText("Type").Add();
                col.Field("AssetType").HeaderText("Asset Type").Add();
                col.Field("EmployeeName").HeaderText("Employee").Add();
                col.Field("HeadOfProblemName").HeaderText("Head of Problem").Add();
                col.Field("DateTime").HeaderText("Date").Format("{0:dd/MMM/yyyy hh:mm tt}").Add();
                col.Field("Status").Add();
                col.Field("NextStatus").HeaderText("Action").Template("<a rel='nofollow' href='/JobOrders/{{>UrlAction}}/{{>JobOrderId}}')>{{>NextStatus}}</a>").Add();
                col.HeaderText("Detail")
                    .Template("<a rel='nofollow' href='/JobOrders/Details/{{>JobOrderId}}')><span class='glyphicon glyphicon-list'></span></a>")
                    .AllowFiltering(false)
                    .TextAlign(TextAlign.Center)
                    .AllowSorting(false)
                    .Add();
            })
            .PageSettings(x => x.PageSize(30))
            .FilterSettings(filter => { filter.FilterType(FilterType.Menu); })
            .EnableToolbarItems(true)
            .ToolbarSettings(x => x.ShowToolbar().ToolbarItems(j =>
                {
                    j.AddTool(ToolBarItems.ExcelExport);
                }))
            .AllowTextWrap()
            .TextWrapSettings(x => x.WrapMode(WrapMode.Both))
            .AllowResizeToFit()
            .AllowFiltering()
            .AllowScrolling()
            .IsResponsive()
            .AllowPaging()
            .AllowSorting()              


I have used urlAdaptor, and have implemented server side paging using Skip and Take parameters.


    public ActionResult GetJobOrders(DataManager dm)
       {
           
            int jobOrderCount = 0;

            if (Session["jobOrderCount"] != null)
            {
                if(Session["jobOrderCount"] is int)
                {
                    jobOrderCount = (int) Session["jobOrderCount"] ;
                }
            }
            else
            {
                jobOrderCount = db.JobOrders.Count();
                Session["jobOrderCount"] = jobOrderCount;
            }

            List<JobOrder> jobOrders;

           
                jobOrders = db.JobOrders
                                .Include(j => j.Employee).Include(j => j.HeadOfProblem).Include(j =>           j.ItemBuild).Include(j => j.JobOrderType)
                                .Include(j => j.SubDepartment).Include(j => j.JobOrderDetail)
                                .OrderByDescending(x => x.JobOrderId).Skip(dm.Skip).Take(dm.Take).ToList();


           List<JobOrderViewModel> lstJobOrders = new List<JobOrderViewModel>();
            JobOrderViewModel jobOrderViewModel;

            foreach (JobOrder item in jobOrders)
            {

                jobOrderViewModel = new JobOrderViewModel();
                jobOrderViewModel.JobOrderId = item.JobOrderId;
                jobOrderViewModel.JobOrderTypeName = item.JobOrderType.JobOrderTypeName;
                if (item.ItemBuild != null)
                {
                    jobOrderViewModel.AssetType = item.ItemBuild.ItemName;
                }
                if (item.Employee != null)
                {
                    jobOrderViewModel.EmployeeName = item.Employee.EmployeeName;
                }

                jobOrderViewModel.HeadOfProblemName = item.HeadOfProblem.HeadOfProblemName;
                jobOrderViewModel.DateTime = item.DateTime;
                JobOrderStatus(item, jobOrderViewModel);
                lstJobOrders.Add(jobOrderViewModel);
            }

            return Json(new { result = lstJobOrders, count = count }, JsonRequestBehavior.AllowGet);
 
}

Now, the problem is how do i implement server side filtering, search and sorting. I am unable to even figure out how to provide unique values to filter for a column from server side.


Mani Sankar Durai [Syncfusion]
Replied On May 9, 2017 12:21 PM UTC

Hi Khurram, 


Thanks for contacting Syncfusion support. 



We have analyzed your query and we can perform server side filtering, sorting, grouping etc.., using dataOperations in grid. In the following example we have used URLAdaptor in grid and explained the way how to handle grid action like filtering, sorting etc.. in server side. 


Refer the code example. 
@(Html.EJ().Grid<object>("Grid") 
                     .Datasource(ds => ds.URL("/Home/DataSource").Adaptor("UrlAdaptor")) 
                     .AllowPaging() 
                     .AllowFiltering() 
                     .FilterSettings(filter=>filter.FilterType(FilterType.Excel)) 
                     .AllowSorting() 
                     .Columns(col => 
        { 
            col.Field("OrderID").IsPrimaryKey(true).HeaderText("Order ID").TextAlign(TextAlign.Right).Add(); 
            col.Field("EmployeeID").HeaderText("Employee ID").TextAlign(TextAlign.Right).Add(); 
            col.Field("Freight").HeaderText("Freight").EditType(EditingType.Numeric).TextAlign(TextAlign.Right).Add(); 
        }) 
) 

The server side data operations are takes place as inbuilt support by passing the URL from client side to server side. In the server side we can get it from the DataManager Class which contains the object for every grid actions like filtering, sorting, grouping, skip and take (i.e paging) etc..,  

Refer the code example. 
[HomeController.cs] 
public ActionResult DataSource(DataManager dm) //Using DataManager class which contains Sorting, filtering as a parameter.   
        { 
…. 

Refer the screenshot below of DataManager class. 
 

We have also prepared a sample that can be downloaded from the below link. 

Please refer the below code example which contains server side operations. Using DataOperations class each actions in grid takes place. 

using Syncfusion.JavaScript.DataSources; 
using Syncfusion.Linq; 
using Syncfusion.JavaScript.Models; 
 
public ActionResult DataSource(DataManager dm) 
        { 
            IEnumerable data = OrderRepository.GetAllRecords().ToList(); 
            DataOperations operation = new DataOperations(); 
            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting 
            { 
                data = operation.PerformSorting(data, dm.Sorted); 
            } 
            if (dm.Where != null && dm.Where.Count > 0) //Filtering 
            { 
                data = operation.PerformWhereFilter(data, dm.Where, dm.Where[0].Operator); 
            } 
            int count = data.Cast<EditableOrder>().Count(); 
            if (dm.Skip != 0) 
            { 
                data = operation.PerformSkip(data, dm.Skip); 
            } 
            if (dm.Take != 0) 
            { 
                data = operation.PerformTake(data, dm.Take); 
            } 
            return Json(new { result = data, count = count }); 
        } 
        public class DataResult 
        { 
            public IEnumerable result { get; set; } 
            public int count { get; set; } 
        } 
        public ActionResult Update(EditableOrder value) 
        { 
          //perform update action 
} 
        public ActionResult Insert(EditableOrder value) 
        { 
             //perform insert action 
          } 
        public ActionResult Remove(int key) 
        { 
            //perform delete action 
        } 


Refer the screenshot that contains the Inbuilt support of server side data operation in grid. 

 


Refer the Kb Link:  

Refer the documentation link. 


Please let us know if you need further assistance. 



Regards, 
Manisankar Durai. 


Khurram Ali
Replied On May 9, 2017 01:43 PM UTC

Well, the problem is I do not want to get all records from database like you are doing here,

IEnumerable Data = OrderRepository.GetAllRecords().ToList();

Rather, i am just fetching the records i need to show in the current page of grid, like this.

  jobOrders = db.JobOrders.Skip(dm.Skip).Take(dm.Take).ToList();

Now the problem is, when filter of the grid is opened, it hits the same method of DataSource again and it only take values for filter from this set of records, which i am getting for the page. While to make it completely server side filtering, i want to provide unique values from database to the filter of grid, and when any filter is selected i can then use DataManager dm as you mentioned to get the filtered response from database with the Where clause.

But i am unable to provide unique values to filter for different columns. If I can, i will be able to manipulate the query to database using DataManager and get desired records. The only way looks possible right now is to get all hundred thousand records for the filter call, so the grid can select unique values from it.

Please help, i have been stuck into this for three days.

Mani Sankar Durai [Syncfusion]
Replied On May 10, 2017 12:33 PM UTC

Hi Khurram, 

We have analyzed your query and we suspect that you would like to send the unique values for filtering the data in grid from server side. 

We have also prepared a sample that can be downloaded from the below link 
Refer the code example. 
@(Html.EJ().Grid<object>("Grid") 
                     .Datasource(ds => ds.URL("/Home/DataSource").Adaptor("UrlAdaptor")) 
                     ... 
                     .Columns(col => 
        { 
            ... 
               .ClientSideEvents(eve => eve.ActionBegin("onActionBegin")) 
) 
<script> 
 
        function onActionBegin(args) { 
            if (args.requestType == "filterchoicerequest") { 
                //selects only the filtering column 
                //which prevents the serialization errror. 
                args.query.select(args.filterModel.fName); 
            } 
        } 
 
</script> 
public ActionResult DataSource(DataManager dm) 
        { 
            IEnumerable Data = new NorthwindDataContext().OrdersViews.ToList(); 
            DataOperations operation = new DataOperations(); 
            DataResult result = new DataResult(); 
            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting 
            { 
                Data = operation.PerformSorting(Data, dm.Sorted); 
            } 
            if (dm.Where != null && dm.Where.Count > 0)  
            { 
                Data = operation.PerformWhereFilter(Data, dm.Where, dm.Where[0].Operator); 
            } 
            result.count = Data.AsQueryable().Count(); 
            if (dm.Select != null) 
            { 
                 Data = operation.PerformSelect(Data, dm.Select); 
                 Data = Data.Cast<dynamic>().Distinct().AsEnumerable(); 
                
            } 
            if (dm.Skip != 0) 
            { 
                Data = operation.PerformSkip(Data, dm.Skip); 
            } 
            if (dm.Take != 0) 
            { 
                Data = operation.PerformTake(Data, dm.Take); 
            } 
            result.result = Data; 
            return Json(result, JsonRequestBehavior.AllowGet); 
        } 

If you need to pass the unique values current filtering column value alone we suggest you to follow the above code example. 

From the above code example we have passed the current filtering column to server side in actionBegin event in grid. From there using Select operation we can get the corresponding column with unique values using Distinct method. 

Refer the screenshot below. 
 

 


Refer the documentation link. 

Please let us know if you need further assistance. 

Regards, 
Manisankar Durai. 


CONFIRMATION

This post will be permanently deleted. Are you sure you want to continue?

Sorry, An error occured while processing your request. Please try again later.

Warning Icon You are using an outdated version of Internet Explorer that may not display all features of this and other websites. Upgrade to Internet Explorer 8 or newer for a better experience.Close Icon

;