Make Code Robust With Flexible Queries (Continued)

The Filter class is more complex; it includes a collection of BaseFilterFragment objects, which can contain any combination of derived FilterItem objects or FilterParenFragment objects. FilterParenFragments have an additional collection of BaseFilterFragments, allowing you to nest parentheses to any depth. FilterItem objects define a comparison expression for the filter. Each FilterItem contains a column name, a table name, an operator, and a value. FilterItems can also contain the preceding conjugation. You can extend this structure to support operators with multiple values (such as BETWEEN and IN). You can also extend it to support comparisons with other columns.

Both filters and display columns allow you to select from many tables. SQL expresses the relation between tables as JOIN clauses. You don't define these joins in the approach shown here; the system infers them. You could infer the database relationships directly from the database at run time, but this would incur an unnecessary performance penalty. At least two other approaches exist. If you're using code generation, you can hard-code this information into your application. If you're not, you can use a technique based on XML extracted and saved to the user's machine, which is the approach I describe here. I used information schemas to extract this information, so it shouldn't take much to modify it for any SQL-92-compatible database (sorry, Access isn't SQL-92-compliant). I used a complete extraction tool I'd written already, and simplified the output with additional Extensible Stylesheet Language Transformations (XSLT) (see Listing 2 for a portion of the resulting structure).

Locate the Closest Relation
Determining the closest relation for each table is a graph theory problem. You can think of each table as a node with a line extending to its parent and child tables. You want the closest relation, so you need to expand the search in a concentric manner. First, look for any parent or child relations, then repeat the process for grandchildren and grandparents, and so on. If you only take each path to its conclusion, you might find a distant complex join before a simple one, and you'd be dependent on the ordering of the structure. You must determine the tables of interest, then cycle to a maximum depth through the adjacent nodes for each table (see Listing 3). The result is a structure of RelationInfo objects; again, you abstract the reusable stuff from the actual SQL generation.

You can easily avoid duplicates in the list of tables you're searching for, but a table still might appear twice in the join. For example, if you're interested in the Customers table and its Orders child and OrdersDetail grandchild, the Orders table appears twice. It appears once as the requested relation and once on the path to OrdersDetail. The simplest solution is to allow the duplicates, then remove them. Be sure to remove them in the same order you're building the JOIN clause, so tables are available in the JOIN when they're needed.

Armed with the structure defining your query, you're ready to dive into creating the T-SQL. A common base class allows you to interchange providers, perhaps based on a configuration setting. Each provider, such as your SQL provider, provides access to specific functionality. It also allows you to use the same query information to access data through completely different mechanisms, including custom caches and XML data.

The actual query creation is a string concatenation process. A divide-and-conquer approach with many discrete methods makes it easier to track down specific locations during debugging, but the concatenation is straightforward if the structure is well-built. For example, building the WHERE clause includes a loop containing code to add each portion of the clause:

With CType(filterItems(i), FilterItem)
   paramInfo = New parameterInfo(paramInfoColl.Count, .Value)
   ParamInfoColl.add(paramInfo)
   sqlString &= " [" & .Table & "].[" & .Column & "] " & _
      GetOperator(.Operator) & " @" & paramInfo.Name
End With

Use ParamInfoColl to retrieve values for the parameters of your SQLCommand object when you retrieve data.

You can extend these ideas to control the children you retrieve or extend the Filter or FilterItem objects to include Top or OrderBy properties. You might also want to limit the tables available for selection to those with legal joins to the main table, rather than throwing an exception later. On the surface, this approach seems more complex than simply concatenating a string. But by abstracting the filter and creating the joined tables dynamically, you've built a system that is more robust in the face of data structure changes. Retrieving children in the same round trip improves your performance; forcing all SELECT statements through a single location makes debugging easier; and most important, using parameterized queries protects your system against current SQL injection attacks.

About the Author
Kathleen Dollard is the author of Code Generation in Microsoft .NET (Apress) and numerous articles on .NET technologies. She's a longtime Microsoft MVP, president of the Northern Colorado .NET SIG, and active in the Denver Visual Studio User Group. You can read more about code generation at her Web site or blog . Reach her at .