Introducing the SqlBuilder

To make it easier to create an SqlQuery, you can use the MicroLite.Query.SqlBuilder class.

The SqlBuilder uses a fluent interface to help you create an SqlQuery.

A basic example:

var query = SqlBuilder.Select("CustomerId", "Name", "DoB", "StatusId")
    .From("Customers")
    .ToSqlQuery();

A parameterised example:

var query = SqlBuilder.Select("CustomerId", "Name", "DoB", "StatusId")
    .From("Customers")
    .Where("DoB > @p0", new DateTime(1980, 1, 1))
    .ToSqlQuery();

A slightly more complex example:

var query = SqlBuilder.Select("CustomerId", "Name", "DoB", "StatusId")
    .From("Customers")
    .Where("Name LIKE @p0", "%John Smith%")
    .AndWhere("StatusId = @p0", CustomerStatus.Active)
    .OrderByAscending("DoB")
    .ToSqlQuery();

There is also a convenience method to pre specify all columns and the table name, it shortcuts the Select() and From() methods so you can add where and order by statements after.

var query = SqlBuilder.SelectFrom(typeof(Customer)).ToSqlQuery();

var query = SqlBuilder.SelectFrom(typeof(Customer)).Where("DoB > @p0", new DateTime(1980, 1, 1)).ToSqlQuery();

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

  • Although we have gone through the fluent api in a single chain, we could pass the result of Select().From().Where() to other methods to conditionally append values.
  • 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 @pX where X 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 customerName = !string.IsNullOrEmpty(customerNameTextBox.Text) ? customerNameTextBox.Text : null;
    
    .Where("Name LIKE @p0 OR @p0 IS NULL", customerName)
    

.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("Name = @p0", "Fred")
.AndWhere("StatusId = @p0", CustomerStatus.Active)
.OrWhere("StatusId = @p0", CustomerStatus.Disabled)

would produce the following SQL:

WHERE (Name = @p0) AND (StatusId = @p1) OR (StatusId = @p2)

If you wanted to produce

WHERE (Name = @p0) AND (StatusId = @p1 OR StatusId = @p2)

you would need to construct the query as follows:

.Where("Name = @p0", "Fred")
.AndWhere("StatusId = @p0 OR StatusId = @p1", CustomerStatus.Active, CustomerStatus.Disabled)

This article was written for version 1.2.2 of the MicroLite framework.

Advertisements

2 thoughts on “Introducing the SqlBuilder

  1. Pingback: MicroLite 2.0 Enhancements | MicroLite ORM

  2. Pingback: SqlBuilder Updates | MicroLite ORM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s