Building SQL Queries

Aaron Sherber edited this page Oct 23, 2018 · 1 revision

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

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());
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.
Press h to open a hovercard with more details.