Have Code, Will Travel

November 17, 2009

Dynamic Search Objects Part 4—Paging Through Records

Filed under: Dynamic SQL — Phil Sandler @ 4:54 pm

In my last post, we revised our requirements to read:

Show all orders, filtering by an optional list of customers and an optional list of employees who took the order and within an optional date range.

While this took us a step closer to our original requirements (see Part 1), we are still missing a few features–paging and sorting.  We’ll cover paging in this installment, and save sorting for next time.  So for now our requirements can be amended to add:

The results should show a page of x records at a time, where x can be selected by the client.  The client should also be able to request and receive a specific page of data by entering the desired page number.

Our framework is based on LLBLGen Pro’s powerful dynamic list functionality, which already supports paging, so adding this flexibility is very straightforward.  We already have a class that encapsulates our search criteria (the parameter or DTO class), so all we really need to is modify it with a few additional fields, then use those fields at the appropriate time in our query service.

Violating YAGNI

I’m going to violate YAGNI slightly by creating a base class for our DTO which will store and expose paging criteria.  Why?  Because I know for a fact that I’m going to be writing more than one of these dynamic searches, and I’d rather not re-create the same functionality in each implementation.

The only change to our DTO/parameter class (not shown) is that it now inherits from a base class:

public abstract class SearchParamBaseV1
        private int _pageNumber = 0;
        private int _pageSize = 0;
        public void SetPaging(int pageSize, int pageNumber)
            _pageNumber = pageNumber;
            _pageSize = pageSize;

        public int PageNumber
            get { return _pageNumber; }

        public int PageSize
            get { return _pageSize; }

Our DynamicListWrapper class only needed two changes: the ExcecuteQuery signature and the first database call.  The changed lines:

public SearchQueryResultV2 ExecuteQuery(int pageNumber, int pageSize)

adapter.FetchTypedList(_fields, dt, _bucket, 0, null, false, _groupBy, pageNumber, pageSize);

Our search Service class didn’t need to change much–the call to the execute method now passes the paging information from the DTO object:

return listManager.ExecuteQuery(param.PageNumber, param.PageSize);

I also fixed a bug in the service class that I came across when I implemented paging (glad I decided to make writing tests part of my process!).  I found an explanation of why specifying the type is necessary here.  The code in question before:

listManager.AddField(new EntityField2("EmployeeName",
                                      EmployeesFields.FirstName + " " +
                                      EmployeesFields.LastName), false);

. . . and after:

listManager.AddField(new EntityField2("EmployeeName",
                                      EmployeesFields.FirstName + " " +
                                      EmployeesFields.LastName, typeof(string)), false);

I’m also going to modify the SearchQueryResult class to include the paging information.  This is useful in scenarios where the consumer of the result (e.g.the view in MVC) needs this information but has no knowledge of the the DTO or its values.

public class SearchQueryResultV2
        public int TotalRows { get; private set; }
        public DataTable Data { get; private set; }
        public int PageNumber { get; private set; }
        public int PageSize { get; private set; }

        public SearchQueryResultV2(DataTable data, int totalRows, int pageNumber, int pageSize)
            if (data == null) throw new ArgumentNullException("data");
            Data = data;
            TotalRows = totalRows;
            PageSize = pageSize;
            PageNumber = pageNumber;

In my next post, we’ll address the dreaded topic of sorting our results (cue foreboding music).

Source Code for Part 4

Shout it


Leave a Comment »

No comments yet.

RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: