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
  1. Home
  2. Forum
  3. ASP.NET Web Forms
  4. Grid FilterType Excel and Virtual Scrolling

Grid FilterType Excel and Virtual Scrolling

Hi

Question 1.
we have an ASP Grid with these settings

 <ej:Grid ID="MasterGrid" runat="server" AllowScrolling="true" AllowFiltering="true" AllowSelection="true" AllowSorting="true" AllowTextWrap="true" >
        <DataManager URL="../Vehicles/GetMasterData" Adaptor="WebApiAdaptor" />
         <ScrollSettings Height="600" AllowVirtualScrolling="true" VirtualScrollMode="Continuous" EnableVirtualization="true" /> 
        <FilterSettings FilterType="Excel"></FilterSettings>
    </ej:Grid>  

When i try to filter by any column and any (checkbox) value nothing happens. No filtering takes place.
If I leave out scrolling and virtualization and load everything, the result is the same.

Question 2.

As far as i can see, the virtual scrolling works when i scroll down the grid.
My datamanager calls an SQL stored procedure to get the data for the grid
When looking in the SQL Server Profiler (when scrolling through the grid) i see that the stored procedure is called every time the scroll reaches certain amount of page.
Comparing to the LightSwitch app we also have, the grid scrolling works in the sql profiler like Select Top 45 from Stored P. for every scroll so i was just wondering shouldn't the same be with your grid scrolling?

Question 3.

We use bundle for styles and scripts, and the EnableOptimisation are True
This is the bundle
 bundles.Add(New StyleBundle("~/bundles/StylesSyncfusion").Include(
                            "~/_COMMON/Styles/ej/flat-saffron/ej.web.all.min.css", New CssRewriteUrlTransform))

Every image from the common-images is shown on the page for every control except the handle on the scrollbar (on grid or dropdownlist) so you cannot click on anything to hold and drag through the scrollbar.

The version is 14.2460.0.26

Thank you for your help.




10 Replies
SP Sureshkumar P Syncfusion Team August 25, 2016 01:44 PM UTC

Hi Semper, 
 
Thank you for contacting Syncfusion support. 
 
Query 1: If I leave out scrolling and virtualization and load everything, the result is the same. 
 
We have checked your provided code example and we found that you have used WebApiAdaptor. While using WebApiAdaptor, we need to handle all the grid actions (sorting, filtering, editing, paging and searching) in server side. 
 
We can get all the grid action queries in the server side, based on that value we can perform the corresponding actions in server side and return the processed data. Please refer to the below code example and sample. 
 
public PageResult<Order> Get(ODataQueryOptions opts)  
        {  
            List<Order> emp = db.Orders.ToList();  
  
            var count = 0;  
            var results = emp.AsQueryable();  
            if (opts.InlineCount != null)  
                count = results.Count();  
  
            if (opts.Filter != null)  
            {  
                results = opts.Filter.ApplyTo(results, newODataQuerySettings()).Cast<Order>();  
  
                count = results.Count();  
                return new PageResult<Order>(results, null, count);  
            }  
            else  
                return new PageResult<Order>(opts.ApplyTo(emp.AsQueryable()) asIEnumerable<Order>, null, count);  
        }  
 
 
Query 2: Comparing to the LightSwitch app we also have, the grid scrolling works in the sql profiler like Select Top 45 from Stored P. for every scroll so i was just wondering shouldn't the same be with your grid scrolling? 
 
We have created a sample by enabling Virtualization and set VirtualScrollMode as Continuous in Grid. In Continuous mode, the data is loaded in grid when the scrollbar reaches the end hence the post is sent to the server for fetching data only when the scrollbar reaches the end. Please refer the below video, to know the post action details while scrolling. 
 
 
If we misunderstood your requirement, then please share the following information to find the root cause of an issue. 
 
1.       Please explain in which scenario you are facing an issue  “post action sent continuously to the server”.  
2.       Share the video to show the issue with profiler details  
3.       Issue replication procedure.  
4.       Please share the sample is possible.  
5.       Replicate the issue in the above provided sample. 
 
Question 3: while creating bundle for styles and scripts, and the EnableOptimisation as True, Icons are not present. 
 
We have analyzed your query. This issue occurs when virtual path folder name doesn’t match with referred theme CSS file folder. 
Please refer below KB link to resolve this issue in your application. 
 
Please let us know if you have any queries. 
 
Regards, 
Sureshkumar P 


SE Semper August 26, 2016 10:47 AM UTC

Thank you.

We don't have a database attached to our project but use ms sql stored procedures to populate a custom class object and attach it to the grid datasource.
Do you perhaps have an example of virtual scrolling for that scenario?

Question 3
I already did exactly like described in
https://www.syncfusion.com/kb/3036/how-to-resolve-css-reference-issue-while-including-the-theme-file-in-bundle
Before that we didn't see any images from the theme.
Now only the vertical scrollbar handle is missing. Localy it is shown, but not when deployed.
And all other images are shown. It doesen't make any sense. And there are no console errors.
I've tried so many combinations. I don't know what else to do.

FP Francis Paul Antony Raj Syncfusion Team August 30, 2016 11:39 AM UTC

