SQL Builder

SqlBuilder

MicroLite includes a SqlBuilder class which can be used to easily build up a query conditionally. To use the SqlBuilder, add a using statement for the namespace it lives in using MicroLite.Builder;

You can then write queries as follows:

var query = SqlBuilder
    .Select("Id", "Forename", "Surname", "DateOfBirth", "CustomerStatusId")
    .From("Customers")
    .ToSqlQuery();

A parametrised example:

var query = SqlBuilder
    .Select("Id", "Forename", "Surname", "DateOfBirth", "CustomerStatusId")
    .From("Customers")
    .Where("DateOfBirth").IsGreaterThan(new DateTime(1980, 1, 1))
    .ToSqlQuery();

A slightly more complex example:

var query = SqlBuilder
    .Select("Id", "Forename", "Surname", "DateOfBirth", "CustomerStatusId")
    .From("Customers")
    .Where("Surname").IsLike("Smith%")
    .AndWhere("CustomerStatusId").IsEqualTo(CustomerStatus.Current)
    .OrderByAscending("DateOfBirth")
    .ToSqlQuery();

If you specify "*" as the select list and specify a type, that instructs MicroLite to create a query to select all mapped columns:

var query = SqlBuilder
    .Select("*")
    .From(typeof(Customer))
    .ToSqlQuery();

var query = SqlBuilder
    .Select("*")
    .From(typeof(Customer))
    .Where("DateOfBirth").IsGreaterThan(new DateTime(1980, 1, 1))
    .ToSqlQuery();

There are a few things to note and understand about the SqlBuilder.

• The fluent API will return the valid options after each method call, when you have built up the query, call .ToSqlQuery() to return the query as an SqlQuery.

• The parameters are named @pn where n is the zero indexed position of the argument value in the argument parameters.

• Each time we add to the where clause, we start at zero again.

• We can refer to the same argument value multiple times

  var customerSurname = !string.IsNullOrEmpty(customerSurnameTextBox.Text) ? customerSurnameTextBox.Text : null;

  .Where("Surname LIKE @p0 OR @p0 IS NULL", customerSurname)

• .AndWhere() will AND the criteria, there is also an .OrWhere() which will OR the criteria however it is important to understand that these are all appended.

So the following query:

.Where("Forename").IsEqualTo("Fred")
.AndWhere("CustomerStatusId").IsEqualTo(CustomerStatus.Current)
.OrWhere("CustomerStatusId").IsEqualTo(CustomerStatus.Closed)

would produce the following SQL:

WHERE (Forename = @p0) AND (CustomerStatusId = @p1) OR (CustomerStatusId = @p2)

If you wanted to produce

WHERE (Forename = @p0) AND (CustomerStatusId = @p1 OR CustomerStatusId = @p2)

you would need to construct the query as follows:

.Where("Forename").IsEqualTo("Fred")
.AndWhere("CustomerStatusId = @p0 OR CustomerStatusId = @p1", CustomerStatus.Current, CustomerStatus.Closed)

alternatively you could use the In function which accepts a set of params or another SqlQuery:

.Where("Forename").IsEqualTo("Fred")
.AndWhere("CustomerStatusId").In(CustomerStatus.Current, CustomerStatus.Closed)

NOTE: In all queries the SQL statement should always refer to the column names in the table, not the property names if they do not match.