Have Code, Will Travel

November 10, 2009

Dynamic Search Objects Part 1–Introduction

Filed under: Dynamic SQL,Uncategorized — Phil Sandler @ 8:51 pm
Tags: , ,

A problem that is common to almost any database application is the need to search the database for specific information, based on a set of criteria provided either by a user/UI or another process.  When the requirements are simple, for example:

Show all orders for a specific customer that occurred during a specific period of time.

the problem is easily solvable using a static query/stored procedure:

SELECT * FROM orders WHERE CustomerID = @customer_id AND OrderDate Between @start_date AND @end_date

However, the problem becomes much more complex when the requirements begin to include things like:
  • Multiple criteria for a specific field
  • Optional criteria
  • Optional sorting, especially with reversible field sorting (ascending and descending) and the ability to sort by multiple fields
  • Paging (especially in conjunction with sorting, since paging REQUIRES server-side sorting)

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.  By default, the orders should be sorted by date, then customer name, but can be sorted by any single or pair 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.

There ARE ways of accomplishing all of this with static SQL, but the end result is a massively complex query that is not easily maintainable.  The sorting alone is a mess, requiring a SQL CASE statement that needs the result of each case to be cast to the same type (see Dynamic ORDER BY Statements in Stored Procedures).  Using multiple criteria per field requires passing an array of values as a single parameter, which then needs to be split back into separate values and integrated into the query (see Arrays and Lists in SQL Server 2005 for what this looks like in SQL Server).  Optional criteria requires testing the parameter against NULL as part of the WHERE clause, e.g. "WHERE CustomerID = @customer_id  OR @customer_id IS NULL."

Thankfully, with the growing acceptance of Object Relational Mapping (ORM) and the Dynamic Query Engines (DQE) that they provide, creating powerful, flexible queries that support highly complex criteria has become much easier.  In this series of articles, I will demonstrate harnessing the power of a DQE to meet the above requirements (and beyond?) in a type-safe and highly maintainable way.

Implementation Details

I will be using LLBLGen Pro (v2.6) as the ORM/DQE. More specifically, I will be using the Dynamic List feature of LLBL, which results in vanilla DataTables/DataSets as a query result.  Although DataSets (vanilla or typed) are not a good substitute for real business objects, I find them extremely useful for representing lists of data, particularly when the data includes columns from multiple database entities.  A flat list of data is frequently convenient for iteration when data binding (in Web/WinForms) or generating HTML (in MVC).

While this article will focus on creating a set of tools to be used in conjunction with LLBLGen and DataSets, the overall intent is to demonstrate the concept of harnessing any DQE.  This same pattern is equally applicable to using full-featured business/domain objects, and should be possible using any mature ORM framework, including NHibernate, Entity Framework, or Linq To SQL.  I am choosing LLBLGen as a DQE because it is the one I am most familiar with, and I find its predicate syntax to be very intuitive and expressive.

I will be using SQL 2005 as a database engine, and the Northwinds database as a target schema.  You won’t need a full version of LLBLGen to tinker with the sample code, as I will provide the LLBL-generated code, which is freely distributable.

Solution Overview

The basic concept is really quite simple.  Create a class (essentially a DTO) that represents all the possible criteria the client requires.  Then create a corresponding class in the Data Access Layer (DAL) that can translate the values from the DTO into a dynamic query.

The client only needs to have knowledge of the DTO, the service, and the type that is returned by the service (Business Object, DataSet, List, etc.).  In the case of my example, some knowledge of what will be provided in the DataSet (column names, for example) will be required as well, since DataSet columns are not typed.

That’s really all there is to it.  As simple as it sounds, I have found it to be a very effective way to quickly create powerful searches that perform well and are easy to maintain.

A Super-Basic Example

Before getting too deep into creating abstractions, wrappers, and helper classes for our framework, let’s start with a concrete example of how this is going to work.  Let’s temporarily pare the requirements down to:

Show all orders, optionally filtered by any combination of: a customer, occurring after start date, occurring before an end date.

This is intended as an illustration only—if the requirements were really this simple, a more straightforward approach, like a single method that takes in three parameters, would probably be a more appropriate solution.

DTO class for representing all the possible criteria:

public class OrderSearchParamV1
{
    public string CustomerId { get; set; }
    public DateTime? StartDate { get; set;}
    public DateTime? EndDate { get; set; }
}

Result class (the return value of the service):

public class SearchQueryResult
{
    public int TotalRows { get; private set; }
    public DataTable Data { get; private set; }

    public SearchQueryResult(DataTable data, int totalRows)
    {
        if (data == null) throw new ArgumentNullException("data");
        Data = data;
        TotalRows = totalRows;
    }
}

Now the LLBL code.  Comments on what each section is doing are inline:

public class OrderSearchServiceV1
    {
        public SearchQueryResult SearchOrders(OrderSearchParamV1 param)
        {
            //create the fields.  This is roughly the equivalent of the SELECT statement
            var fields = new ResultsetFields(6);
            fields[0] = OrdersFields.OrderId;
            fields[1] = OrdersFields.OrderDate;
            fields[2] = OrdersFields.ShippedDate;
            fields[3] = CustomersFields.CompanyName;
            fields[4] = new EntityField2("EmployeeName", EmployeesFields.FirstName +
                " " + EmployeesFields.LastName);
            fields.DefineField(OrderDetailsFields.ProductId, 5, "OrderDetailCount", AggregateFunction.Count);

            //Setup a GROUP BY clause for non-aggregate fields
            var groupBy = new GroupByCollection();
            groupBy.Add(fields[0]);
            groupBy.Add(fields[1]);
            groupBy.Add(fields[2]);
            groupBy.Add(fields[3]);
            groupBy.Add(fields[4]);
            groupBy.Add(EmployeesFields.FirstName);
            groupBy.Add(EmployeesFields.LastName);
            
            //setup relations.  This is roughly the equivalent of the FROM and JOIN statements
            var bucket = new RelationPredicateBucket();
            bucket.Relations.Add(OrdersEntity.Relations.CustomersEntityUsingCustomerId);
            bucket.Relations.Add(OrdersEntity.Relations.EmployeesEntityUsingEmployeeId);
            bucket.Relations.Add(OrdersEntity.Relations.OrderDetailsEntityUsingOrderId);

            //filter by customerId if it was provided
            if (param.CustomerId != null)
            {
                bucket.PredicateExpression.Add(OrdersFields.CustomerId == param.CustomerId);
            }

            //filter by StartDate if it was provided
            if (param.StartDate.HasValue)
            {
                bucket.PredicateExpression.Add(OrdersFields.OrderDate >= param.StartDate);
            }

            //filter by EndDate if it was provided
            if (param.EndDate.HasValue)
            {
                bucket.PredicateExpression.Add(OrdersFields.OrderDate <= param.EndDate);
            }

            int dbCount;
            var dt = new DataTable();
            using (var adapter = new DataAccessAdapter())
            {
                //fetch the data table
                adapter.FetchTypedList(fields, dt, bucket, 0, null, false, groupBy);
                //perform an additional query to get the total count of rows that matched
                dbCount = adapter.GetDbCount(fields, bucket, groupBy, false);
            }

            return new SearchQueryResult(dt, dbCount);
        }
    }

I know what you’re thinking: that looks like a lot of code to do something relatively simple.  But remember that:

  1. Our “real” requirements are going to add much more complexity, and
  2. we’re going to create a more refined toolset to make much of this code reusable.

In my next post, I will create the first draft of our “Dynamic List Wrapper”, which will make creating fields, relations, and aggregations easier, and will centralize the code that actually makes the calls to the database.

Source Code for Part 1

Shout it

Blog at WordPress.com.