Hi Semper, 
 
Query #1) We don't have a database attached to our project but use ms sql stored procedures to populate a custom class object and attach it to the grid datasource.  
  
We have prepared a sample based on your query and it can be downloaded from the below link,  
  
 
Also please refer the below code example,  
 
[ValuesController.cs]  
  
public PageResult<EditableOrder> Get(ODataQueryOptions opts)  
        {  
            var data = OrderRepository.GetAllRecords().ToList();  
            var count = 0;  
            var result = data.AsQueryable();  
            if (opts.InlineCount != null)  
                count = result.Count();  
  
            if (opts.Filter != null)  
            {  
                result = opts.Filter.ApplyTo(result, newODataQuerySettings()).Cast<EditableOrder>();  
  
                count = result.Count();  
                return new PageResult<EditableOrder>(result, null, count);  
            }  
            else  
                return new PageResult<EditableOrder>(opts.ApplyTo(result.AsQueryable()) as IEnumerable<EditableOrder>, null, count);  
        }  
 
In the above example we have populate the class object and bound it to the grid dataSource. Also with that we have performed the grid actions (sorting, filtering, editing, paging and searching) in server side.  
Query #2) all other images are shown, no console errors, the vertical scrollbar handle is missing 
 
We have tried to reproduce the reported issue (“all other images are shown, no console errors, the vertical scrollbar handle is missing”). But we are unable to reproduce the issue at our end. Vertical scrollbar shown properly when deployed or run the project in localhost. 
 
We have used the bundles as per the code you have referred in the sample. For us, when deploying the project, control get rendered properly with vertical scrollbar.  
If some images missed in web page, then it can be rectified based on the below suggestion. 
 
But in your case no vertical bar is shown in dropdown and grid. Can you please share the below details? 
 
#1) All other controls rendered properly in the page with necessary images (including up/down arrow icons in scrollbar) and only vertical scrollbar is missing? 
If icons are not referred in scroller, then please check with the other icons in your application as referred in the below application. 
 
#2) If still issue persists with vertical scroller, can you please share the sample or video to reproduce the issue at our end. This will help us to analyze and provide the solution at the earliest. 
 
Regards, 
Francis Paul A 


SE Semper September 9, 2016 09:34 AM UTC

Hi,

thank you for the feedback.
I have managed to solve the images that were not showing.


As for Query #1, there must be something I don't understand.

In your example you are pulling all the records to populate a class object, like we do in our project
and then do the pagination (and filtering etc) on the server side

But if we have 50.000+ records, then the time it takes to populate all the data is still very long.
Because it seems that you are first getting all the data, and then paginate to render in the grid.

The var data = OrderRepository.GetAllRecords().ToList(); can in our case be a lot of rows.
That's what we're trying to avoid.
And if we take only first 200 for example, that wouldn't be helpful either, because we will paginate only those 200 and not the rest.
Am i wrong on this?

Also is there a chance for a VB example?

Thank you in advance.

MS Mani Sankar Durai Syncfusion Team September 12, 2016 02:53 PM UTC

Hi Semper, 

We have analyzed your query and we have modified the sample by passing 1000 Records without converting as list. 
The modified sample can be downloaded from the below link, 

Also please refer the below code example, 
public PageResult<_30000Record> Get(ODataQueryOptions opts) 
        {     
var data = db._30000Records; 
            var count = 0; 
            IEnumerable<_30000Record> result = null; 
            if (opts.InlineCount != null) 
                count = data.Count(); 
 
            if (opts.Filter != null) 
            { 
                result = opts.Filter.ApplyTo(data, new ODataQuerySettings()).Cast<_30000Record>(); 
 
                count = result.Count(); 
                return new PageResult<_30000Record>(result, null, count); 
            } 
            else 
                return new PageResult<_30000Record>(opts.ApplyTo(data.AsQueryable()) as IEnumerable<_30000Record>, null, count); 
        } 
 

In the above code example, we have passed the data with 1000 Records without converting as a list and in the initial rendering we have done pagination when binding data to the grid. When performing server side actions it will takes some time to load the data. Also now the time taken to load the data after doing server side actions has been less when without converting as a list. 

Please refer the below screenshot for time taken for filtering and load the filtered data to the grid 
 

Please let us know if you need further assistance, 

Regards, 
Manisankar Durai. 


SE Semper September 19, 2016 08:07 AM UTC

Hi,

thank you for the feedback.

Can you please clarify a few things. 
You wrote that you are passing 1000 records. Where did you define that 1000?
I see that you are pulling all 30000 records and with scrolling every 95 rows is taken.
Which is good and fast.

