PetaPoco - Single Column Value Requests

This post is about PetaPoco - "a tiny ORMish thing for your POCOs". Read more on the PetaPoco Project Page.

Single Column Scalar Value Queries

Previous versions of PetaPoco only supported returning poco classes. It now supports queries of the form:

foreach (var x in db.Query<long>("SELECT article_id FROM articles"))
{
    Console.WriteLine("Article ID: {0}", x);
}

This should work for all scalar value types (Type.IsValueType), strings and byte arrays.

At symbol @ Escaping

PetaPoco uses @<name> for name parameters which can conflict with some providers. Previously you could escape this for MySQL, but it's now supported on all providers. So in this example, @@id would be passed to the database as @id whereas @name would be used to lookup a property name on one of the passed arguments:

select
    t.Id as '@@id'
from
    dbo.MyTable as t
where
    t.Name = @name
for xml path('Item'), root ('Root'), type 

Automatic Parenthesizing of Where Clauses

PetaPoco's SQL builder can automatically join consecutive WHERE clauses so that this:

sql.Where("cond1");
sql.Where("cond2");

becomes:

WHERE cond1 AND cond2

This is fine except it makes it quite easy to inadvertently introduce operator precedence bugs. For example, this:

sql.Where("cond1 OR cond2");
sql.Where("cond3");

will be generated as:

cond1 OR cond2 AND cond3

To be honest I don't know what the actual operator precedence of SQL for AND or OR is - nor do I care, but I do know using the PetaPoco's SQL builder's Where method did make it easy to introduce this sort of bug. So the Where() method now parenthesizes it's args, and will now render the following SQL:

(cond1 OR cond2) AND (cond3)

Note: this only applies to the Where method and doesn't work when using Append("WHERE cond").

Misceallaneous Bug Fixes

And some bug fixes:

• T4 fix to not use conflicting property names. Say your DB has a column named Query this will break what the T4 template generates because it conflicts with the Query method. This has now been fixed and the property will be named _Query
• Table name escaping. Table names with spaces and other characters will now be correctly escaped in SQL generated by PetaPoco.
• Fixed column name escaping in Oracle.

Availability

Available in GutHub now, Nuget package probably coming soon.