SqlBuilder Tutorial

Overview

SqlBuilder is a class designed to make dynamic SQL tasks easier. The main design goals are:

• The query should look like SQL, and all SQL queries should be possible.
• SqlBuilder is about building SQL, not getting or mapping data, so it should not be bound to any particular data-access implementation.

Let’s take a look at the first example:

var query = new SqlBuilder()
   .SELECT("*")
   .FROM("Products")
   .WHERE("Name LIKE {0}", "A%");

What makes SqlBuilder very easy to learn/use is that all methods have the same signature:

SqlBuilder SELECT(string, params object[]);
SqlBuilder FROM(string, params object[]);
SqlBuilder WHERE(string, params object[]);
...

The first parameter is a composite format string, as used on String.Format, and the second parameter is an array of the parameter values you want to use in your command. So, if you call ToString on the first example this is what you get:

SELECT *
FROM Products
WHERE Name LIKE {0}

Pretty much the same. The parameter placeholder is still there, and the ‘A%’ value is kept in the ParameterValues collection. To turn this into a command you use the Database class:

var db = new Database();
Console.WriteLine(db.CreateCommand(query).CommandText);

Outputs:

SELECT *
FROM Products
WHERE Name LIKE @p0

The parameter placeholder is now replaced with a parameter name, and the parameter value is included in the command.

Keeping track of the last clause

SqlBuilder keeps track of the last clause to determine when to use separators like commas (‘, ‘), logical operators (‘ AND ‘), etc, when building the clause’s body. This allows you to call the same method more than once:

// SQL.SELECT is just a shortcut to new SqlBuilder().SELECT
var query = SQL
   .SELECT("ID")
   .SELECT("Name")
   .FROM("Products")
   .WHERE("Name LIKE {0}", "A%")
   .WHERE("CategoryID = {0}", 2); 

Console.WriteLine(query);

Outputs:

SELECT ID, Name
FROM Products
WHERE Name LIKE {0} AND CategoryID = {1}

This is how you can dynamically construct the clause’s body. This separator feature does not apply for all clauses. For example, calling JOIN two times will append JOIN on both calls. Also notice we used a zero index for both parameter placeholders, but the output shows zero and one indexes. The format string must always use method-call-relative placeholders, SqlBuilder takes care of translating those into instance-relative.

To keep things DRY you can also use the _ method for clause continuation:

var query = SQL
   .SELECT("ID")
   ._("Name")
   .FROM("Products")
   .WHERE("Name LIKE {0}", "A%")
   ._("CategoryID = {0}", 2);

Dynamic SQL

So far we’ve only used SqlBuilder to dynamically construct queries that are static, meaning the resulting SQL will always be the same. Let’s look at a real dynamic query example:

void DynamicSql(int? categoryId, int? supplierId) { 

   var query = SQL
      .SELECT("ID, Name")
      .FROM("Products")
      .WHERE()
      ._If(categoryId.HasValue, "CategoryID = {0}", categoryId)
      ._If(supplierId.HasValue, "SupplierID = {0}", supplierId)
      .ORDER_BY("Name DESC"); 

   Console.WriteLine(query);
}

Let’s call this several times with different arguments:

DynamicSql(2, null);
DynamicSql(null, 3);
DynamicSql(2, 3);
DynamicSql(null, null);

Outputs:

SELECT ID, Name
FROM Products
WHERE CategoryID = {0}
ORDER BY Name DESC

SELECT ID, Name
FROM Products
WHERE SupplierID = {0}
ORDER BY Name DESC

SELECT ID, Name
FROM Products
WHERE CategoryID = {0} AND SupplierID = {1}
ORDER BY Name DESC

SELECT ID, Name
FROM Products
ORDER BY Name DESC

The WHERE method that takes no parameters only sets WHERE as the next clause, it does not append the clause, so when a clause continuation method is used, such as _If, SqlBuilder knows which clause to use.

Sub-queries

Parameter placeholders are always used for command parameters, except when you pass another SqlBuilder instance, in which case the supplied builder’s text is injected at the placeholder. This is how SqlBuilder supports sub-queries:

var query = SQL
   .SELECT("c.CategoryName, t0.TotalProducts")
   .FROM("Categories c")
   .JOIN("({0}) t0 ON c.CategoryID = t0.CategoryID", SQL
      .SELECT("CategoryID, COUNT(*) AS TotalProducts")
      .FROM("Products")
      .GROUP_BY("CategoryID"))
   .ORDER_BY("t0.TotalProducts DESC");

Console.WriteLine(query);

Outputs:

