Whatever kind of software that you work on,  I’m sure you have some object tables in which ones you store typically the most important data (for example: Orders or Sales records). These tables need to be displayed in many ways and edited. One common way of representing them in C#/ASP.Net it’s by using a DataGridView or the equivalent UI control in other platforms.

Usually most UI-control providers encourage you to rely on them for paging capabilities. The potential issue is that in certain point your data size (or record count) will grow and will pass by the control capacity, degrading the response speed and even raising “time out exceptions” because the control usually fetches the entire data set, stores it to memory and pages it per request.

To keep your response size constant, while favoring your architecture performance and stateless you should implement the data base paging design pattern. Although is classified by the Yahoo!  Design pattern library as an UX-pattern, I think that there is a shared responsibility between layers for giving it the UI scope only.

As you may imagine, the pattern mentions that for a given long list of results you must display a limited data set (or pages). The limit is known as the PageSize. The pages must be numbered and the user should be able to navigate sequentially and randomly through them. In order to achieve this using a control library like Telerik you should define the control’s navigation as custom and implement the corresponding code. In the next Telerik’s sample a RadGid is defined with custom binding:

<telerik:RadGrid ID="RadGrid2" AllowSorting="true" DataSourceID="ObjectDataSource1"
ShowGroupPanel="true" AllowFilteringByColumn="true" Width="95%" ShowStatusBar="true"
runat="server" AllowPaging="True" OnColumnCreated="RadGrid2_ColumnCreated">
<ClientSettings AllowDragToGroup="true" />
<PagerStyle Mode="NextPrevAndNumeric"></PagerStyle>
<MasterTableView OverrideDataSourceControlSorting="true" />
</telerik:RadGrid>

The correspondent controller’s code is as follows:

protected void RadGrid1_NeedDataSource(object source, GridNeedDataSourceEventArgs e)
{
MyBusinessObjectCollection MyBusinessObjectCollection1 = new MyBusinessObjectCollection();
int startRowIndex = (ShouldApplySortFilterOrGroup()) ?
0 : RadGrid1.CurrentPageIndex * RadGrid1.PageSize;
int maximumRows = (ShouldApplySortFilterOrGroup()) ?
MyBusinessObjectCollection1.SelectCount() : RadGrid1.PageSize;
RadGrid1.AllowCustomPaging = !ShouldApplySortFilterOrGroup();
RadGrid1.DataSource = MyBusinessObjectCollection1.Select(startRowIndex, maximumRows);
}

Note that you should page in the data base to achieve the best performance, so as a Software Designer/Architect you should triage complexity and performance in order to define how your queries will be executed. If you have a small number of columns, probably by predefining the accepted filters and directions you will achieve a pretty good performance (an example of it is available here).

By other hand if you have many columns and many paging options (like sorting, filtering and grouping) you will have to use dynamic conditions. Dynamic conditions can be implemented in a complex managed code query builder or by the means of dynamic SQL so it’s necessary to triage again according to your specs. Dynamic SQL usually works well for all scenarios, so don’t be afraid of performance or security penalties always you use the best of the underlying data base (see these measurements).

Designing software includes avoiding exotic elements (and thus complex and hard to understand / maintain) so if you end up writing any of the following two bad samples you should consider more options. Bad example 1: the following code is taken from the WINDOWS AZURE TABLE whitepaper (by Jai Haridas, Niranjan Nilakantan, and Brad Calder, May2009) and it shows how paging by using the LINQ Take(N) function and a continuation token (so involves maintaining it as internal state):

serviceUri = new Uri("http://<account>.table.core.windows.net");
DataServiceContext svc = new DataServiceContext(serviceUri);
var allBlogs = context.CreateQuery<Blog>("Blogs");
foreach (Blog blog in allBlogs.Take(100))
{
// do something with each blog
}

Bad sample 2: this bad sample is taken from this website and it shows you how to increase the complexity of your queries by using different approaches, including temporal tables, common table expressions, OFFSET/FETCH NEXT and the TOP clause, combined with the ROW_NUMBER() function:

DECLARE @PageIndex INT = 1, @Pagesize INT = 10
SELECT res.PageId, res.PageName
FROM(SELECT ROW_NUMBER() OVER(ORDER BY PageName,PageId) as RW,
PageId,  PageName FROM PaginationTable) res
WHERE RES.RW BETWEEN (@PageIndex - 1) * @Pagesize AND ((@PageIndex - 1) * @Pagesize) + @Pagesize

Choose whatever it works for your scenario. I particularly prefer the clean and easy MySQL syntax to achieve paging. In the next sample a dynamic query is concatenated and the trivial LIMIT clause is used (be aware that I’m not claiming that MySQL is the best RDBMS, I only said that the LIMIT clause is cleaner and easier than the alternative MS Sql approaches):

SET @query = CONCAT('SELECT FIELD1, FIELD2 FROM TABLE_NAME WHERE ', I_DYNAMIC_WHERE);
SET @query = CONCAT(@query, '  LIMIT ');
SET @query = CONCAT(@query, I_START_RECORD);
SET @query = CONCAT(@query, ' , ');
SET @query = CONCAT(@query, I_PAGE_SIZE);
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

[UPDATE 07/31/2012]: This pattern is an evolution of the Core J2EE Value List Handler pattern as well.

[UPDATE 08/11/2012]: Dynamic SQL is such as popular that you can find it in the Entity Framework source code:


internal override void ToFullString(StringBuilder builder)

{

builder.Append("SELECT ");

if (m_selectDistinct == SelectDistinct.Yes)

{

builder.Append("DISTINCT ");

}

StringUtil.ToSeparatedString(builder, m_projectedSlots, ", ", "_");

if (m_boolExprs.Count > 0)

{

builder.Append(", Bool[");

StringUtil.ToSeparatedString(builder, m_boolExprs, ", ", "_");

builder.Append("]");

}

builder.Append(" FROM ");

m_extentMemberPath.ToFullString(builder);

if (false == m_whereClause.IsTrue)

{

builder.Append(" WHERE ");

m_whereClause.ToFullString(builder);

}

}

[UPDATE 11/DEC/2013: If you want to take this to the next level you can abstract and encapsulate each contidion/filter as an object and use them from a aggregated object. This was actually called “Query Object” by Martin Fowler. BTW: I think Query Object is over engineering unless you’re implementing a query parser or UI component -e.g. a Grid-]

Cheers,

Javier Andrés Cáceres Alvis

Microsoft Most Valuable Professional – MVP
Intel Black Belt Software Developer