Sorting and Pagination in ASP.NET 4.5
This is part 10 of Paul Cociuba’s series on ASP.NET 4.5.
In this chapter, I want to focus on how the revamped data-controls from ASP.Net 4.5 can sort and paginate data using Entity Framework context as a starting point. I will focus on the GridView controls that display both the list of categories available and the list of products in the database.
0:30 - Since the last video, the sample application has been expanded to include more data in the database. There are now 6 categories and 6 products in the database, so a page size of 5 in the GridView controls will yield two pages when displaying the data.
1:10 – To allow the sorting of the data, all that has to be changed on the two GridView controls on the Products.aspx and Categories.aspx pages is the value of the attribute AllowSorting which will now be set to true.
1:40 – Running the project after making this modification shows that the GridView column headers are now hyperlink fields, and clicking on these will sort the GridView in ascending or descending order on the particular column.
2:40 – The default CSS template that comes with the project also comes with built in styles for selectable column headers. It also comes with styles to indicate that data inside a column is sorted in ascending or descending order.
3:00 – To make use of these styles, we can use two tags in the GridView markup – the SortedAscendingHeaderStyle and the SortedDescendingHeaderStyle. The two classes that we set these tags to (‘asc’ and ‘dsc’) are already defined inside the CSS style sheet.
3:40 – applying these two new styles allows the GridView control to clearly indicate the sorted column and the direction in which the column is sorted (ascending vs. descending).
4:30 – The ‘asc’ and ‘dsc’ styles in the /Content/Styles.css make use of CSS selectors to select the ‘th’ tag (table header) which has the ‘asc’ or ‘dsc’ style applied to it from the columns, and then append the ▲ (up glyph) or ▼ (down glyph) symbols to the text of the anchor tag to indicate the way in which the data is sorted.
5:19 - When running the project again, a sorted column will actually change style to either the ‘asc’ or ‘dsc’ styles and will automatically be added (via style sheet) the ▲ (up glyph) or ▼ (down glyph) symbols to indicate the direction in which the data is sorted. Paginating to page number 2 of the data in the GridView does not impact the sorting. The same column is shown as being sorted even when displaying a second page of results.
If you want to know how to enable the browse’s ‘back’ button to work when sorting a GridView or when simply paginating to a second page of results, please read this article that shows how to use the History control with pagination or sorting in WebForms.
6:00 – I then look to how the data-controls work under the covers to achieve the sorting and paging operations: the controls make use of the IQueryable objects that are returned by the code behind methods on the page.
6:30 – to see the controls in action, I place a breakpoint using Visual Studio on the GetCategories() code-behind method. During execution this will show that Entity Framework only builds an IQueryable object (but does not run any SQL queries), and that the method sends this object back to the data-control.
7:00 – running the project and SQL Server Profiler after having set the breakpoint in the code behind, clearly shows that there is no SQL statement executed when the GetCategories() method is run. The SQL query is executed only after the IQueryable object is passed back to the GridView, which then populates the values of the parameter of the generated query based on its state (sorted column, page, etc.)
8:00 – an examination of the generated SQL statement with the SQL Server Profiler shows that it will just be selecting the top 5 categories in the table, starting at row 0, since the GridView was showing the first page of 5 elements when the query was composed.
8:30 – with the project still running, paginating to page 2 of the categories list in the Categories.aspx page will have the GridView object generate a second SQL statement. The statement is executed against the database only after the IQueryable object has been passed from the GetCategories() method to the GridView, which has filled in the values of the parameters in the query. This query starts with all rows greater than 5 (>5) since we are on the second page of the result set.
The control will only load the data it needs to display a single page of results. This strategy is called lazy loading, as the control only loads the data when it is needed – in opposition with data being loaded and cached into memory.
9:20 – The final test is sorting the GridView control on one of the columns while on page 1 of the result set. The SQL statement that is executed following this shows that the first 5 elements are selected, starting from row 0, since this is first page of categories, and that they are ordered in ascending order via Name. Clicking on the ‘Name’ column header yet again generates a second statement where instead of having an ascending sorting via Name the sorting will be in descending order.
The next chapter of the series will start looking into implementing the ‘Repository’ development pattern, to separate out the business logic from the code behind (presentation logic) of the application. This will allow the decoupling of the layers and the separation of concerns that is key to modern application design principles.
Original content from Paul Cociuba ; posted by MSPFE Editor Arvind Shyamsundar
Comments
Anonymous
July 07, 2014
Very well explained! Thanks a lot!Anonymous
July 11, 2014
Is this also working when having an n-tier asp.net 4.5 webapp having a repository call in a wcf service?