Have Code, Will Travel

December 1, 2009

Dynamic Search Objects Part 6—the Search Service

Filed under: Dynamic SQL — Phil Sandler @ 3:25 pm

If you’ve read this series up to this point, you pretty much have everything you need to implement dynamic queries in your own application.  Just a few notes to wrap up the series and I will get on to other topics.

Creating a Search Service Facade

Depending on your requirements, it may be perfectly reasonable to create a tight coupling between DTO and search implementation.  In cases where the return object of the search is strongly typed (i.e. not a dataset/datatable), this actually may be a necessity.  However, there are a number of reasons why creating a simpler “catch-all” service interface might be desirable.  You may want to actually develop the parameter/DTO implementation and query implementation separately, so your UI developer can write their code before the query has been written.  You may simply want to hide the complexity of the Data Access Layer from other layers of the application.  Or your search service may actually be part of a “real” service layer (SOA). 

In any case, you may want to utilize a simple service facade for search execution that will take in any of your DTO types and determine the correct type of query to perform.  The basic idea is that the consumer using the search service only needs knowledge of the parameter/DTO object and the service interface itself.  Any coupling between the DTO and the query implementation exists only behind the service facade.  So the developer calling the service needs only to set the desired values on the DTO and call SearchService.ExecuteSearch(myDTO).

A simple, first-pass approach might look something like this:

public class SearchServiceFacade
    public SearchQueryResultV2 ExecuteSearch(SearchParamBaseV2 param)
        if (param is OrderSearchParamV4)
            var service = new OrderSearchServiceV5();
            return service.SearchOrders((OrderSearchParamV4) param);
        if (param is CustomerSearchParam)
            var service = new CustomerSearchService();
            return service.SearchCustomers();
            throw new NotSupportedException();


This looks a little ugly because I didn’t use an interface for my service (or DTO for that matter).  A more permanent implementation might use a factory to select the query, and could look something like this (consider this pseudo-code!):

public class SearchServiceFascade : IServiceFascade
    private readonly ISearchServiceFactory _serviceFactory;

    public SearchServiceFascade(ISearchServiceFactory serviceFactory)
        if (serviceFactory == null) throw new ArgumentNullException("serviceFactory");
        _serviceFactory = serviceFactory;

    public SearchQueryResultV2 ExecuteSearch(ISearchParam param)
        ISearchService service = _serviceFactory.GetService(param);
        return service.PerformSearch(param);



Since I started using this technique in ~2005, I have implemented it on at least five systems with consistent success.  The key to that success?  Basing the design of the framework on the specific needs of each project. 

  • In one of the implementations I worked on, virtually every query against the database needed to be filtered by client Id, so it made sense to create an abstract base query class, with an abstract method FilterByClientId which had to be overridden in each derived class. 
  • In another project the requirement to filter on multiple values in the same field (with different comparison operators) came late in the development cycle, and we had to scramble to update the framework to support this. 
  • In a third project, the query specification needed to be persisted to the database, so the DTO was actually a representation of a set of parameter values that could be used repeatedly to formulate the same query (this was for a reporting system).

Because this technique employs OOP concepts like encapsulation and loose coupling, responding to changing requirements is made much easier, and some measure of flexibility is built right in.

I’m not sure of the EXACT topic of my next post yet, but I have done a lot of work with an Inversion of Control (IOC) framework in the last year, and I have come to believe that the benefits of using these frameworks are huge.  So my next post(s) will share some code and/or thoughts around utilizing an IOC container.

Shout it


November 20, 2009

Dynamic Search Objects Part 5—Sorting

Filed under: Dynamic SQL — Phil Sandler @ 2:23 pm

In my last post, we added paging back to our original requirements, leaving only sorting left to implement.  So since we are covering that topic in today’s post, we can re-add it, which gets us back to all the functionality we had initially envisioned (with some minor clarifications to the original version):

Show all orders, filtering by an optional list of customers [using Company Name] and an optional list of employees who took the order and within an optional date range.  By default, the orders should be sorted by date [Ascending], then customer name [Ascending], but can be sorted by any single or pair [or any number] of columns in ascending or descending order.  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.

Like paging, sorting is built right in to the LLBLGen Dynamic List functionality.  So writing the lines of code that actually generate the ORDER BY clause is pretty straightforward.  However, figuring out how to fit a dynamically selected sort order into our framework presents a few design problems that we need to address.

Sorting is a PITA

As noted in Part 1, Dynamic SQL at least allows us to avoid one sorting challenge—using (or generating) a SQL CASE statement and casting the result to a common type.  But whether you are using static SQL (a stored procedure) or Dynamic SQL (as we are in this article), a common problem remains: providing the consumer the ability to effectively express the intended sort order without exposing the inner workings of the query.

Our query (and any query) returns a fixed set fields in the result set.  The client needs the ability to communicate to our service (via the DTO) the fields that should participate in the sort order, the rank of each field (first sort by fieldX, then by fieldY), and the operator to use for each (ASC or DESC).  So how do we express this in the DTO?  Remember that while the query service does (and must) have knowledge of the the DTO, the DTO has no knowledge of the query service, so there is no guarantee that the DTO’s fields match with the fields in the service. 

There are a number of ways we can handle this, but none of them are perfect.  Most of the time we end up simply managing sort fields by ordinal number.  So in our query, if a client wants to order the data by company name ascending, then order date descending, the code that sets values on the DTO will need to indicate that we want “field 3 ascending” then “field 1 descending” (3 and 1 are the ordinal numbers that represent the positions of company name and order date, respectively).  This convention needs to be well understood by the developer who is writing the code to add these values to the DTO, as it can get confusing when, for example, a grid in the UI puts fields in a different order than the way they come back in the result set.

