Anyone that has worked with large data sets has had to figure out this problem at some point. Any application that returns a large number of records at one time needs to have a way to paginate the results so the end user doesn’t get overwhelmed by the data. This came up recently on a project I’m working on, and a friend of mine John Miller had an interesting solution to the problem. I took our solution and converted it to C# for this example. As I started working on this, I was surprised by how much stuff was actually here. I have made my sample code available here if you would like to follow along, especially since I will more than likely gloss over some stuff that isn’t critical to the core concept.
Requirements
This example was designed using SQL Server 2005 as a back-end. That was the environment that we were working with, and there is no requirement to port this to any other database. As such, we used a stored procedure to do most of the hard work. You will have to adapt this to your database of choice if you’re on a different platform.
We also used this in a web application, so to make this solution available across pages on the site, a user control made sense here.
If the code in this article is hard to read I apologize…I had to make some formatting changes to get everything to fit on the page. It is definitely formatted better in the code download.
Setup
In the download, you will find a .sql script called tPagerDemo.sql. This script contains the create script for the table that I used for this example, and it handles the creation of the stored procedure that will be doing the work for us, called psPagerUsers. The example is simple, it’s just a group of names with ID’s associated with them, but if you want to use the same data I did for my example, I included a .txt file called PagerData.txt that contains my sample data set. This set isn’t large at all, only 32 records. You can import my sample data directly into your table after it is created using the data import task.
Output
The finished product for us will be a grid showing 5 rows of data at a time, and will contain links to all the pages necessary to paginate our result set. Although this example does not need this, our pager control will also have a check to make sure only 10 “pages” of links are ever shown at a time.
Here is a screenshot of our finished control: (yes, I’m a football fan, and my favorite team is the Steelers)
Notice that we’re only showing 5 rows at a time, and that we have a “next” link available. In this case, there is no “previous” link because we are already on the first page. Also, we will make the rows per page configurable when we build the control.
Stored Procedure
The key concept in this implementation is that we want the database to do all of the work. I only want to bring back N records over the wire, where N is the number of rows per page. The hard part is remembering where we are on the database for each request. This can be more of a challenge if you are sorting records, or filtering the table results. We chose to have a stored procedure do the work for us, and use temporary tables do the work of manage the pagination.
The stored procedure has 3 parameters, 2 of them for input, and an output parameter that keeps track of how many total rows there are. This example didn’t do any sort of filtering, I’m just bringing back all of the records. If I was filtering the data, I would add another input parameter and use it to filter the records by whatever column I wanted.
1: SET ANSI_NULLS ON
2: GO
3: SET QUOTED_IDENTIFIER ON
4: GO
5:
6: CREATE PROCEDURE [dbo].[psPagerUsers]
7: @CurrentPage INT = NULL,
8: @RowsPerPage INT = NULL,
9: @TotalRows INT OUTPUT
10: AS
11: BEGIN
12: SET NOCOUNT ON;
13:
14: --Drop temp table if it already exists
15: IF OBJECT_ID('tempdb..#tempPagerDemo') IS NOT NULL
16: DROP TABLE #tempPagerDemo
17:
18: --Create temp table
19: CREATE TABLE #tempPagerDemo(ID INT IDENTITY(1,1), UserID INT)
20:
21: --Populate the temp table
22: INSERT INTO #tempPagerDemo(UserID)
23: SELECT UserID
24: FROM tPagerDemo (NOLOCK)
25: ORDER BY UserID
26:
27: --Set the TotalRows output parameter
28: SET @TotalRows = (SELECT COUNT(*) FROM #tempPagerDemo)
29:
30: --Get all records if either @CurrentPage or @RowsPerPage
31: --weren't specified in the parameters
32: IF (@CurrentPage = 0) OR (@RowsPerPage = 0)
33: BEGIN
34: SET @CurrentPage = 1
35: SET @RowsPerPage = @TotalRows
36: END
37:
38: --Get a page of records from the temp table and
39: --join them with the real table
40: SELECT pd.UserID, pd.FirstName, pd.LastName
41: FROM #tempPagerDemo (NOLOCK) tmp
42: INNER JOIN tPagerDemo (NOLOCK) pd ON tmp.UserID = pd.UserID
43: WHERE ID BETWEEN
44: ((@CurrentPage - 1) * @RowsPerPage + 1) AND ((@CurrentPage) * @RowsPerPage)
45:
46: --Drop temp table
47: IF OBJECT_ID('tempdb..#tempPagerDemo') IS NOT NULL
48: DROP TABLE #tempPagerDemo
49: END
If you are familiar with stored procedures, this one should make sense to you. I will cover it at a high level though. The first thing I do is create a temp table. This table has an identify field and also a field I can use to link to the main data table. In this case, since the UserID field is the primary key of the table tPagerDemo, I use a UserID field in my temp table.
The next thing that happens is the population of the temp table. This is where you would do any filtering of the data if necessary. In this case I just select all of the records. The important thing here is that you are NOT storing all of the data from the main table, only a key that is used to reference the main data table. After this is done, I set the output parameter to be the number of rows in the temp table so the control knows how many total records are present.
The last step is the most important step. This is where we take the current page and the rows per page parameters to figure out which records to bring back. The beauty of this system is that we are using the auto-increment feature of our temp table to do this. We know that our records are numbered sequentially from 1 to N, so once we are given the current page and rows per page variables, we can calculate exactly which records to bring back. At this time, we join our subset of the records with the main data table to get all of the necessary information about the record.
Pager Classes
I created 4 classes to handle the pagination logic. The first is the Pager class. This class only has a few private members, and a bunch of properties. The sole purpose of this class is to keep track of where you are in a recordset. The private variables are totalRows, rowsPerPage, and currentPage. With these pieces of data, we can derive properties such as TotalPages, PreviousPage, NextPage, FirstPage, LastPage, IsFirstPage, IsLastPage, and HasMultiplePages. This class also has some public methods for the creation of page number lists, which are pages 1 through 7 in our screenshot above.
The next class is the PaginationPageListItem class. This class just has a page number and a boolean variable that determines if it’s the current page. This is because we want the current page to show up as text, and all other pages to show up as links so we can jump to them.
The third class is the PaginationClickEventArgs class. This class extends the EventArgs class, and just adds a pageNumber field. This is used by the control to throw an event to the page whenever a link on the control is clicked. It is important to store the page number to load as part of the arguments because we need to know which rows to load with our stored procedure.
Lastly, we have a PaginatedList<T> class. This class extends List<T>, and adds a pager to the generic list. Adding the pager allows us to see which part of the data collection we have in the list. Remember, the whole key here is that we are only bringing back the records for the specific “page” we are looking at…so our PaginatedList does not contain the entire data set.
To try and keep this article a reasonable length, I’m not going to show you all of these classes here, they are all available in the code download. For my example, all of these classes were just dropped in the App_Code folder.
Data Access
I decided for this example to use a simple data layer class to grab the data. I just dropped this in the App_Code section of the website to use it on my page. Since this example is trivial, I only have 1 method in my class to access the data. My class looks like this:
1: public class DLayer
2: {
3: public static PaginatedList<Person> LoadPeople(int currentPage,
4: int rowsPerPage)
5: {
6: PaginatedList<Person> lstPeople = new PaginatedList<Person>();
7:
8: using (SqlConnection conn = new SqlConnection("yourConnString"))
9: {
10: conn.Open();
11: using (SqlCommand cmd = new SqlCommand("psPagerUsers", conn))
12: {
13: SqlDataReader rdr;
14: cmd.CommandType = CommandType.StoredProcedure;
15: cmd.Parameters.Add("@CurrentPage", SqlDbType.Int).Value =
16: currentPage;
17: cmd.Parameters.Add("@RowsPerPage", SqlDbType.Int).Value =
18: rowsPerPage;
19:
20: // Capture the output parameter.
21: SqlParameter paramTotalRows =
22: new SqlParameter("@TotalRows", SqlDbType.Int);
23: paramTotalRows.Direction = ParameterDirection.Output;
24: cmd.Parameters.Add(paramTotalRows);
25:
26: rdr = cmd.ExecuteReader();
27: while (rdr.Read())
28: {
29: Person current = new Person();
30: current.UserID = (int)rdr["UserID"];
31: current.FirstName = (rdr["FirstName"]).ToString();
32: current.LastName = (rdr["LastName"]).ToString();
33: lstPeople.Add(current);
34: }
35: rdr.Close();
36:
37: // Set up the pager.
38: int totalRows = (int)paramTotalRows.Value;
39: Pager pgr = new Pager(totalRows, rowsPerPage, currentPage);
40: lstPeople.Pager = pgr;
41: }
42: }
43: return lstPeople;
44: }
45: }
All this method does is call the stored procedure we created above to load the relevent people, and then sets up our pager. We use the rowsPerPage and currentPage parameters of this function and just pass them straight to the procedure. I should point out that this data class references another class I built called Person that just holds the ID, first name, and last name of the person. This class was also dropped in the App_Code folder.
Notice that this method returns a PaginatedList of type Person. We set up the pager for our list after the data has been loaded, and attach it to our list to be used by the control. Remember that the PaginatedList<T> extends List<T>, so that gives us the ability to bind our result to our GridView to display it to the user.
User Control
OK. So far we created our stored procedure to access the data, and created a data layer class to load this data and return a PaginatedList<Person>. We also set up all of the classes needed to create our pager control, so we can finally talk about building the control itself. I’m not going to go into the specifics for creating a user control here. I’ll touch on some points, but if you need more specific help, then Google is your friend.
Here is what the layout of our control looks like:
1: <%@ Control Language="C#" AutoEventWireup="true" CodeFile="PaginationControl.ascx.cs"
2: Inherits="PaginationControl" %>
3: <asp:Panel ID="pnlPager" runat="server">
4: <table id="tblPager" runat="server" class="PagerTable">
5: <tr>
6: <td class="LeftCell">
7: <asp:LinkButton ID="lbPagerPreviousPage" runat="server" Text="Previous"
8: OnClick="lbPager_Click" CssClass="PagerLink"></asp:LinkButton>
9: </td>
10: <td class="CenterCell">
11: <asp:LinkButton ID="lbPagerPreviousTen" runat="server" Text="<<"
12: OnClick="lbPager_Click" CssClass="PagerLink" />
13: <asp:Repeater ID="rptPagerPages" runat="server">
14: <ItemTemplate>
15: <asp:Literal ID="ltPagerDisabledPageLink" runat="server"
16: Visible='<%# Eval("DisplayAsText") %>'
17: Text='<%# Eval("PageNumber") %>'></asp:Literal>
18: <asp:LinkButton ID="lbPagerPageLink" runat="server"
19: Text='<%# Eval("PageNumber") %>' OnClick="lbPager_Click"
20: CommandArgument='<%# Eval("PageNumber") %>'
21: CssClass="PagerLink" Visible='<%# Eval("DisplayAsLink") %>'>
22: </asp:LinkButton>
23: </ItemTemplate>
24: </asp:Repeater>
25: <asp:LinkButton ID="lbPagerNextTen" runat="server" Text=">>"
26: OnClick="lbPager_Click" CssClass="PagerLink" />
27: <br /><asp:Literal ID="ltPagerCaption" runat="server"></asp:Literal>
28: </td>
29: <td class="RightCell">
30: <asp:LinkButton ID="lbPagerNextPage" runat="server" Text="Next"
31: OnClick="lbPager_Click" CssClass="PagerLink"></asp:LinkButton>
32: </td>
33: </tr>
34: </table>
35: </asp:Panel>
I am using a Repeater control to display the numbered links, and I also have links to load the next and previous ten pages of links, and to jump to the previous or next page. A style sheet controls the look and feel of the pager control (as well as the GridView) and has been included in the code download.
The code behind for this control has several items that should be pointed out. First…..a few of the pager properties are configurable from the web.config. The first is the amount of links that run across the bottom of the pager control. In our screenshot above, it is set to 10, so all of our pages show up. I can change a key I created in the web.config file called PaginationPageNumberLinksRange to make this value 5 (from 10), and while I’m in there I’ll change the PaginationRowsPerPage key to be 6 (from 5).
1: <appSettings>
2: <add key="PaginationPageNumberLinksRange" value="5"/>
3: <add key="PaginationRowsPerPage" value="6"/>
4: </appSettings>
The result of that configuration change would make our example from above look like this:
Notice that changing the rows per page to 6 reduced the number of pages we would need from 7 to 6, and since we are now only showing 5 pages at a time we get the >> that will allow us to see the last page.
One of the most important methods in the code behind of the control is the BuildPagination method.
1: public void BuildPagination(Pager pager)
2: {
3: _pager = pager;
4: CurrentPage = pager.CurrentPage;
5: string totalText;
6:
7: if (_pager.TotalPages > 0)
8: {
9: pnlPager.Visible = true;
10: lbPagerPreviousPage.CommandArgument =
11: _pager.PreviousPage.ToString();
12: lbPagerNextPage.CommandArgument =
13: _pager.NextPage.ToString();
14: lbPagerPreviousPage.Visible =
15: (_pager.HasMultiplePages && (!_pager.IsFirstPage));
16: lbPagerNextPage.Visible =
17: (_pager.HasMultiplePages && (!_pager.IsLastPage));
18:
19: // check that grammar!
20: if (_pager.TotalRows == 1)
21: totalText = "Record";
22: else
23: totalText = "Records";
24:
25: ltPagerCaption.Text =
26: String.Format("Page <strong>{0}</strong> of <strong>{1}</strong><br/>"
27: + "<strong>{2}</strong> {3}", _pager.CurrentPage,
28: _pager.TotalPages, _pager.TotalRows, totalText);
29:
30: int startPage =
31: Pager.GetPageNumberListStartPage(_pager, _pageNumberLinksRange);
32: int endPage =
33: Pager.GetPageNumberListEndPage(_pager, _pageNumberLinksRange, startPage);
34: List<PaginationPageListItem> pageNumbers =
35: Pager.GetPageNumberList(_pager, startPage, endPage);
36:
37: // Bind the repeater to our list of page items.
38: rptPagerPages.DataSource = pageNumbers;
39: rptPagerPages.DataBind();
40:
41: if (startPage > _pager.FirstPage)
42: {
43: lbPagerPreviousTen.Visible = true;
44: lbPagerPreviousTen.CommandArgument =
45: (startPage - 1).ToString();
46: }
47: else
48: lbPagerPreviousTen.Visible = false;
49:
50: if (_pager.TotalPages > endPage)
51: {
52: lbPagerNextTen.Visible = true;
53: lbPagerNextTen.CommandArgument =
54: (endPage + 1).ToString();
55: }
56: else
57: lbPagerNextTen.Visible = false;
58: }
59: else
60: pnlPager.Visible = false;
61: }
This is the method that actually creates the list of page numbers by binding the repeater, and it shows (or hides) different elements of the control based on the status of the pager. A few good examples of this are only showing the previous and next links when they make sense, and only showing the previous / next page groupings when they make sense.
Another important item to cover is the event handling. I want to generate an event any time a link is clicked so that the page hosting this control can handle this. I do this with the following code:
1: public delegate void PaginationClickHandler(object sender,
2: PaginationClickEventArgs args);
3:
4: // I use this syntax just to make sure that there is at least
5: // 1 thing subscribed to my event.
6: public event PaginationClickHandler PaginationClickEvent = delegate { };
7:
8: protected void lbPager_Click(object sender, EventArgs args)
9: {
10: LinkButton lb = (LinkButton)sender;
11: PaginationClickEvent(this,
12: new PaginationClickEventArgs(Convert.ToInt32(lb.CommandArgument)));
13: }
I use the PaginationClickEventArgs class that I created because it is important to be able to keep track of the page we are looking at. The last thing that is worth pointing out is that the control uses ViewState to keep track of the current page through a property of the control, so it can be accessed from the web form.
1: public int CurrentPage
2: {
3: get
4: {
5: if ((ViewState["CurrentPage"]) == null)
6: ViewState["CurrentPage"] = 0;
7: return (int)ViewState["CurrentPage"];
8: }
9: set { ViewState["CurrentPage"] = value; }
10: }
Page Layout and Binding
For the next step, we need to think about how we are going to display our data to the user. I chose to use a simple GridView control to show this data. We will be making a control to handle the pagination aspect, but this control only shows you which data is loaded, and allows you to load other pages of data. How you display this data is separate from the pagination. I could have just as easily made this an unordered list using the Repeater control if that’s how I wanted the data to be displayed.
1: <%@ Register Src="PaginationControl.ascx" TagName="Pagination" TagPrefix="JAL" %>
2:
3: <form id="form1" runat="server">
4: <div>
5: <asp:GridView ID="gdvBlogTest" runat="server"
6: AutoGenerateColumns="false"
7: EmptyDataText="No Open Projects Found."
8: CssClass="DefaultGrid"
9: HeaderStyle-CssClass="DefaultGridHeader"
10: RowStyle-CssClass="DefaultGridRow"
11: AlternatingRowStyle-CssClass="DefaultGridAltRow">
12: <Columns>
13: <asp:TemplateField HeaderText="ID">
14: <ItemTemplate>
15: <asp:Label ID="lblID" runat="server">
16: <%# Eval("UserID") %>
17: </asp:Label>
18: </ItemTemplate>
19: </asp:TemplateField>
20: <asp:TemplateField HeaderText="First Name">
21: <ItemTemplate>
22: <asp:Label ID="lblFirstName" runat="server">
23: <%# Eval("FirstName") %>
24: </asp:Label>
25: </ItemTemplate>
26: </asp:TemplateField>
27: <asp:TemplateField HeaderText="Last Name">
28: <ItemTemplate>
29: <asp:Label ID="lblLastName" runat="server">
30: <%# Eval("LastName") %>
31: </asp:Label>
32: </ItemTemplate>
33: </asp:TemplateField>
34: </Columns>
35: </asp:GridView>
36: <JAL:Pagination ID="jalPager" runat="server" PagerTableCSSClass="PagerTable" />
37: </div>
38: </form>
This shows an abbreviated version of what our .aspx page looks like. I cut out some of the fluff, such as the Page directive, the link to the style sheet, etc. I just wanted to show how to register the control that we created, how do declare the control, and where to put it on the page in relation to our GridView.
The final step is to show how the GridView gets populated, and how we handle the PaginationClick event. I added these 2 methods to the code behind of web form to handle everything.
1: protected void Page_Load(object sender, EventArgs e)
2: {
3: PaginatedList<Person> lstPeople;
4:
5: // Bind the data grid to the list of people.
6: lstPeople = DLayer.LoadPeople(CurrentPage,
7: Convert.ToInt32(ConfigurationManager.AppSettings["PaginationRowsPerPage"]));
8:
9: gdvBlogTest.DataSource = lstPeople;
10: gdvBlogTest.DataBind();
11:
12: // Build the pagination elements.
13: jalPager.BuildPagination(lstPeople.Pager);
14:
15: // Subscribe to the pager event.
16: jalPager.PaginationClickEvent +=
17: new PaginationControl.PaginationClickHandler(jalPager_PaginationClickEvent);
18: }
19: /*------------------------------------------------------------*/
20: void jalPager_PaginationClickEvent(object sender, PaginationClickEventArgs args)
21: {
22: PaginatedList<Person> lstPeople;
23:
24: // Determine which page should be displayed and rebind the gridview.
25: CurrentPage = args.PageNumber;
26: lstPeople = DLayer.LoadPeople(CurrentPage,
27: Convert.ToInt32(ConfigurationManager.AppSettings["PaginationRowsPerPage"]));
28:
29: gdvBlogTest.DataSource = lstPeople;
30: gdvBlogTest.DataBind();
31:
32: // Rebuild the pagination.
33: jalPager.BuildPagination(lstPeople.Pager);
34: }
The page load takes care of the initial binding of the GridView control, and it also builds the pagination for the PaginationControl using the pager from the PaginatedList<T>. Remember that this pager object contains information about the specific records that are coming back, that way the GridView and the PaginationControl stay in sync. It also subscribes to the PaginationClick event. When one of these events fires, our PaginatedList is repopulated based on the page specified in the PaginationClickEventArgs class, the GridView is rebound, and the PaginationControl has it’s navigation rebuilt.
Once again, you can download the sample code here. Let me know what you think of this method of handling pagination, I’m interested to hear how you think it could be optimized.
Recent Comments