PetaPoco - Not So Poco! (or, adding support for dynamic)

Tuesday, April 19th, 2011     #petapoco #everything

PetaPoco's main focus is obviously to work with POCO objects. Sometimes though you just want to run query and not have to worry about declaring an object for results. C#'s 4.0's new dynamic support is perfect for this.

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

First up, let me say everything in this post is experimental in that it may not make it into the master branch.

EDIT: it did make it into master and is available now in Nuget and Github

PetaPoco was originally inspired by Massive - which returns everything through dynamic Expando objects. For most uses I find this cumbersome and prefer strongly typed classes. There are cases however where support for dynamics is useful - particularly for joins, group by and other calculated queries.

To do a "dynamic" query just use the existing query methods but pass dynamic as the generic parameter. The returned objects will have a property for each column returned by the query:

foreach (var a in db.Fetch<dynamic>("SELECT * FROM articles"))
    Console.WriteLine("{0} - {1}", a.article_id, a.title);

Note there's no support for automatic SELECT clauses - since PetaPoco doesn't know the table name.

You can also do updates, inserts and deletes but you need to specify the table name and primary key of the table to update:

// Create a new record
dynamic a = new ExpandoObject();
a.title = "My New Article";

// Insert it
db.Insert("articles", "article_id", a);

// New record ID returned with a new property matching the primary key name
Console.WriteLine("New record @0 inserted", a.article_id")

Here's an update:

// Update
var a = db.Single("SELECT * FROM articles WHERE article_id=@0", id);
a.title="New Title";
db.Update("articles", "article_id", a);

Delete(), Save() and IsNew() all work similarly.

As stated above, this is currently experimental and only available in a separate github branch. It's also optional - there's a compile time directive that can be used to disable it for use in .NET 3.5.

What do you think - should this be built-in to PetaPoco?

« Older - PetaPoco - Version 2.1.0 Newer - PetaPoco - Oracle Support and more... »