DaedTech

Stories about Software

Oct
16

By

FluentSqlGenerator

Category: .NET Tags: , , |

A while back, I made a post about using string.Join() to construct SQL where clauses from collections of individual clauses. In that post, I alluded to playing with a “more sophisticated” where clause builder. I did just that here and there and decided to post the results to github. You can find at here if you want to check it out.

My implementation makes use of the Composite design pattern and the idea of well formed formula semantics in formal systems such as propositional and first order logic. The latter probably sounds a little stuffy and exposes my inner math geek, but that’s just a rigorous way of expressing the concept of building a statement from literals and basic operations on those literals. To pull it back yet another level of stuffiness, consider simple arithmetic, in which all of the following are valid expressions: “6”, “12 + 6”, “12 + (9 – 3)”. The first expression is an atomic literal and the second expression a binary operation. The third expression is interesting in that it shows that functions can have arguments that are literals or other expressions (if this still seems strange, think of these examples as “6”, “Add(12, 6)” and “Add(12, Subtract(9, 3))”.

Think of how this applies to the propositional semantics that make up SQL query where clauses. I can have “Column1 = 12” or I can have “Column1 = 12 AND Column2 = 13” or I can have “Column1 = 12 AND (Column2 = 13 OR Column3 = 4)”. When I want to model this concept in an object oriented sense, I need to represent the operators “AND” and “OR” as objects with two properties: left expression and right expression. I also need it to be possible that either of these properties is a “literal” of the form “col = val” or that it could be another expression as with the last example. Composite is thus a natural fit when you consider that these clauses are really expression trees, in a very real sense. So there is a “Component” base that’s abstract and then “Clause” and “Operation” objects that inherit from them and are fungible when constructing expressions.

This was the core of the implementation, but I also dressed it up a bit with some extension methods to support a discoverable, fluent interface, but optionally (I’m still very leery of this construct, but this seems like an appropriate and judicious use). Another nice feature, in my opinion is that it supports generic parameters so you don’t have massive overloads — you can set your columns equal to objects, strings, decimals, ints, etc. It makes heavy use of ToString() with these generic parameters, so use any type you please so long as what you want out of it is well represented by ToString().

A sample API is as follows:

var clause = Column.Named("Column1").IsEqualTo(123);
Console.WriteLine(clause);

clause = Column.Named("Column1").IsEqualTo(123).And(Column.Named("Column2").IsEqualTo("123456"));
Console.WriteLine(clause);

clause = Column.Named("Column1").IsOneOf(1, 2, 3).Or(Column.Named("Column2").IsGreaterThan(12));
Console.WriteLine(clause);

clause = Are.AnyOfTheseTrue(Column.Named("Column1").IsEqualTo(832), Column.Named("Column2").IsLessThan(25.30));
Console.WriteLine(clause);

clause = Are.AreAllOfTheseTrue(Column.Named("Column1").IsEqualTo(832), Column.Named("Column2").IsLessThan(25.30), Column.Named("Column3").IsOneOf("Current", "Valid"));
Console.WriteLine(clause);

Console.ReadLine();

Currently supported SQL operations include various comparison (equal, not equal, greater, less, etc) as well as “like” and “in()”. Expression operators “AND”, “OR” and “NOT” are supported. The utility is well covered by unit tests and a handful of integration tests too if you want to poke around but preserve functionality.

Feel free to download, use, fork, enhance, make fun of, etc, whatever. I’m not pretending this is a problem never before solved nor that this is the most elegant solution imaginable, but it was fun to write, code-kata style, and if someone can get some use out of it, great. If I wind up making significant modifications to it or extending it, I’ll post updates here as well as checking changes into github.

Sep
12

By

SQL Queries and String.Join()

Category: .NET Tags: , | 7 Comments

Over the last few weeks, I’ve found myself working within a framework where I’m writing a lot of SQL. Most specifically, in the code I’m writing a lot of WHERE clauses related to optional user search parameters. As a simple example, consider a search over “customer” where a user could filter by a part of a customer name or by a selectable customer type or simply list all customers. This creates a situation where I can have a where clause with 0, 1, or 2 entries in it depending on what the user wants to do.

The consequence of this that my where clause may be blank, it may have a clause or it may have two clauses with an AND joining them. The most basic (naive) way to handle this is to check for each control/clause whether the user has entered something and if so, to append “{Clause} AND ” to a string builder. Then, you snip off the last 5 characters to take care of the spurious “AND” that got appended. I think we’ve all seen this sort of thing before in some form or another.

But then, I got to thinking a bit, and realized that the problem I was facing here was really that I would have n clauses and would want n – 1 ANDs (except the special case of zero, where I would want zero ANDs). A clause is just a string and the ” AND ” is essentially a delimiter, so this is really a problem of having a collection of strings and a delimiter and wanting to jam them together with it. What I want is the opposite of String.Split().

And, as it turns out, the opposite of Split() is a static method on the string class called String.Join(), which takes an array of strings and a delimiter and does exactly what I need. In this fashion I can add clauses to an object as strings and then query the object for a well-formed WHERE clause. In its simplest incarnation, it would look like this:

public class WhereBuilder
{
    private readonly List _clauses = new List();

    public void Add(string clause)
    {
        _clauses.Add(clause);
    }

    public string GetFullWhereText()
    {
        return String.Join(" AND ", _clauses.ToArray());
    }
}

You keep track of your various sub-clauses of the where in a list, and then join them together on the fly, when requested by consumer code. If you wanted to allow OR instead of AND, that’s pretty simple to support simultaneously:

public class WhereBuilder
{
    private readonly List _clauses = new List();

    public void Add(string clause)
    {
        _clauses.Add(clause);
    }

    public string GetConjunctionClause()
    {
        return Join(" AND ");
    }

    public string GetDisjunctionClause()
    {
        return Join(" OR ");
    }

    private string Join(string separator)
    {
        return String.Join(separator, _clauses.ToArray());
    }
}

Of course, this is handy for constructing clauses that all have the same operator only, and it doesn’t do anything about getting rid of the annoyance of monotonously specifying operators in side of the various clauses, but my purpose here was to highlight the handiness of String.Join() for those who hadn’t seen it before.

Stay tuned if you’re interested in a more sophisticated where clause builder — I’ve been playing with that a bit in my spare time and will post back here with it if it gets interesting.