Querying

This is a brief introduction to the various types of query PetaPoco can perform.

Query and Fetch

The Database class has two methods for retrieving records: Query and Fetch. These are pretty much identical except Fetch returns a List<> of POCO's whereas Query uses yield return to iterate over the results without loading the whole set into memory.

var inmemorylist = db.Fetch<article>("SELECT * FROM Articles")
foreach (var a in inmemorylist)
{
    Console.WriteLine($"{a.Id} - {a.Title}");
}
Console.WriteLine($"Count: {inmemorylist.Count}");

foreach (var a in db.Query<article>("SELECT * FROM Articles"))
{
    Console.WriteLine($"{a.Id} - {a.Title}");
}

LINQ Style Queries

PetaPoco supports some LINQ inspired query methods: Exists, Single, SingleOrDefault, First and FirstOrDefault (along with their Async counterparts). These methods follow this form:

var person = db.FirstOrDefault<Person>("SELECT * From Person WHERE Id = @0", 12); // Returns a Person or NULL

See Querying LINQ Style for detailed usage.

Building SQL

PetaPoco can derive the table name, or you can decorate your POCO with mapping attributes. As such, a simple SELECT can start at the WHERE clause:

var person = db.FirstOrDefault<Person>("WHERE Id = @0", 12); // Returns a Person or NULL

A simple SQL Builder is available:

var a = db.Query<article>(PetaPoco.Sql.Builder
    .Append("SELECT * FROM articles")
    .Append("ORDER BY article_id")
)

And there are methods for the common SQL clauses:

var sql = PetaPoco.Sql.Builder()
            .Select("*")
            .From("articles")
            .Where("date_created < @0", DateTime.UtcNow)
            .OrderBy("date_created DESC");

These examples are based on querying a single table. You can return the results of a join of multiple tables into a type modelled on the specific query being performed. There is also some support for more structured Multi Poco Queries.

See also: SQL Builder, Mapping POCOs, Multi Poco Queries

Parameters

Pass in numbered parameters:

var a = db.Query<article>("SELECT * FROM articles WHERE article_id>=@0 and article_id<@1", firstid, lastid)

Any @ characters in the SQL should be escaped as @@.

If you use the SQL Builder the parameter numbers are reset to zero for each appended segment:

var a = db.Query<article>(PetaPoco.Sql.Builder
    .Append("SELECT * FROM articles")
    .Append("WHERE article_id=@0", child_id)
    .Append("OR article_id=@0", parent_id)
)

The SQL Builder is also useful for building dynamic WHERE clauses:

var sql = PetaPoco.Sql.Builder
    .Append("SELECT * FROM articles")
    .Append("WHERE article_id=@0", id);

if (start_date.HasValue)
    sql.Append("AND date_created>=@0", start_date.Value);

if (end_date.HasValue)
    sql.Append("AND date_created<=@0", end_date.Value);

var a = db.Query<article>(sql)

Named parameters can be passed via a concrete or anonymous type with properties that match the parameter names:

class ParmValues
{
    public int Foo { get; set; }
    public string Bar { get; set; }
}

var parms = new ParmValues()
{
    Foo = 5,
    Bar = "apple"
}

db.Query<SomeClass>(sql, parms);

// OR

db.Query<SomeClass>(sql, new { Foo = 5, Bar = "apple" });

Paged Fetches

PetaPoco can automatically perform paged requests.

var result = db.Page<article>(1, 20, // <-- page number and items per page
        "SELECT * FROM articles WHERE category=@0 ORDER BY date_posted DESC", "coolstuff");

In return you'll get a PagedFetch object:

public class Page<T> where T:new()
{
    public long CurrentPage { get; set; }
    public long ItemsPerPage { get; set; }
    public long TotalPages { get; set; }
    public long TotalItems { get; set; }
    public List<T> Items { get; set; }
}

Behind the scenes, PetaPoco does the following:

1. Synthesizes and executes a query to retrieve the total number of matching records
2. Modifies your original query to request just a subset of the entire record set

You now have everything to display a page of data and a pager control all wrapped up in one handy little object!

Future Changes

A future release will allow parameters to be passed in a Dictionary:

var dict = new Dictionary<string, object>()
{
    ["Foo"] = 5,
    ["Bar"] = "apple"
}
db.Query<SomeClass>(sql, dict);

See PR #623: Allow dictionaries to be passed with parameters.