SELECT c.CategoryName, t0.TotalProducts
FROM Categories c
JOIN (
   SELECT CategoryID, COUNT(*) AS TotalProducts
   FROM Products
   GROUP BY CategoryID) t0 ON c.CategoryID = t0.CategoryID
ORDER BY t0.TotalProducts DESC

If the sub-query contains any parameter values, these are copied to the outer query. SqlBuilder doesn’t keep any reference to sub-queries, all instances are completely independent and composability is achieved by copying state from one instance to the other.

Lists

A very common requirement is to use a list of values as the right expression of the IN operator:

var query = SQL
   .SELECT("*")
   .FROM("Products")
   .WHERE("CategoryID IN ({0})", SQL.List(1, 2, 3));

Console.WriteLine(query);

Outputs:

SELECT *
FROM Products
WHERE CategoryID IN ({0}, {1}, {2})

By using the SQL.List method you can expand a collection of values into a list of parameters, instead of being treated as a single parameter.

Extending an existing query

If there’s a large portion of the query that is static, there’s no need to convert everything to method calls, just pass it to the constructor and extend it from there:

var query = new SqlBuilder(@"
    SELECT ProductID, ProductName
    FROM Products")
   .WHERE("CategoryID = {0}", 1);

Console.WriteLine(query);

Outputs:

         SELECT ProductID, ProductName
         FROM Products
WHERE CategoryID = {0}

In this case I favored source code readability over SQL readability.

Inserts, Updates, Deletes

There’s really no limit to the kind of statements SqlBuilder can handle:

var insert = SQL
   .INSERT_INTO("Products(ProductName, UnitPrice, CategoryID)")
   .VALUES("Chai", 15.56, 5);

var update = SQL
   .UPDATE("Products")
   .SET("Discontinued = {0}", true)
   .WHERE("ProductID = {0}", 1);

var delete = SQL
   .DELETE_FROM("Products")
   .WHERE("ProductID = {0}", 1)
   .LIMIT(1);

Console.WriteLine(insert);
Console.WriteLine(update);
Console.WriteLine(delete);

Outputs:

INSERT INTO Products(ProductName, UnitPrice, CategoryID)
VALUES ({0}, {1}, {2})

UPDATE Products
SET Discontinued = {0}
WHERE ProductID = {1}

DELETE FROM Products
WHERE ProductID = {0}
LIMIT 1

You can see here some methods that do not take the format string, like VALUES and LIMIT. Since the format of these clauses is well known, all you need to pass in are parameters.

Extensibility

To understand how SqlBuilder can be extended let’s take a look at how some of the built-in clauses are implemented:

public SqlBuilder SELECT(string format, params object[] args) {
   return AppendClause("SELECT", ", ", format, args);
}

public SqlBuilder FROM(string format, params object[] args) {
   return AppendClause("FROM", ", ", format, args);
}

public SqlBuilder JOIN(string format, params object[] args) {
   return AppendClause("JOIN", null, format, args);
}

As you can see implementing a clause can be as easy as writing one line of code, it all depends on the parameters you define and how much you need to analyze them to produce a format string. Alternatively, you can access the underlying StringBuilder directly to append text, through the Buffer property.

Querying with Entity Framework (DbContext)

As stated in the design goals, getting and mapping data is beyond the scope of SqlBuilder, so to get data we need a data access component. SqlBuilder was inspired by LINQ to SQL’s ExecuteQuery and ExecuteCommand methods. Entity Framework provides the same functionality with the SqlQuery and ExecuteSqlCommand methods:

readonly NorthwindContext context = new NorthwindContext();

public IEnumerable<Product> GetProducts(int? categoryId) {

   var query = SQL
      .SELECT("ProductID, ProductName, UnitPrice")
      .FROM("Products")
      .WHERE()
      ._If(categoryId.HasValue, "CategoryID = {0}", categoryId)
      .ORDER_BY("ProductName");

   return this.context.Database.SqlQuery<Product>(query.ToString(), query.ParameterValues.ToArray());
}

Entity Framework maps columns to properties based on the column aliases used.

Querying with DbExtensions

SqlBuilder is part of the DbExtensions library, which also supports mapping based on column aliases, see Query Mapping.

Conclusions

SqlBuilder helps your build dynamic SQL in a database/ORM independent way. A generic query API like LINQ works great for simple queries, but its statically-typed nature tends to become a disadvantage for complex scenarios, and is very difficult to extend. Many ORM products have their own query APIs, but using them means marrying to a particular product and more APIs to learn. To optimize queries you require complete control of the executing SQL. SqlBuilder gives you that control, freeing you from dealing with low-level objects like DbCommand and DbParameter.