Building SQL Queries

To help with building up SQL queries, PetaPoco comes with a fluent SQL builder that can handle many basic tasks. The documentation for this is currently on the Old Documentation page.

Here's a simple example of using the SQL builder:

var category = "foo";
var sql = Sql.Builder.Where("CATEGORY = @0", category)
    .Where("DATE_CREATED < @0", DateTime.Now);

// Assumes EnableAutoSelect is true
var records = db.Query<MyClass>(sql);

Third Party SQL Builders

This SQL builder has always been "really basic" (to quote the docs). If you are looking for a more powerful query builder, there's a nice-looking package called SqlKata. Another package called PetaPoco.SqlKata provides some extension methods that simplify using SqlKata in conjunction with PetaPoco.

The above example in SqlKata looks very much the same, but SqlKata will let you construct much more complex queries.

var category = "foo";
var query = new Query().GenerateSelect<MyClass>()
    .Where("CATEGORY", category)
    .Where("DATE_CREATED", "<", DateTime.Now);

var records = db.Query<MyClass>(query.ToSql());