PetaPoco – Tips and Tricks

Deciding on a data layer for your application is one of the most important decisions you will make and there is no shortage of viable options.  In the .NET world you have full fledged ORM’s such as Entity Framework or NHibernate.  There are also a large number of micro-ORM’s that solve many of the pain points of writing your data layer directly while still allowing you to manually control the actual SQL used.  Last, you always have the option of rolling your own, though there are many reasons why this is not usually the right choice. 

This post is not about which ORM you should choose, but rather a place to keep a couple of helpful tips/tricks relating to one of my favorite micro-ORM’s, PetaPoco.

PetaPoco is a great little ORM that is there when you need it and gets out of the way when you don’t.  I have used this guy in several ASP.net MVC web applications now and I have yet to be disappointed.  One of the great things about PetaPoco is that it is open source and largely in a single class.  You can grab the source here:

https://github.com/CollaboratingPlatypus/PetaPoco

Alternatively, if you live in Visual Studio there is also a NuGet package.  I would recommend grabbing the source as that is the best documentation.

To get started, you should definitely check out the one (albeit long) page documentation here:

http://www.toptensoftware.com/petapoco/

Tip 1: Model Annotations

PetaPoco is great at mapping database results to POCO’s. What happens when you want your model to contain a calculated value?  Here is where annotations come to the rescue. 

Here is a basic Model:

public class Customer
{
  public string sFirstName { get; set; }
  public string sLastName { get; set; }

  [PetaPoco.Ignore]
  public string sFullName
  {
    get { return string.Format("{0} {1}", sFirstName, sLastName); }
    set { }
  }
}

In this model the two public properties sFirstName and sLastName directly correlate to columns being returned from the database.  However, in our views we may want to be able to be able to display the full name, hence we create a new public property named sFullName and put code in the get {} to return the first name plus the last name.  The catch is that we don’t want PetaPoco to try to map this property in the Model POCO to a column in the database because it doesn’t exist.  Simply adding the [PetaPoco.Ignore] annotation will tell PetaPoco to skip this column when generating the update SQL.

There are a handful of other useful annotations as well (PrimaryKey, ResultColumn, etc) but Ignore is by far the one I use most often.

Tip 2: The SQL Object

One of the primary reasons I dig PetaPoco so much is that it allows me to still write SQL when I need to. They do have lots of helper methods to do the entire mapping from database to object, but I don’t have to mess around with trying to get Linq expressions figured out to do complex queries.  Linq is great for simple stuff but SQL is by far the best language for getting complex data out of a database, so stop trying to abstract it away.  Additionally, the SQL object makes it trivial to use parameterized queries, which are and essential when dealing with web applications that you want to keep secure.

Here is an example:

Sql sql = new Sql()
       .Append(@"select iCIId, sCITypeCode, sCIName, sCITitle, sCIPhone, sCIEmail
                              ,sCIAddress, sCICity, sCIState, sCIZip, sCIComments 
                                , sCTTypeCode, sCTTypeDescription
                   from CustomerContacts
                        inner join ContactInfo on iCCContactInfoId = iCIId
                        inner join ContactType on sCITypeCode=sCTTypeCode
                   WHERE iCCCustomerId=@0", customerId);

return db.Query<ContactInfo, ContactType>(sql).ToList<ContactInfo>();

The SQL Object demonstrated above is one of the Helper classes included in the PetaPoco.cs file.  It provides an easy way for you to create SQL statement with parameterized queries.  It does all the mapping of the variable types saving you lots of boilerplate typing.

Tip 3: ORM

Object-relational mapping is the bulk of what PetaPoco does.  The tip here is actually how to let PetaPoco know what types of objects you are looking to have returned.  I am putting this as a tip because it works slightly different than other ORMs.

In tip 2, I included the return line where I am using the PetaPoco Database object (the db variable) and using the query function to execute the SQL I built.  In other ORM’s you typically only have to specify the single Type of the object that you are mapping the results into.  In this case I have defined two types, ContactInfo and ContactType.  Both of those are Models in my MVC application and the ContactInfo model has a public property of the type ContactType.

PetaPoco will not automatically find and perform that mapping.  This in itself I don’t find to be a problem and in actuality it may make the intended operation crystal clear.

PetaPoco does have a couple of drawbacks related to how it handles this mapping.

1.     You are limited to 4 types.

a.     If your model has more than 4 distinct non-primitive types you will need to do some additional legwork.  You can have as many primitive types as your heart desires.

2.     Having multiple properties in your model of the same non-primitive type will cause you issues.

a.     I have found the easiest way to get around this is simply selecting the individual columns back with an alias, then mapping them in the model to their non-primitive types.  You just need to make sure you use the [PetaPoco.ignore] annotation.

I have seen some grumbling on other sites about these limitations.  In my experience with PetaPoco these have been easily worked around and are a small trade off for the small size and ease of use of PetaPoco.

Tip 4: Catch ORM Exceptions

In my web applications using PetaPoco I typically include the PetaPoco.cs file directly.  I then create a new class called AppDataLayer where I abstract away the details of the actual connection to the database making it dead simple to connect to and use the database in the app.

One small but very important feature of this class is the exception handling.  PetaPoco utilizes a virtual method OnException that it calls on all exceptions.  You can either override this method or simply add your logging to PetaPoco.cs.  PetaPoco provides LastCommand() and LastSQL() methods that allow you to log out all the pertinent details to help you figure out where it all went wrong.