Wait, Can’t We Just Sort on the Client Side?

Deferring sorting to the client side (e.g. jQuery, Dataset/Dataview sorting) may look tempting.  And it actually is a viable option for paging, sorting and filtering, if (and only if):

  1. the set of data is small, and
  2. there is little concern about performance and scalability

From my experience, the systems that meet this kind of criteria are few and far between.  Note that it’s not possible to mix and match sorting/paging in the application/database—if you ask the database for an unsorted 100 rows of data, then sort those rows on the application side, you’re likely not going to get the set of data you were expecting.  This may seem obvious, but I’ve seen the mistake made enough times that I wanted to at least mention it.

It is much more efficient to sort and filter in the database for a number of reasons.  For one thing, database engines are highly optimized for doing exactly the kind of work that sorting and filtering entail; this is what their underlying code was designed to do.  But even barring that—even assuming you could write code that could match the kind of sorting, filtering and paging performance of a mature database engine—it’s still preferable to do this work in the database, for the simple reason that it’s more efficient to limit the amount of data that is transferred from the database to the application server. 

So for example, if you have 10,000 rows before filtering, and your query pares that number down to 75, filtering on the client results in the data from all 10,000 rows being passed over the wire (and into your app server’s memory), where filtering on the database side would result in only the filtered 75 rows being moved between database and application.  This can make a huge impact on performance and scalability.

Sorting is a PITA 2: Revenge of the RDBMS

One last note about using sorting and paging in conjunction.  A query that implements paging can have odd results if the ORDER BY clause does not include a field that represents an empirical sequence in the data; sort order is not guaranteed beyond what is explicitly specified in the ORDER BY clause in most (maybe all) database engines.   An example: if you have 100 orders that all occurred on the exact same date, and you ask for the first page of this data sorted by this date, then ask for the second page of data sorted the same way, it is entirely possible that you will get some of the data duplicated across both pages.  So depending on the query and the distribution of data that is “sortable,” it can be a good practice to always include a unique field (like a primary key) as the final field in a sort clause if you are implementing paging.

Have I mentioned that sorting is a PITA?

Our Approach

Now that I’ve explained my views on Life, the Universe, and Sorting in 5000 words or less, let’s get to the ~20 lines of code that need to be added to implement sorting in our framework.

Let’s create an enum to represent sort direction (ascending or descending).  LLBLGen Pro actually provides a similar type out of the box, but for the purpose of this article, I don’t want knowledge of the LLBLGen framework exposed above the service layer (to be clear, this is not a restriction that I normally enforce):

public enum SortDirection

Next we need a class to represent the combination of (ordinal) field and the sort direction:

public class SortField
    private readonly int _fieldPosition;
    private readonly SortDirection _direction;

    public SortField(int fieldPosition, SortDirection op)
        _fieldPosition = fieldPosition;
        _direction = op;

    public SortDirection Operator
        get { return _direction; }

    public int FieldPosition
        get { return _fieldPosition; }

Since we will provide the ability to sort by multiple fields, we need a list of our SortField class on our DTO.  Since sorting will likely be supported on every implementation, we’ll add it to the base class.  I’m only showing code that changed in the base class here—grab the source code if you want to see it in context:

private readonly List<SortField> _sortFields = new List<SortField>();

public void AddSortField(SortField sort)

public List<SortField> SortFields
    get { return _sortFields; }

Our Dynamic List wrapper needs to change in a few ways.  We need to create a private method to transform our list of sort fields into a sort expression:

private SortExpression CreateSortExpression(List<SortField> sortFields)
    if (sortFields == null || sortFields.Count == 0) return null;

    var existingFields = new HashSet<int>();

    var sort = new SortExpression();
    foreach(SortField field in sortFields)
        //this prevents the same field from being added twice
        if (existingFields.Contains(field.FieldPosition)) continue;

        SortOperator op = field.Operator == SortDirection.Ascending ? SortOperator.Ascending : SortOperator.Descending;
        var sortClause = new SortClause(_fields[field.FieldPosition], null, op);

    return sort;

Now our main execute method needs to change to accept the list of sort fields, hand it to our new new method, and use the resulting sort expression in our database call:

public SearchQueryResultV2 ExecuteQuery(int pageNumber, int pageSize, List<SortField> sortFields)
    SortExpression sort = CreateSortExpression(sortFields);
    int dbCount;
    var dt = new DataTable();
    using (var adapter = new DataAccessAdapter())
        //fetch the data table
        adapter.FetchTypedList(_fields, dt, _bucket, 0, sort, false, _groupBy, pageNumber, pageSize);
        //perform an additional query to get the total count of rows that matched
        dbCount = adapter.GetDbCount(_fields, _bucket, _groupBy, false);

    return new SearchQueryResultV2(dt, dbCount, pageNumber, pageSize);

Finally, we need to implement the default sort order from our requirements if no order was specified, and call the new version of the ExecuteQuery method.  Note that we also add the PK to the sort order to ensure that our sequence is empirically maintained between pages of data (see above):

//handle default sort
if (param.SortFields.Count == 0)
    param.AddSortField(new SortField(1, SortDirection.Ascending));
    param.AddSortField(new SortField(3, SortDirection.Ascending));

//add PK to the end of the sort clause.
param.AddSortField(new SortField(0, SortDirection.Ascending));

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

That should do it for sorting, and that should be all you need to get up and running with a single implementation of a Dynamic Search Object.  In my next post (which I promise will be brief!), I’ll talk a little bit about creating a clean service interface for situations when you have many implementations.

Source Code for Part 5

Shout it

Blog at WordPress.com.