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
- 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.
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.
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:
Result class (the return value of the service):
Now the LLBL code. Comments on what each section is doing are inline:
I know what you’re thinking: that looks like a lot of code to do something relatively simple. But remember that:
- Our “real” requirements are going to add much more complexity, and
- 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.