Filtering/Sorting/Searching not working if grid is configured with Datamanager and UrlAdaptor

[Mithun Revankar]

hi,
i have successfully implemented skip and take method using data manager and url adaptors in grid. but when i want to search or filter record it only works on visible records,  not all records available in the database. i have almost 50000 records so i am only taking 19 records to show.
following is my code

////////////////////// grid code

  @(Html.EJ().Grid<CRM.ViewModel.CustomerVM>("FlatGrid")
         //.Datasource((IEnumerable<object>)ViewBag.CustomerList)
         .Datasource(ds => ds.URL("../Customer/DataSource").Adaptor("UrlAdaptor").Offline(true))
         .AllowSorting()
         .AllowPaging().PageSettings(p => { p.PageSize(19); })
         .ShowColumnChooser()
         .AllowFiltering()
         .FilterSettings(filter => { filter.FilterType(FilterType.Menu); })
         .AllowSearching()
         .AllowSelection()
        

                 .ToolbarSettings(toolBar => toolBar.ShowToolbar(true).ToolbarItems(items =>
                        {
                            items.AddTool(ToolBarItems.Add);
                            items.AddTool(ToolBarItems.ExcelExport);
                            items.AddTool(ToolBarItems.WordExport);
                            items.AddTool(ToolBarItems.PdfExport);
                            items.AddTool(ToolBarItems.PrintGrid);
                        }))
        .Columns(col =>
        {
            col.Field("Id").Visible(false).HeaderText("Id").IsPrimaryKey(true).TextAlign(TextAlign.Right).Width(50).Add();
            col.HeaderText("Rating").Template(true).TemplateID("#RatingTemplate").TextAlign(TextAlign.Center).Width(40).Add();
            col.Field("Name").HeaderText("Name").TextAlign(TextAlign.Left).Width(150).Add();
            col.Field("BusinessTypeName").HeaderText("Business").TextAlign(TextAlign.Left).Width(80).Add();
            col.Field("ChannelName").HeaderText("Channel").TextAlign(TextAlign.Left).Width(80).Add();
            col.Field("Ownername").HeaderText("Owner").TextAlign(TextAlign.Left).Width(80).Add();
            col.Field("Ratings.RatePoint").Visible(false).HeaderText("RatePoint").TextAlign(TextAlign.Left).Width(80).Add();
        })
        .ClientSideEvents(eve => eve.ActionComplete("actionComplete"))
        .ClientSideEvents(eve => eve.DataBound("DataBound"))
        .ClientSideEvents(eve => { eve.ActionBegin("begin");}))



 function DataBound(args) {
        Rating1();
    }
  
    function actionComplete(args) {
        if (args.requestType == "paging") {
            Rating1();//Again call the rating function while pagination.
        }
        else if (args.requestType == "sorting") {
            Rating1();
        }
        else if (args.requestType == "filtering") {
            Rating1();
        }

    }
    function begin(args) {
      
        this.model.query.addParams("Type", '@ViewBag.Type');
        this.model.query.addParams("IsActive", '@ViewBag.IsActive');

             
    }

//////////////////// Controller Code

        public ActionResult DataSource(Syncfusion.JavaScript.DataManager dm, string Type, bool IsActive = true)
        {

            CustomerService customerService = new CustomerService();
           

            bool IsCustomer = false;
            if (Type == "Lead")
            {
                IsCustomer = false;
            }
            else
            {
                IsCustomer = true;
            }
            IEnumerable Data = customerService.GetAll(IsCustomer, IsActive, (int)dm.Skip, 19);

            
            //IEnumerable Data = customerService.GetAll(false, true);
            int count = customerService.GetCount(IsCustomer, IsActive);
            Syncfusion.JavaScript.DataSources.DataOperations operation = new Syncfusion.JavaScript.DataSources.DataOperations();
          
          

            if (dm.Where != null && dm.Where.Count > 0) //Filtering
            {
                //IEnumerable Data1 = customerService.GetAll(IsCustomer, IsActive, (int)dm.Skip, 19);

                //string Name = "";
                //string Business = "";
                //string Channel = "";
                //string Owner = "";


                //for (int i = 0; i <= dm.Where.Count - 1; i++)
                //{
                //    if (dm.Where[i].Field == "Name")
                //    {
                //        Name = dm.Where[i].value.ToString();
                //    }
                //    else if (dm.Where[i].Field == "BusinessTypeName")
                //    {
                //        Business = dm.Where[i].value.ToString();
                //    }
                //    else if (dm.Where[i].Field == "ChannelName")
                //    {
                //        Channel = dm.Where[i].value.ToString();
                //    }
                //    else if (dm.Where[i].Field == "Ownername")
                //    {
                //        Owner = dm.Where[i].value.ToString();
                //    }

                //}
                //// IEnumerable Data1 = customerService.GetAllSearchContent(IsCustomer, Name, Business, Channel, Owner, IsActive);

                Data = customerService.GetAll(IsCustomer, IsActive, 0, 2000);
                //Data = operation.PerformWhereFilter(Data, dm.Where, dm.Where[0].Operator);
                Data = operation.Execute(Data, dm);
              

            }
          

            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting
            {
                //Data = customerService.GetAll(IsCustomer, IsActive, 0, 2000);
                Data = operation.PerformSorting(Data, dm.Sorted);
            }

            //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 }, JsonRequestBehavior.AllowGet);
        }

[Gowthami V]

Hi Mithun,

Thanks for contacting Syncfusion products.

We have analyzed your code example and we have found that you have set “Offline” property of DataSource as “true”.

While set Offline property as true the post back will be done at initial rendering alone. After rendering the grid if we perform any actions then it will perform at client side itself instead of doing post back.

