PetaPoco - Oracle Support and more...

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

Most of the changes here are contributions by Adam Schroder who deserves special thanks not just for the miscellaneous improvements he's made but also the Oracle support. Thanks Adam.

Oracle Support

As just mentioned, PetaPoco now supports Oracle. Mostly this is transparent and just works. The one exception is support for sequences. To support this, the [PrimaryKey] attribute has a new named property that can be used to specify the name of a sequence.

eg:

[PrimaryKey("id", sequenceName="article_id_seq")]

Once set, inserting records with PetaPoco's Insert method will automatically populate the primary key column with the next sequence value (eg: article_id_seq.nextval). It will also retrieve the new sequence value and put it back into the POCO.

There were a few other changes to get Oracle working, but nothing worth mentioning here.

Oh! And Adam's also provided an updated T4 template for generating POCOs straight from your Oracle schema.

Primary Key Versions Of Single and SingleOrDefault

Fetching a single record with PetaPoco was already pretty simple:

var a = db.SingleOrDefault<article>("WHERE article_id=@0", some_id);

Of course the most common case of the above is fetching a record by it's primary key, so PetaPoco now has a new overloads of Single and SingleOrDefault:

var a = db.SingleOrDefault<article>(some_id);

As a side note, don't forget if you're using the T4 templates the above can be simplified even more:

// Normal version
var a = article.SingleOrDefault("WHERE title=@0", "My Article");

// New simpler PK version
var a = article.SingleOrDefault(some_id);

Too easy!

SQL Builder Methods for Joins

PetaPoco's SQL builder now supports joins through two new methods InnerJoin and LeftJoin. eg:

var sql = Sql.Builder
    .Select("*")
    .From("articles")
    .LeftJoin("comments").On("articles.article_id=comments.article_id");

Enum Property Types

Previously an exception would be thrown if you tried using a POCO with an enum property. Now PetaPoco will correctly cast any integer column value onto such a property.

New OnExecutingCommand Virtual Method

OnExecutingCommand is a new virtual methods that gets called just before PetaPoco hits the database

// Override this to log commands, or modify command before execution
public virtual void OnExecutingCommand(IDbCommand cmd) { }

There's two things you might use this for:

1. For logging - you can grab the SQL and parameter values off the command and log them to wherever you need.
2. For tweaking the SQL - you can modify the SQL command text before returning - perhaps to tweak it for a particular brand of database.

As always, all these changes are available in GitHub and NuGet now.