Grid with UrlAdapter (Ajax) and FilterType Excel, How populate the checkboxList

[Benjamín Rivero Cruz]

I have a Grid with a UrlAdapter (for ajax) that works fine, the Grid call's the action and get the datasource. Also I configure the Filter to FilterType.Excel and this Filter works ok, on the controller action with the datamanager i can retreive the page, skip, take, etc. of the datamanager. 

The question/problem is, How can i populate the checkbox list of each filter column????

If I use a simple datasource without UrlAdapter (passing all data) the checkboxes are generated automatically, but my table is very large so for optimization reasons I need to use UrlAdapter (Ajax) but I cant find an example or sample code to pass the data for each filter and populate the checkboxes. Is this posible?

Thanks in advance...

[Seeni Sakthi Kumar Seeni Raj]

 

Hi Benjamin,  

 

Thanks for contacting Syncfusion support.  

 

We could see you would like to populate the Grid Excel filter checkbox and need to handle the large records in the Grid. By default, Grid will fetch only first 1000 records while opening the Excel Filter dialog. So there won’t be any possibility for the serialization problem or performance issues.  

 

We have created a sample that can be downloaded from the following location. 

 

Sample: http://www.syncfusion.com/downloads/support/forum/128998/ze/F1289981468990895.zip 

 

In the above sample, we have bound Grid with 30,000 records (load on demand concept). Upon clicking the Filter icon, 1000 records will be fetched and displayed in the excel filter dialog. As you can see the “CompanyName” and “Address” column excel filter dialog displays a label “Not all items showing” which indicates there are more values for that column. You can search for the values and get them displayed in the checklist. 

 

You can also customize the requested value (1000) by resetting the MaxFilterChoices Property of Grid FilterSettings. If you have changed this value to 500, only 500 records will be requested to the server and the same has been populated in the Excel Filter Dialog. Later, on searching the values in excel filter, requests will be sent and populated based on that. Refer to the following code example. 

 

@(Html.EJ().Grid<object>("FlatGrid") 

        .Datasource(ds => ds.URL("/Home/DataSource").Adaptor(AdaptorType.UrlAdaptor)) 

        .AllowPaging() 

        .AllowFiltering() 

        .FilterSettings(f => f.FilterType(FilterType.Excel).MaxFilterChoices(500)) 

            . . .  

                   . ..  

) 

 

 

Regards, 

Seeni Sakthi Kumar S.  

[Benjamín Rivero Cruz]

Hi Seeni Sakthi Kumar S. thanks for your response and example... I have some doubts about it:

1. On the HomeController, ActionResult Index you call OrderRepository.GetAllRecords method, wich returns the "orders" data based on the session or a query to Table Orders. But the grid shows info from the table called 30000Records that have a diferent structure, so, first, what's the point of query this Orders table an pass this data to the view? It's a typo?
2. On  the HomeController, ActionResult DataSource a query was made with this line: IEnumerable datasource = new MyDbDataContext()._30000Records.ToList(); with this all the records are queryed from the database, every time that the user interacts with the grid (paging, filter, etc) I think that for optimization this is not correct, because this query and this 30,000 records are always queryed from the database. And after that with the object DataOperations the results are filtered for pagination, etc. Its this optimal? There's should be a way of only query the data that will be passed to the view... Right now I make a Session with this 30,000 records and if this session exist, only use the data of the session and if is null I use the data of the DB and save in the session. 

What you suggest in this escenario?

[Seeni Sakthi Kumar Seeni Raj]

Hi Benjamin, 

 

Query #1: On the HomeController, ActionResult Index you call OrderRepository.GetAllRecords method, wich returns the "orders" data based on the session or a query to Table Orders. But the grid shows info from the table called 30000Records that have a diferent structure, so, first, what's the point of query this Orders table an pass this data to the view? It's a typo? 

 

We are sorry for the inconvenience. We have mistakenly placed this line in the Index method which is not needed. We have modified sample. 

 

Query #2: ActionResult DataSource a query was made with this line: IEnumerable datasource = new MyDbDataContext()._30000Records.ToList(); 

 

As per your request, we have prepared a sample that loads only needed records to the Controller page using the Dynamic Linq method. Refer to the following articles for the Dynamic Linq.  

  

http://www.c-sharpcorner.com/UploadFile/deveshomar/dynamic-linq-library-in-C-Sharp/ 

https://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library  

 

@(Html.EJ().Grid<object>("FlatGrid") 

        .Datasource(ds => ds.URL("/Home/DataSource").Adaptor(AdaptorType.UrlAdaptor)) 

        .AllowPaging() 

        .AllowFiltering() 

        .FilterSettings(f => f.FilterType(FilterType.Excel)) 

           . . .  

                 . . .  

) 

 

        public ActionResult DataSource(DataManager dm) 

        { 

            MyDbDataContext db = new MyDbDataContext(); 

            var result = db._30000Records.AsQueryable(); 

            var count = db._30000Records.AsQueryable().Count(); 

            if (dm.Where != null) 

            { 

                List<WhereFilter> Where = dm.Where; 

                for (var i = 0; i < Where.Count(); i++) 

                { 

                    if (Where[i].IsComplex) 

                    { 

                        string cond = Where[i].Condition; 

                        string connStr = "", connVal = ""; 

                        List<WhereFilter> pred = Where[i].predicates; 

 

                        var str = 0; 

                        for (var p = 0; p < pred.Count(); p++) 

                        { 

                                . .  

                                       . . . 

                        } 

 

 

                        string[] s = connVal.Split(','); 

                        if (str > 0) result = result.Where(connStr, s); 

                        else result = result.Where(connStr); 

                    } 

                    else 

                    { 

                      . . ..  

                            . . . 

 

                    } 

                } 

            } 

            count = result.AsQueryable().Count(); 

            //OrderBy must be applied to anyone of the column to apply the skip and take 

            result = result.OrderBy("CompanyName").Skip(dm.Skip).Take(dm.Take); 

            if (dm.Sorted != null) 

            { 

                   . ..  

                          . .                 

 

                result = result.OrderBy(sortText); 

            } 

            var list = result.ToList(); 

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

        } 

 

 

To avail a pagination using Skip and Take methods of Linq, OrderBy must be applied in the given Queryable variable. Otherwise, a runtime exception will occur. Please make a note, response to the Grid must be wrapped with the result/count pair as shown in the above code example.  

 

The modified sample can be downloaded from the following location. 

 

Sample: http://www.syncfusion.com/downloads/support/forum/128998/ze/MvcApplication66920895841.zip 

 

Regards, 

Seeni Sakthi Kumar S.