The Catch Block #99 - Finishing the Dapper Where Clause Builder

Welcome to the 99th edition of The Catch Block!

In this edition, we finish up the DapperWhereClauseBuilder that we started building two weeks ago.

Plus: gotchas; real-world refactoring; unit tests for legacy systems; the code review pyramid; and .NET 7 Preview 2.

Let's go!

Finishing the Dapper Where Clause Builder

In the previous issue, we talked at length about a class called DapperWhereClauseBuilder that we could use to build complex SQL WHERE clauses. At the end of the previous post, our class looked like this:

public class DapperWhereClauseBuilder
{
    private readonly SortedDictionary<string, object> singleValues = new SortedDictionary<string, object>();

    public DapperWhereClauseBuilder AddValue(string parameterName, int? value)
    {
        if (value.HasValue && !singleValues.ContainsKey(parameterName))
            singleValues.Add(parameterName, value.Value);

        return this;
    }
    
    //Lots of overload AddValue methods, one for each primitive type, including bool, double, string, DateTime, and more.

    public DapperWhereClauseBuilder AddValue(string parameterName, Guid? value)
    {
        if (value.HasValue && !singleValues.ContainsKey(parameterName))
            singleValues.Add(parameterName, value.Value);

        return this;
    }

    public string WhereClause
    {
        get
        {
            //If no values are submitted, there is effectively no WHERE clause.
            //So, return an empty string.
            if(!singleValues.Any())
                return string.Empty;

            string whereClause = " WHERE ";
            foreach(var item in singleValues)
            {
                whereClause += item.Key + " = @" + item.Key.ToLower() + " AND ";
            }
            whereClause = whereClause.Remove(whereClause.LastIndexOf("AND"));

            return whereClause;
        }
    }

    public DynamicParameters Parameters
    {
        get
        {
            DynamicParameters parameters = new DynamicParameters();
            foreach(var item in singleValues)
            {
                parameters.Add(item.Key.ToLower(), item.Value);
            }
            return parameters;
        }
    }
}

At this point, this class can only generate WHERE clauses for exact matches on a value. We could end up with "WHERE ColumnName = @Parameter". What we cannot do yet is have more complex matching clauses, such as IS NOT NULL or IS IN. We'll be extending DapperWhereClauseBuilder to permit these kinds of clauses in this issue.

IS NOT NULL Clauses

Let's deal with IS NOT NULL clauses first. We need a new collection in DapperWhereClauseBuilder to hold the column names that we need to check for NULL on:

public class DapperWhereClauseBuilder
{
    private readonly SortedDictionary<string, object> singleValues = new SortedDictionary<string, object>();
    
    //NEW
    private readonly List<string> notNullValues = new List<string>();
    
    //...Rest of implementation
}

We also need a method by which we can add columns to be checked for NOT NULL:

public class DapperWhereClauseBuilder
{
    //...Rest of implementation
    
    public DapperWhereClauseBuilder AddNotNull(string parameterName)
    {
        if (!notNullValues.Contains(parameterName))
            notNullValues.Add(parameterName);

        return this;
    }
    
    //...Rest of implementation
}

Finally, we need to modify the WhereClause property to includ the output for IS NOT NULL clauses: