XML FeedsSince Chrissie is playing around with Simple.Data today, I found some time to play with PetaPoco. PetaPoco is a single file micro ORM that uses MSIL generation to do it's magic. As the name suggests, it works with concrete POCOs, though support for dynamics is also being tested. It is designed to be fast and doesn't try to reimplement SQL, so double win in my book. PetaPoco was created by Brad Robinson (b | t).
It has been on my list to try for a while, so today seemed like a good day for it.
Setting it up
Setup is simple using Nuget:
- Install-Package PetaPoco
- Install-Package SqlServerCompact
PetaPoco comes with a T4 template to generate POCOs from a database, all you have to do is add a connection string to your config and run the transform. This is a neat feature, but I didn't plan to use it for this post so I excluded the files from my project.
PetaPoco supports SQL Server, SQL Server CE, MySQL, PostgreSQL and Oracle, and works with .Net 3.5 and Mono 2.6 forward. Documentation is available via the Main Page and blog posts.
As Chrissie did in his post, we're going to first create a Compact SQL database, except we'll be doing it in C#.
- private SqlCeEngine CreateDatabase() {
- if (File.Exists("test.sdf")) File.Delete("test.sdf");
- string connectionString = "DataSource=\"test.sdf\"; Password=\"chrissiespassword\"";
- var en = new SqlCeEngine(connectionString);
- en.CreateDatabase();
- return en;
- }
Now that we have Chrissie's database, lets add his table.
- private void CreateTable() {
- using (var db = new Database("DataSource=\"test.sdf\"; Password=\"chrissiespassword\"", "System.Data.SqlServerCe.4.0")) {
- db.Execute("CREATE TABLE Person (LastName nvarchar (40) NOT NULL, FirstName nvarchar (40))");
- }
- }
This statement was quite a bit shorter using PetaPoco then it was with Simple.Data. The Database object takes care of the connection and command work for us, leaving us just the bits that are specific to our individual scenario. We still have the option of providing an IDbConnection if we want, which would be handy if we were using something like Sam Saffron's MiniProfiler and wanted to pass in a profiled connection object.
Because we want to play with concrete POCOs, I'm going to add a very plain POCO and a very basic decorated POCO:
- public class Person {
- public string LastName { get; set; }
- public string FirstName { get; set; }
- }
- [PetaPoco.TableName("Person")]
- public class DecoratedPerson : Person { }
There are also attributes for the primary key* and the ability to explicitly define columns so PetaPoco will know which columns should be included in queries and which should not.
I have to say I was surprised Chrissie didn't include a Primary Key given how many DBAs and DB Developers also blog here, brave man 
So as chrissie pointed out in his post, we've got the basic setup behind us and can now move forward to interacting with our new database.
PetaPoco
With a database created, lets go ahead and add some data to play with.
- db.Insert("Person", null, new Person() { LastName = "lastname1", FirstName = "firstname1" });
The first overload of the Insert() method takes a table name, primary key name, and the POCO instance to insert. if we don't mind add some decoration to our Person object, we can decorate the POCO with the table name and shrink it to this:
- db.Insert(new DecoratedPerson() { LastName = "lastname2", FirstName = "firstname2" });
This method uses the table name attribute to generate the insert.
And last, if we want to keep our table and object names in sync, we can let reflection magically figure it out:
- db.Insert(new Person() { LastName = "lastname3", FirstName = "firstname3" });
Now that we have some data in our database, lets look at a few ways to get it out.
- // select statement
- var results = db.Query<Person>("SELECT * FROM Person WHERE lastname=@0", "lastname1");
- // let PetaPoco generate the SELECT portion
- var results = db.Query<DecoratedPerson>("WHERE lastname=@0", "lastname1");
We can execute a parameterized SQL statement fairly easily by using numbered parameters that will line up with the additional arguments we provide. In the second case we're actually letting PetaPoco generate the SELECT portion of the statement for us, which will resolve to: SELECT [Person].[LastName], [Person].[FirstName] FROM [Person] WHERE lastname=@0.
Executing specifically for a single record instead of querying for a collection is similarly straight forward:
- // select statement
- var result = db.Single<Person>("SELECT * FROM Person WHERE lastname=@0", "lastname1");
- Console.WriteLine(String.Format("{0}: {1}", result.GetType(), result));
- // let PetaPoco generate the SELECT portion
- var result = db.Single<DecoratedPerson>("WHERE lastname=@0", "lastname1");
- Console.WriteLine(String.Format("{0}: {1}", result.GetType(), result));
And if we examine the output we'll see they are concrete instances of our POCOs, not dynamics or proxies:
If we then follow Chrissie's lead and add two records into the database that will match this criteria, we'll receive an exception, as we would expect from a Single call. PetaPoco also offers a First<T> implementation we could use in this situation, a SkipTake<T> we could use to get the 2nd record, and a number of different ways to query multiple records out of the database:
- // T
- var result = db.First<DecoratedPerson>("WHERE lastname=@0", "lastname1");
- // List<T>
- var results = db.SkipTake<DecoratedPerson>(1, 1, "WHERE lastname=@0", "lastname1");
- //IEnumerable<T>
- var results2 = db.Query<DecoratedPerson>("WHERE lastname=@0", "lastname1");
- //List<T>
- var results3 = db.Fetch<DecoratedPerson>("WHERE lastname=@0", "lastname1");
- //Page<T> - page #2 and page size of 1
- var results4 = db.Page<DecoratedPerson>(2, 1, "WHERE lastname=@0", "lastname1");
Conclusion
I haven't done much with PetaPoco yet, but just from playing with these basic queries I can tell I want to spend some more time with it. The syntax is clean and focuses on simplifying the bits that are repeated in so many projects (connection and command wrangling, mapping) while leaving me the full power of SQL and not injecting an additional layer of abstraction to try to work through. On top of that, it performs very closely to the speed of hand-coded SqlDataReader statements (results available on the dapper-dot-net page).
My sample code is up on GitHub.
Eli delivers software and technology solutions for a living. His roles have included lone developer, accidental DBA, team lead, and even unintentional Solaris consultant once. With experience in adhoc, Lean, and Agile environments across NSF grants, SaaS products, and in-house IT groups, he is just as willing to chat about the principles of Lean or Continuous Delivery as he is to dive into Azure, SQL Server, or the last ATDD project he created. 
That is brave indeed that Chrissie doesn't have a primary key...or he simply doesn't know what a primary key is :-) 
An interesting thing about PetaPoco is that it really nudges you in the direction of having one and has support for auto-incrementing id's built right in. I may end up doing a more in depth follow-up just on some of the more interesting features it has where you can really tell it's trying to remove the cookie cutter code without changing how you work with the stuff that changes from query to query (like the query :P).
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.