SQL Queries and String.Join()

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<string> _clauses = new List<string>();

    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<string> _clauses = new List<string>();

    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.

  • Brian Romanowski

    Built-in “join” is one of the things I love about Python and miss in Java. Related, I’ve found myself wanting some syntactic sugar for “first iteration” and “last iteration” in loops. I don’t know any language that has this, so it’s probably a bad idea on balance…

  • Mark

    This is a great example of a reason why I’ve always felt I wasn’t a great .Net developer. I’ve solved this exact problem a ton of times but in an embedded environment I would come up with some crafty way of doing it like you posted about. Unfortunately what that means is that now as a C# developer, I would tend to go back to those same habits. I had never seen or heard of this function used and it’s unfortunately one of thousands of classes or methods that I’ve never been exposed to which makes me feel like a perpetual beginner in the .Net world.
    Thanks for the tip. Next time I won’t implement that situation in such a naive way.

  • http://www.daedtech.com/blog Erik Dietrich

    I don’t think that’s a bad idea at all – it seems like good practice to standardize something you do a lot. As for whether doing it a lot is a bad idea, I don’t know, but I don’t see any reason that it should be. It seems like more of a matter of opinion and convention. Why should it be the convention to be uniform in a loop as opposed to having some kind of notion of loop setup and teardown, so to speak..? Anyway, I’d be curious to see the java version of this syntactic sugar (a python version probably wouldn’t mean much to me since I don’t know the language).

  • http://www.daedtech.com/blog Erik Dietrich

    Well, naive might have been a bit harsh (that was more meant as a reflection of how I felt when I discovered the method about the way I had previously done it). And, I wouldn’t let the richness of libraries in .NET or anywhere else get you down. Knowing them all in their nitty-gritty detail isn’t necessarily a badge of honor. I mean there are a lot of deprecated and goofy ones out there as well as helpful and code-saving ones. For me, the (admittedly imperfect) rule of thumb is that if I’m typing out code and find myself thinking “a lot of people have probably needed a solution to this before me”, I start to google and/or ask if there’s a better way, since some industrious soul probably thought what I’m thinking and did something about it.

    But yeah, .NET being hugely popular and hugely extensible is going to create a massive collaboration vibe that’s probably polar opposite of the embedded world.

  • Pingback: FluentSqlGenerator | DaedTech()