Is it possible to implement paging in a way that the Datasource contains only the Data to display?
Lets assume i have 1 Million records, but for performance reasons i just want to load 10 at a time from sql server.
How can I tell the Grid that he's on Page 2 of X and have a total record of 1 Million (in AJAX mode)?
DECLARE @PageNumber AS INT, @RowspPage AS INT SET @PageNumber = 2 SET @RowspPage = 10 SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE FROM TB_EXAMPLE ORDER BY ID_EXAMPLE OFFSET ((@PageNumber - 1) * @RowspPage) ROWS FETCH NEXT @RowspPage ROWS ONLY;
Thanks and best Regards
Giuseppe
15 Answers, 1 is accepted
Hello gsharp,
By default the Kendo UI for ASP.NET MVC will enable server operations ( sorting, filtering, grouping and paging). This means that the data source will leave the data item paging implementation to the remote service. Setting .Pageable() for the Grid and .PageSize(10) for the data source will achieve the desired functionality.
@(Html.Kendo().Grid<KendoGridAjaxBinding.Models.Product>() .Name("client") .... .Pageable() .DataSource(dataSource => dataSource .Ajax() .PageSize(10) .Model(model => model.Id(p => p.ProductID)) .Read(read => read.Action("Products_Read", "Home")) .Update("Editing_Update", "Home") ))It will load only 10 items at a time and show the total amount of records.
Regards,
Boyan Dimitrov
Telerik
Rank 1
Hi Boyan
Tahnks for your answer. But that's actually not what I want. your solution still require that i load all 1 Million records from the Database to show Page 1 of X and total records of 1 Million.
What I want is to have only 10 records in the DataSource itself and "tell" the grid, hey look I give you 10 records, but tell the grid that actually there are a total of 1 Million records.
Hope my question is a bit clearer.
Regards
Giuseppe
Hello gsharp,
Please find attached a sample example of Kendo UI for ASP.NET MVC with server operations. If run in debug mode the following line of code returns only 10 items from the data base.
DataSourceResult result = products.ToDataSourceResult(request);Also I checked the Sql query:
SELECT TOP (10) [Extent1].[ProductID] AS [ProductID], [Extent1].[ProductName] AS [ProductName], .....Boyan Dimitrov
Telerik
Rank 1
Rank 1
Hello Mike,
As far as I understand the exception is thrown when the stored procedure is executed without calling the ToDataSourceResult extension method in the Kendo,MVC.dll. If so I would suggest to take a look at the ASP.NET System.OutOfMemoryException forum thread.
Regards,
Boyan Dimitrov
Progress Telerik
Rank 1
I'm in agreement with gsharp. The example you posted is a bit deceptive in not addressing the question of whether this is supported or can even be done.
Every example that has custom paging always fails to address the question. Every example has the controller receiving the DbContext for the ToDataSourceResult method to then do the paging which is not what is wanted.
So I'm going to ask this question because I need a concrete answer.
My Controller does not have access to the DbSet. My Controller calls a data service which returns ViewModel objects. My data service does the filtering and returns only the paged 10 items which are converted to ViewModel objects and passed to the controller. The controller then wraps the 10 ViewModel objects via ToDataSourceResult
Just like gSharp said, unless my dataservice returns all 1million records to the controller, paging will not work. it will only load the first page and stop.
It is clear that ToDataSourceResult method is looking for all 1 million records for it to then do the paging.
I don't want that. I want to return 10 records and the total count.
How can that be done?
To achieve such goal the DataSourceRequest object should be sent from your Controller to the DAL service. The potential problem you might face is related to issue with serialization/deserialization of the DataSourceRequest object when sent from one project to another one.
In order to avoid any issues with serialization/deserialization of the DataSourceRequest object ( if it is sent to the data access layer) I would recommend to parse manually the DataSourceRequest object to custom class that holds the state parameters as int and string types and use that object in the data access layer:
public class DataSourceState{ public int PageSize { get; set; } public int Page { get; set; } public string Filter { get; set; } public string Sort { get; set; } public string Group { get; set; } public string Aggregate { get; set; } public DataSourceRequest DataSourceRequest { get { var request = new DataSourceRequest() { Page = Page, PageSize = PageSize }; if (!string.IsNullOrEmpty(Filter)) { request.Filters = FilterDescriptorFactory.Create(Filter); } if (!string.IsNullOrEmpty(Sort)) { request.Sorts = GridDescriptorSerializer.Deserialize<SortDescriptor>(Sort); } if (!string.IsNullOrEmpty(Group)) { request.Groups = GridDescriptorSerializer.Deserialize<GroupDescriptor>(Group); } if (!string.IsNullOrEmpty(Aggregate)) { request.Aggregates = GridDescriptorSerializer.Deserialize<AggregateDescriptor>(Aggregate); } return request; } }}Regards,
Boyan Dimitrov
Progress Telerik
Rank 1
Well after trying and failing repeatedly, I simply bit the bullet once it became clear that this would not work as I wanted without more issues and questions.
So I reverted back to the way they described but now noted one caveat that was a contributory/likely issue.
https://docs.telerik.com/aspnet-mvc/helpers/grid/binding/ajax-binding
"Important
The ToDataSourceResult() method will page, sort, filter, and group the collection that is passed to it. If this collection is already paged, the method returns an empty result."
What I had to do was rewrite the Middle Service to not enumerate the objects or do the paging or even create the viewmodels.
I had the MiddleService just pass the Linq ExpressionTree IQuerable<T> up after apply the selection criteria.
The change to the codebase was very minimal for me but might be really sucky for others.
I simply moved my viewmodel creation codeblock logic right into the controller method.
Then I had to do the projection to the ViewModel Object in the controller as shown in the example
var product = _myMiddleService.GetProductsBasedOnScreenOptions(screenOptions) // Used all the options to setup the IQueryable of my Repository object without projecting.DataSourceResult result = products.ToDataSourceResult(request, product => new ProductViewModel { ProductID = product.ProductID, ProductName = product.ProductName, UnitsInStock = product.UnitsInStock }); return Json(result);
This works as I want with the the query to the database that is generated only asking for the 20 records per page.
Maybe somebody else will come up with something else, but we have a large amount of code to implement and could not dwell on this issue.
Thanks
Rank 1
I realize this is an old thread but I came across it today as I almost submitted a ticket for this very issue. After reading this forum I felt like if I was going to submit a ticket I was going to have to get my ducks in a row. Fortunately this actually led me to an answer on my own. I'm going to give you step by step what I did so anyone looking at this can see everything it takes.
First....since this is data is being loaded by the listview from javascript I have my an action in my controller defined like this:
public ActionResult GetSearchResults([DataSourceRequest]DataSourceRequest request, string searchText)
In my case I'm calling a search function of a library I have no control over. I tell it how many items per page and what page I'm on which you can get from the request object passed in to the action.
IEnumerable<string> searchIndexes = new List<string> { "MainSitePageCrawlerIndex" };SearchParameters searchParameters = SearchParameters.PrepareForPages(searchText, searchIndexes, request.Page, request.PageSize, MembershipContext.AuthenticatedUser);SearchResult searchResult = SearchHelper.Search(searchParameters);
To keep my example flowing I'll include the next line. I'm simply copying the data from one list to another...but the list remains just the 10 items for the current page...just a different object type.
List<SearchResultEntry> searchResultEntries = searchResult.Items.Select(searchResultItem => new SearchResultEntry { Title = searchResultItem.Title, Url = ((TreeNode)searchResultItem.Data).GetFriendlyUrl(), Preview = SearchResultHelper.GetSearchResultPreview(searchResultItem) }).ToList();
Here is the key to the whole thing. If you use ToDataSourceResult method there is logic in there that is incomplete. It assumes you are passing everything in...and it is this code that makes it seem like the listview is not working correctly (or whatever control you might be using). I found, however, there isn't anything real special about this method so I just created the DataSourceResult manually. Regardless of which way you go you also must set the Total field manually
DataSourceResult dataSourceResult = new DataSourceResult() { Data = searchResultEntries, Total = searchResult.TotalNumberOfResults };return Json(dataSourceResult);
I hope this helps someone. Thankfully I figured this out in one day....but it looks like others have struggled with this for sometime.
Thank you,
Brandon
Rank 1
Veteran
Rank 1
Veteran
Hi @freeTheCode's
i am having the same issue right now, i have huge number of records, so what i want is to load 10 records in db per click on each page but will display i have thousands of records, thanks
Rank 1
Rank 1
Veteran
public ActionResult GetRegularCutups([DataSourceRequest] DataSourceRequest request)
{
bool isAdmin = false;
decimal location = Convert.ToDecimal(Location);
int role = Convert.ToInt32(WebConfigurationManager.AppSettings["AdminId"]);
if (RoleId == role)
{
isAdmin = true;
}
else
{
isAdmin = false;
}
IEnumerable<MeatCutupHdrData> regularCutups = new List<MeatCutupHdrData>();
regularCutups = _mapper.Map<IEnumerable<MeatCutupHdrDto>, IEnumerable<MeatCutupHdrData>>
(_meatCutupHdr.GetRegularCutups(isAdmin, location));
return Json(regularCutups.ToDataSourceResult(request));
}
Rank 1
I believe you may have multiple questions you are trying to answer by the post. I believe I can help you on most if not all parts but I need a clearer understanding of where you are stuck (even if that is multiple places). Here is some general info:
- The action for the DataSourceRequest can be passed parameters to help filter your results (separate from paging)
- The DataSourceRequest object has a property for what Page is being requested and what the PageSize is so that when you pull data you can properly pull back only the data necessary for the current request
- The key to getting the grid to act right is getting it to know how many rows there are in total (or the max number you want someone to access). This is where I ran into trouble....turns out the answer is to not use ToDataSourceResult as you have above. Will include sample below
- To get the grid to request pages in the datasource request you set the PageSize property of the datasource object of the grid or listview in your MVC view
The alternative to using ToDataSourceResult as you did above would be this:
DataSourceResult dataSourceResult = new DataSourceResult(){ Data = regularCutups, Total = {totalNumberOfResults}};return Json(dataSourceResult);
As far as filling in {totalNumberOfResults} that depends on how you handle pulling the results from your database (or whereever it comes from). If you pull all the results back you can get a count obviously. You can then use Skip and Take to get the set of records for the given page. Depending on where you are pulling the data from you might be able to request the data in pages...if so then you can use what it tells you for the number of records...or do a second call to just return a count. That part, however, is outside the scope of the Telerik control itself....I'm mainly trying to help you figure out where to put the values once you get them.
Rank 1
Rank 1
{"Data":[{"Title":"History - 1993","Url":"/about-us/history?historicaleventid=94","Preview":"Northern Water expands its Irrigation Management Service to include a Turf and Urban Landscape Water Management and Conservation Program component. By"},{"Title":"History - October 1998","Url":"/about-us/history?historicaleventid=107","Preview":"Crews begin constructing a bypass flow structure at the Flatiron Power Plant. The bypass will enable water to bypass Flatiron in the event of a plant"},{"Title":"History - April 2000","Url":"/about-us/history?historicaleventid=113","Preview":"Windy Gap participants, the Subdistrict and Northern Water discuss increasing the Southern Water Supply Project pipeline’s capacity via installation"},{"Title":"History - March 2006","Url":"/about-us/history?historicaleventid=138","Preview":"Northern Water relocates a section of the St. Vrain Supply Canal upstream of the Little Thompson Siphon due to unstable geology. The canal is back in"},{"Title":"History - July 5, 1938","Url":"/about-us/history?historicaleventid=32","Preview":"Northern Water signs a contract with the United States to repay a portion of C-BT Project construction costs."},{"Title":"History - Jan. 1, 1962","Url":"/about-us/history?historicaleventid=65","Preview":"A 40-year repayment period commences for Northern Water\u0027s portion of C-BT Project facilities and construction costs."},{"Title":"History - 1968","Url":"/about-us/history?historicaleventid=67","Preview":"Northern Water purchases 11 acres adjoining the south and west sides of its current property to expand its headquarters. "},{"Title":"History - September 1999","Url":"/about-us/history?historicaleventid=109","Preview":"Crews finish work on the Southern Water Supply Project pipeline’s final 42 miles from Platteville to Fort Morgan."},{"Title":"History - July 14, 2000","Url":"/about-us/history?historicaleventid=116","Preview":"The Northern Water Board approves a contract with RB+B Architects, Inc. to design new headquarters facilities in Berthoud."},{"Title":"History - December 2000","Url":"/about-us/history?historicaleventid=118","Preview":"Northern Water and Reclamation sign C-BT Project Supplemental Contract No. 7 to modernize Horsetooth Reservoir’s four 50-year-old dams."}],"Total":100,"AggregateResults":null,"Errors":null}
Rank 1
If anyone else needs to convert a datatable to use in this way here's how it's done - just need to convince the client they don't need the actual number of records as that is the query that is possibly causing all the trouble in the first place :).
var data = dt.AsEnumerable().Select(
row => dt.Columns.Cast<DataColumn>().ToDictionary(
column => column.ColumnName,
column => row[column].ToString()
)).ToList();
result = new DataSourceResult()
{
Data = data,
Total = 3000
};
Rank 1
Rank 1
Rank 1