Have Code, Will Travel

November 12, 2009

Dynamic Search Objects Part 2—Dynamic List Wrapper

Filed under: Dynamic SQL — Phil Sandler @ 9:52 pm
Tags:

In my last post, I demonstrated a concrete implementation of a dynamic query, not very far removed from the basic functionality that ships with LLBLGen Pro.  In this installment, we’ll create a convenience wrapper that will generally make creating dynamic lists easier, and further facilitate rapidly moving from a set of requirements for a search to a working implementation.

The wrapper is essentially a stateful class that combines the structures that make up a Dynamic List, and provides a convenient way to put those structures together and then execute the query.

This class will be responsible for:

  • Allowing fields to be created
  • Managing the GroupBy collection via field creation
  • Allowing relations to be added
  • Allowing filters to be added
  • Allowing sort expressions to be added (future post)
  • Add paging (future post)
  • Managing the state of all of the above structures before query execution
  • Executing the query and returning a SearchQueryResult

The exact details of the wrapper should be tailored to the overall requirements of your system.  For example, if none of your search results require any kind of aggregation, the GroupBy clause functionality isn’t necessary.

Note that the responsibility of defining fields, adding relations, creating aggregates, etc. are still the responsibility of the query implementation.  The core of the pattern is to have one query implementation per parameter implementation; there is no getting around having to build classes that have specific knowledge about the fields and entities involved in the query.  Our goal is to make each implementation as simple as possible.

Version 1 of our Dynamic List Wrapper:

public class DynamicListWrapperV1
    {
        private readonly RelationPredicateBucket _bucket = new RelationPredicateBucket();
        private ResultsetFields _fields;
        private GroupByCollection _groupBy;

        public void AddRelation(IRelation relation)
        {
            _bucket.Relations.Add(relation);
        }

        public IPredicateExpression Filter
        {
            get { return _bucket.PredicateExpression; }   
        }

        public void AddField(IEntityField2 fieldToAdd, bool addToGroupBy)
        {
            AddField(fieldToAdd, addToGroupBy, fieldToAdd.Name);
        }

        public void AddField(IEntityField2 fieldToAdd, bool addToGroupBy, string fieldName)
        {
            //if this is the first field, instantiate
            if (_fields == null) _fields = new ResultsetFields(1);
            else _fields.Expand(1);
            
            _fields.DefineField(fieldToAdd, _fields.Count – 1, fieldName);
            
            if (addToGroupBy)
            {
                if (_groupBy == null) _groupBy = new GroupByCollection();
                _groupBy.Add(fieldToAdd);
            }
        }

        public SearchQueryResult ExecuteQuery()
        {
            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);
        }
    }

Our updated Order Search Service:

public class OrderSearchServiceV2
    {
        public SearchQueryResult SearchOrders(OrderSearchParamV1 param)
        {
            var listManager = new DynamicListWrapperV1();

            //create the fields.  This is roughly the equivalent of the SELECT statement
            listManager.AddField(OrdersFields.OrderId, true);
            listManager.AddField(OrdersFields.OrderDate, true);
            listManager.AddField(OrdersFields.ShippedDate, true);
            listManager.AddField(CustomersFields.CompanyName, true);
            listManager.AddField(EmployeesFields.FirstName, true);
            listManager.AddField(EmployeesFields.LastName, true);

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

            var countField = OrderDetailsFields.ProductId;
            countField.AggregateFunctionToApply = AggregateFunction.Count;
            listManager.AddField(countField, false, "OrderDetailCount");
            
            //setup relations.  This is roughly the equivalent of the FROM and JOIN statements
            listManager.AddRelation(OrdersEntity.Relations.CustomersEntityUsingCustomerId);
            listManager.AddRelation(OrdersEntity.Relations.EmployeesEntityUsingEmployeeId);
            listManager.AddRelation(OrdersEntity.Relations.OrderDetailsEntityUsingOrderId);

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

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

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

            return listManager.ExecuteQuery();
        }
    }

This is a pretty good start, and is an improvement over what we put together in Part 1.  However, we’re still dealing with a pared-down set of requirements which really don’t necessitate a dynamic query.  In my next post, we’ll add back some of these requirements, modify our parameter object to support more flexible filtering, and create a helper class to simplify creating those filters.

Source Code for Part 2

Shout it kick it on DotNetKicks.com

About these ads

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 263 other followers

%d bloggers like this: