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);

And here's a more complex example with both fluent and non-fluent usage, joins, aliases and optional clauses:

var sql = PetaPoco.Sql.Builder.Select("Table1.*",
        "Table3.T3Col1",
        "Table3.T3Col2 as SomeAlias",
        "(select sum(Table4.Quantity) from Table4 where Table4.Key1 = Table3.FKeyA and Table4.Key2 = Table3.FKeyB) as SomeAlias2",
        "Table2.T2Col1",
        "Table2.T2Col2 as SomeAlias3")
        .From("Table1")
        .InnerJoin("Table2").On("Table2.ContractID = Table1.ContractID and Table2.Deleted='N'")
        .LeftJoin("Table3").On("Table1.PartCode = Table3.PartCode");

if (!string.IsNullOrEmpty(FilterVal))
{
    sql = sql.Where("UPPER(T3Col1) = @0", FilterVal);
}

sql = sql.Where("UPPER(T3Col2) = @0", AreaCode);    //Creates a WHERE or AND clause

sql = sql.OrderBy("StartDate");

var records = await db.FetchAsync<SomeModel>(sql);

As you can see, the column selection can be a wildcard, a list of columns (with or without aliases) or the result of inline functions.

The POCO SomeModel should reflect the columns returned by the query (using the column aliases where appropriate.)

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());