Overview
SqlBuilder is a class designed to make dynamic SQL tasks easier. The top 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 we call ToString on the first example this is what we 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 SqlBuilder.ParameterValues collection. To turn this into a command we need a System.Data.Common.DbProviderFactory instance:
var command = query.ToCommand(SqlClientFactory.Instance); Console.WriteLine(command.ToTraceString());
The output:
SELECT * FROM Products WHERE Name LIKE @p0 -- @p0: Input String (Size = 2) [A%]
The parameter placeholder is now replaced with a parameter name, and the parameter value is included in the command. To understand how this command is created you can read my post about it.
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 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);
The output:
SELECT ID, Name
FROM Products
WHERE Name LIKE {0} AND CategoryID = {1}
This is how you can dynamically construct the clause body. The ’separator’ feature is not 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 to instance-relative.
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)
.WHERE_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);
The output:
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
WHERE_If is an extension method that takes an extra boolean parameter which indicates if WHERE should be called or not. Although you can implement the same logic explicitly writing an if statement, the advantage of this method is that you keep the query as a single method-call chain, improving the readability of the code.
Sub-queries
Parameter placeholders are always used for command parameters, except when you pass another SqlBuilder instance, case in which the supplied builder’s text is injected at the placeholder. This is how SqlBuilder support 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);
The output:
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.
Arrays
Not many SQL dialects support array types, but a very common requirement is to use an array of values as the right expression of the IN operator:
int[] ids = { 1, 2, 3 };
var query = SQL
.SELECT("*")
.FROM("Products")
.WHERE("CategoryID IN ({0})", ids);
Console.WriteLine(query);
The output:
SELECT *
FROM Products
WHERE CategoryID IN ({0}, {1}, {2})
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 = SQL.New(@"
SELECT ProductID, ProductName
FROM Products")
.WHERE("CategoryID = {0}", 1);
Console.WriteLine(query);
The output:
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", true)
.WHERE("ProductID = {0}", 1)
.LIMIT(1);
var delete = SQL
.DELETE_FROM("Products")
.WHERE("ProductID = {0}", 1)
.LIMIT(1);
Console.WriteLine(insert);
Console.WriteLine(update);
Console.WriteLine(delete);
The output:
INSERT INTO Products(ProductName, UnitPrice, CategoryID)
VALUES ({0}, {1}, {2})
UPDATE Products
SET Discontinued = {0}
WHERE ProductID = {1}
LIMIT {2}
DELETE FROM Products
WHERE ProductID = {0}
LIMIT {1}
You can see here some methods that do not take the format string, like SET, 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(new SqlClauseInfo("JOIN", null), format, args);
}
public SqlBuilder SET(string columnName, TValue value) {
return AppendClause("SET", columnName + " = {0}", value);
}
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.
Here’s how the WHERE_If extension method is implemented (right now I don’t have a longer/more complex example, when I do I’ll post it here):
public static SqlBuilder WHERE_If(this SqlBuilder sqlBuilder, bool condition, string format, params object[] args) {
return (condition) ? sqlBuilder.WHERE(format, args) : sqlBuilder;
}
Mapping to objects with LINQ to SQL
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. Since SqlBuilder was inspired by LINQ to SQL’s ExecuteQuery/ExecuteCommand methods, integrating with this ORM is very easy:
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");
// instantiate your DataContext
DataContext context = null;
return context.ExecuteQuery<Product>(query.ToString(), query.ParameterValues.ToArray());
}
LINQ to SQL parses the query and maps columns to properties based on the column aliases used.
Mapping to objects with DbExtensions
SqlBuilder is part of the DbExtensions library, which includes a basic CRUD API:
public IEnumerable<Product> GetProducts() {
// Instantiate your DAO
DataAccessObject dao = null;
var query = new SqlBuilder { QueryMembers = dao.CreateQueryMembers() }
.SELECT(typeof(Product), "p")
.SELECT(typeof(Category), "c")
.SELECT(typeof(Supplier), "s")
.FROM("Products p")
.LEFT_JOIN("Categories c ON p.CategoryID = c.CategoryID")
.LEFT_JOIN("Suppliers s ON p.SupplierID = s.SupplierID")
.WHERE("p.ProductID < {0}", 7);
return dao.Map<Product>(query).ToList();
}
DbExtensions takes a different approach to mapping. First, it defines an extension method SqlBuilder.SELECT(Type, String) that takes the type of a mapped object, which appends the names of the columns based on the mapping, and adds the System.Reflection.MemberInfo instances for each mapped member to the SqlBuilder.QueryMembers collection. This way, the SqlBuilder instance not only contains the query to be executed, but also the members to which the result should map to. With this, DbExtensions is able to take a query and return mapped objects. It also detects that Category and Supplier types are related to Product with a Many-to-one association, and loads the Product.Category and Product.Supplier properties for each returned Product.
Conclusions
SqlBuilder helps your build dynamic SQL in an ADO.NET provider/RDBMS/ORM independent way. Generic query APIs like LINQ work great for simple queries, but it’s statically-typed nature tends to become a disadvantage for complex scenarios, and it’s 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. Complex queries require complete control of the executing SQL. SqlBuilder gives you that control, freeing you from dealing with low-level objects like DbCommand and DbParameter.
