PetaPoco - Not So Poco! (or, adding support for dynamic)
Tuesday, 19 April 2011
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?
10 Comments
I dont think it should TBH. I've been playing with Massive for a while now and that is my main issue with it. Dynamic might be good for some small quick things but in any serious project you need some static POCOs (to provide validation and refactoring support at the very least). While it shounds nice I really don;t see any real benefit from it.
My $0.02 anyway.
I tend to agree with James. If you do need to do grouping or reporting stuff, creating a flat model to do the mapping first isn't that lengthy a process. It also enables you to use the TypeConverter to change values as they come in. eg. Y -> true etc.
I Think it is a great addition. Makes it very flexible and easy to ude. Espcially while prototyping. Why not have the option of using dynamics?
Supporting ExpandoObjects would be extremely helpful!
A large part of our application is built at runtime from metadata providing all info about tables, columns etc. to build complex form and queries on demand. Massive was a great match for this, but I would love to switch to PetaPoco completely.
Thank you for including Dynamics support. It will come in handy for those cases when itโs not worth the trouble of creating a custom model.
I like SQL, and my preferred approach is to create views to flatten and preprocess my tables into view models. I tweak the T4 templates to build my batch of read only view model POCOs. Keeping the data store and presentation logic separate may be the right call for major applications, but itโs clean, simple and fast for my little projects.
Great addition!!
This is a great piece of software. I have tried Massive but stumbled into problems with tables with dots (e.g. "ta.ble"). Currently testing PetaPoco! =)
I personally think it is a great idea, while I won't use it myself it helps PetaPoco cover edge cases that people may have, which makes it a better API in my eyes.
Add it. Developers dont have to use it, but it provides some very rich flexiblity. Over RESTful query APIs I can take the dynamic and easily turn it into an unstructured JSON result.
I'm agree with James about the fact that Poco is essential to easily refactor code. However, when you have a query that is executed single time in a method or function and that the result of that query is only used inside that method of function, I think dynamic is the best solution. If the query is directly map to a single table, a Poco should be better but in my case, when I have to query a single table, I use LightSpeed of Mindscape. But when I have to create a query with some joins or some complex where clauses, Linq is not in my view the choice even if we are in 2012. Maybe in the futur Linq will be so powerful than pure Sql but this time is not arrived.
Leave a comment