SQL builder

There are two requirements of the project that tend to counteract each other:

• The query language should expose all the expressive power of SQL, down to the level of DBMS-specific syntax where necessary
• The exact query executed should be able to be modified away from the original site where it is defined.

Using pure SQL doesn’t support this, since SQL appears to the library as an amorphous string. Although it can be parsed back into structured form in order to be modified, it isn’t easy to do so and it’s hard to have full support for third-party extensions to SQL. Therefore the LINQ wrapper library makes use of a simple SQL builder module that holds the query in a semi-structured form until right before the query is executed.

Using the SQL builder

Most of the SQL builder methods are analogous to clauses in the SQL query. Unlike SQL, these methods can be called in any order (although in the case of WHERE clause additions, this may change the meaning if you use a non-commutative combination of AND and OR)

The SELECT clause

Adding lines to the SELECT clause is simple:

SQLBuilder builder = new MySQLBuilder();

builder.AddSelectClause("employees.id AS employee_id");
builder.AddSelectClause("employees.name AS employee_name");

In practice, this gets cumbersome and is unnecessary. Since the Object to table mapping layer knows which fields must be in the result set in order to instantiate the class, you can simply add all fields for the class to the SELECT with a single call:

builder.AddSelectTypeClause("employees", typeof(Employee));

The first argument is the name of the table that will be used, the second is the type whose members should be included in the result set.

The FROM and JOIN clauses

The builder follows SQL structure quite closely (perhaps too closely) in allowing you to specify one table as the FROM clause, and an arbitrary number of further tables as JOIN clauses:

builder.AddFromClause("employees");
builder.AddJoinClause("JOIN", "departments", "departments.id = employees.department_id");

This will expand to:

FROM employees
JOIN departments ON departments.id = employees.department_id

Note that the JOIN keyword is just a string: if your DBMS supports non-standard join types, you can specify the appropriate keyword here, provided that the lexical structure is the same. You can also specify LEFT JOIN.

The WHERE clause

Any expression can be added into the WHERE clause by specifying the expression as a string. Each expression also takes a LINQ expression type, which should be ExpressionType.And or ExpressionType.Or in order to specify whether the new expression should be logically ANDed with any existing expression or logically ORed.

sqlBuilder.AddWhereClause("employees.name LIKE @name", ExpressionType.And);
sqlBuilder.AddWhereClause("departments.name = @departmentName", ExpressionType.And);