As per your code at initially you have bound the grid with “19” records and set offline as true. So that only the filtering and searching actions are performed with the data initially bound to the grid at client side.

So we suggest you to set “Offline” property as “false” to perform the filtering and sorting action in server side.

@(Html.EJ().Grid<crm.viewmodel.customervm>

    ("FlatGrid")

    //.Datasource((IEnumerable<object>

        )ViewBag.CustomerList)

        .Datasource(ds => ds.URL("../Customer/DataSource").Adaptor("UrlAdaptor").Offline(false))
. . . .
. . . .
    )

While using above code example each and every grid actions performed at server side with the data passed to the corresponding server side method.

Regards,

Gowthami V.

[Mithun Revankar]

Thanks for your prompt reply. as you have suggested if i set offline to false, i have following queries
1) i am unable to pass custom parameters to the method
2) Initially it will take to much time to upload data ( 50000 records).

please let me know. Thanks

[Gowthami V]

Hi Mithun,

Query 1: I am unable to pass custom parameters to the method

We have analyzed your issue and we are unable to reproduce the issue.

Refer to the below screenshot and we can get the custom parameters at initial rendering, filtering, paging etc..




Please refer to the below mentioned sample, we have passed the additional parameter via addParams property in ActionBegin event as follows,

@(Html.EJ().Grid<object>("Grid")
                .Datasource(ds => ds.URL("DataSource").Adaptor(AdaptorType.UrlAdaptor).Offline(false))
. . . .

}).ClientSideEvents(eve => { eve.ActionBegin("begin"); })

        )

<script type="text/javascript">

    function begin(args) {

        this.model.query.addParams("Type", "type");

        this.model.query.addParams("IsActive", true);

    }

</script>

Query 2: Initially it will take to much time to upload data ( 50000 records).

While set Offline as true the current page records will be bound to the grid using on demand concept. When Offline as false then the total records will be bound to the grid.
While binding the data using PerformSkip and PerformTake method of the data operations class the current page data only will retrieve. So it will not take more time for initial render.

So kindly please check that you have get the record using skip and take value to avoid to return all the 50000 records.

In your code example we have found that you are using user defined method GetAll for getting data based on the passed skip and take value.

Can you please share the code that you have used for getting data in GetAll method? Which will help to analyze your issue “taking more time to retrieve data” and will provide you the response as early as possible.

Refer to the below sample for more clarification,

http://www.syncfusion.com/downloads/support/directtrac/155269/ze/Sample_(2)533519976

Regards,

Gowthami V.

[Nirbhay]

Hi ,

I am using Angular 2 and  also have the same issue. When we use 'allowFiltering' option or 'search toolbar' option the grid is filtering from the already populated data into it, but not giving any option to filter the same from our database by using Web API. Can you please guide me what to do for searching the data from database using webapi?

Actually I was expecting to create a method that can override the existing event. 

[Farveen Sulthana Thameeztheen Basha]

Hi Nirbhay, 

Thanks for contacting Syncfusion support. 

We have checked your query and we have already discussed the KB about how to handle search operations using Web API services. Please refer to the KB link:- 

https://www.syncfusion.com/kb/8251/search-operation-using-webapi-service 

 

Please refer to the code example:- 

App.component.html 

<ej-grid id="Grid" #grid [dataSource]="gridData" [allowPaging]="true" [allowSearching]="true" [toolbarSettings]="toolbarsettings"> 

      <e-columns> 

        <e-column field="OrderID" headertext="Order ID" [isPrimaryKey]='true' width="90"></e-column> 

        <e-column field="CustomerID" headertext="Customer ID" width="80"></e-column> 

           .    .    . 

    </e-columns> 

</ej-grid> 

App.component.ts 

export class GridComponent { 

         constructor() { 

           this.gridData = ej.DataManager({ 

            url: "http://localhost:49339/api/Orders", 

            adaptor: new ej.WebApiAdaptor(), 

            crossDomain: true 

        }); 

   

        this.toolbarsettings = { showToolbar: true, toolbarItems: ["search"] } 

         

    } 

  } 

 

Serverside:- 

 

  public class ValuesController : ApiController 

     { 

        // GET api/<controller> 

        public PageResult<Order> Get(ODataQueryOptions opts) 

          { 

             

            if (opts.OrderBy != null) 

                results = opts.OrderBy.ApplyTo(results); 

            if (opts.Filter != null) 

            { 

                if (opts.Filter.RawValue.Contains("substring")) 

                { 

                    // Seperating the value from the arguments of opts using spilt 

                    string key = opts.Filter.RawValue.Split(new string[] { "'" }, StringSplitOptions.None)[1]; 

                    results = results.Where(fil => fil.CustomerID.Contains(key) || fil.EmployeeID.ToString().Contains(key) || fil.Freight.ToString().Contains(key) || fil.OrderID.ToString().Contains(key) || fil.ShipName.Contains(key) || fil.OrderDate.ToString().Contains(key) || fil.RequiredDate.ToString().Contains(key)); 

                } 

                else 

                    results = opts.Filter.ApplyTo(results, new ODataQuerySettings()).Cast<Order>(); 

            } 

       } 

After following the steps in the KB, still facing any issue please share us the following details to find the cause of the issue. 

1. Share your Grid code example(both in server and  client side). 

2. You have mentioned that you need to create a method that can override the existing event? Do you want to override the method used  for searching operation. Exact scenario you need to achieve regarding the query. 

3. If you are facing any issue share screenshot/Video Demo. 

The provided information will be helpful to find to provide you response as early as possible. 

Regards, 

Farveen sulthana T