New methods on SqlBuilder

Some handy new methods have been added to SqlBuilder.

They implement the standard SQL comparison operators for =, <>, >, >=, < and <=, together with IS NULL, IS NOT NULL, IN and NOT IN.

Why are these useful? After all, MicroLite allows you to write any clause you need in the form .Where("LastName = @p0, "Smith").

But there’s the catch: "@p0" is specific to only certain database engines, such as MsSql. The clauses will all need to change to support other platforms.

This is where the new methods come in. They respond to the flavour of SQL you are using, as defined when you created your session factory e.g.

var sessionFactory = Configure
    .Fluently()
    .ForConnection("TestDB", "MicroLite.Dialect.MsSqlDialect")
    .CreateSessionFactory();

So when you write…

var sqlQuery = SqlBuilder
    .Select("FirstName")
    .From("Contacts")
    .Where("LastName").IsEqualTo("Smith");

MicroLite helpfully generates…

"SELECT FirstName FROM Contacts WHERE LastName = @p0", "Smith"

It’s important to remember that if you do embed your own conditional clauses e.g. .Where("LastName = @p0, "Smith"), you continue to be responsible for amending the syntax to suit the flavour of SQL in use.

If you wish to engage with a different database part way through your application, it’s possible to amend SqlCharacters on the fly…

SqlBuilder.SqlCharacters = SqlCharacters.PostgreSql;

var sqlQuery = SqlBuilder
    .Select("FirstName")
    .From("Contacts")
    .Where("LastName").IsEqualTo("Smith");

The new methods are…

.Where("MyColumn").IsEqualTo("a")
.Where("MyColumn").IsNotEqualTo("a")
.Where("MyColumn").IsGreaterThan(200)
.Where("MyColumn").IsGreaterThanOrEqualTo(new DateTime(2010, 1, 1))
.Where("MyColumn").IsLessThan(50)
.Where("MyColumn").IsLessThanOrEqualTo(1000)
.Where("MyColumn").IsLike("Fred%")
.Where("MyColumn").IsNull()
.Where("MyColumn").IsNotNull()
.Where("MyColumn").In("a", "b", "c")
.Where("MyColumn").In(new SqlQuery("SELECT MyValue FROM MyTable"))
.Where("MyColumn").NotIn("a", "b", "c")
.Where("MyColumn").NotIn(new SqlQuery("SELECT MyValue FROM MyTable"))

And a most useful feature: these methods will chain, so you can write…

var sqlQuery = SqlBuilder
    .Select("FirstName")
    .From("Contacts")
    .Where("LastName").IsEqualTo("Smith")
    .AndWhere("FirstName").In("Jane", "John");
Advertisements

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