But you are using linq to sql dbml and we just populate our class (which is the datasource) from an external sql table
When i implemented your code with our data all the records are loaded at once. 
Can you please read this api function and help with the implementation where there is no dbml and no Linq.DataContext

 Public Function GetMasterData(opts As ODataQueryOptions) As PageResult(Of Vehicles)
            Dim CMD As New SqlCommand("Vehicles_Select")
            CMD.CommandType = CommandType.StoredProcedure
          
            Dim VehiclesList As New List(Of Vehicles)()
            Using connection = New SqlConnection(GlobalGS.DB())
                connection.Open()
                Using command = CMD
                    CMD.Connection = connection
                    Using reader = command.ExecuteReader()
                        While reader.Read()
                            Dim item As New Vehicles() With {
                            .ID = reader("ID"),                        
                            .Name = reader("Name").ToString()}
                            VehiclesList.Add(item)
                        End While
                    End Using
                End Using
            End Using
            Dim count = 0
            Dim result As IEnumerable(Of Vehicles) = Nothing
            If opts.InlineCount IsNot Nothing Then
                count = VehiclesList.Count()
            End If

            If opts.Filter IsNot Nothing Then
                result = opts.Filter.ApplyTo(VehiclesList.AsQueryable(), New ODataQuerySettings()).Cast(Of Vehicles)()
                count = result.Count()
                Return New PageResult(Of Vehicles)(result, Nothing, count)
            Else
                Return New PageResult(Of Vehicles)(TryCast(opts.ApplyTo(VehiclesList.AsQueryable()), IEnumerable(Of Vehicles)), Nothing, count)
            End If
        End Function


Also, your refresh button doesn't work in the sample you provided, or with our solution.
It works only until you start to scroll and load more data.
After scrolling and clicking on the refresh it throws an error Cannot read property length of undefined.



RU Ragavee U S Syncfusion Team September 22, 2016 07:22 AM UTC

Hi Semper, 

Sorry for the inconvenience caused. 

Query #1: You wrote that you are passing 1000 records. Where did you define that 1000? 

We have mistakenly updated like we are passing 1000 records. Actually we have meant to say that we have bound 30000 records to grid and fetch data on demand from the api controller. 

Query #2: Can you please read this api function and help with the implementation where there is no dbml and no Linq.DataContext 

We understand that you would like to fetch data from sql instead of accessing from Linq DataContext. We have achieved this by storing the data in an static List initially and perform data fetching from the globally stored list everytime. 

This will inturn enhance the performance such that everytime it is not necessary to read the dataSource from the database using ExecuteReader. 

Query #3: After scrolling and clicking on the refresh it throws an error Cannot read property length of undefined. 

The cause of the issue is that we are updating the dataSource for the Grid after navigating to a certain page using virtual scrolling. So once the data is re-bound to grid, the currentPage of the Grid is not updated. So we suggest you to reset the current page of the Grid pageSettings before refreshing the dataSource of the Grid. Please refer to the below code example. 

<script> 
       function btnClick() { 
           $("#EmployeesGrid").ejGrid({ pageSettings: { currentPage: 1 } }); 
           $("#EmployeesGrid").ejGrid({ dataSource: ej.DataManager({ url: "api/Values", adaptor: new ej.WebApiAdaptor() }) }); 
       } 
   </script> 

For your reference, we have modified the previously updated sample with the above solution, which can be downloaded form the below location. 


Regards, 
Ragavee U S. 


SE Semper September 22, 2016 09:42 AM UTC

Hi,

that works perfectly, thank you!

One thing though.
If you scroll through the data with the mouse scroll wheel to the end of the grid, the last subset (skip-take) gets repeated with no reaching to the end of the grid.
There is no calling the api function on that last take but the grid gets scrolled indefinitely.
You must try that on a smaller set of data (not 30000) to get to the end quickly.

If you just use the mouse click and pull the vertical scroll bar a few times (if there is ~200 rows) to get to the end it works ok.

But not if you use only mouse wheel scroll all the way down.

Can you please check.

Thank you again for all the help.





SE Semper September 22, 2016 11:10 AM UTC

One more thing.

The refresh still doesn't behave as it should.

In your sample (when preserving log) you get an error:
Uncaught TypeError: Cannot read property 'indexOf' of undefined
So the whole page gets refreshed, not only the grid.

In my case the grid only gets refreshed but i end up with a problem i wrote about in this thread.



RU Ragavee U S Syncfusion Team September 23, 2016 11:56 AM UTC

Hi Semper, 

Query #1: If you scroll through the data with the mouse scroll wheel to the end of the grid, the last subset (skip-take) gets repeated with no reaching to the end of the grid. 

We are sorry but we are unable to reproduce the reported issue at our end. Could you please share the following details for our reference? 

1.       Video demo of the reported issue. 
2.       Essential studio version details. 
3. Grid rendering code. 
4.       If possible, please reproduce the issue in the provided sample and share. 

Query #2: Uncaught TypeError: Cannot read property 'indexOf' of undefined 

Since you want to refresh the dataSource of the Grid, we suggest you to use the dataSource method of the Grid instead of using setModel. Please refer to the below code example. 

   <script> 
       function btnClick() {            
           $("#EmployeesGrid").ejGrid("dataSource", ej.DataManager({ url: "api/Values", adaptor: new ej.WebApiAdaptor() }) ); 
       } 
   </script> 

Please use the above solution in order to handle the above reported query. 

Query #3: In my case the grid only gets refreshed but i end up with a problem i wrote about in this thread. 

Please follow up with the forum #125551 for further updates on this query. 

Regards, 
Ragavee U S. 


Loader.
Up arrow icon