PetaPocoA tiny ORM-ish thing for your POCOs PetaPoco is a tiny, fast, single-file micro-ORM for .NET and Mono.
BackgroundPetaPoco was original inspired by Rob Conery's Massive project but for use with non-dynamic POCO objects. It came about because I was finding many of my projects that used SubSonic/Linq were slow or becoming mixed bags of Linq and CodingHorror. I needed a data acess layer that was tiny, fast, easy to use and could run on .NET 3.5 and/or Mono 2.6 (ie: no support for dynamic expandos). Rob's claim of Massive being only 400 lines of code intruiged me and I wondered if something similar could be done without dynamics. So, what's with the name? Well if Massive is massive, this is "Peta" massive (it's now over 1,500 lines after all) and since it works with "Poco"s ... "PetaPoco" seemed like a fun name!! PetaPoco's line count has grown to more than I originally hoped - it's not the tiny 400 lines of Massive. But check out what it can do ... it packs a lot of punch for it's size. Features at a Glance
DownloadPetaPoco is available from:
At this point in time support for dynamic expandos is only in github on a separate branch. Show Me the Code!These examples start out more verbose than they need to be but become less so as more features are
introduced... make sure you read to the bottom for the full experience. I've explicitly referenced the PetaPoco
namespace to make it obvious what comes from where but in reality you'd probably chuck in a Also, all of these examples have been hand-typed and never compiled. There are probably typos. If so, please let me know. No AssemblyPetaPoco is supplied as a single file - PetaPoco.cs. With no dependencies other than what's in the GAC, just add this file to your project and you're set to go... Running QueriesFirst define your POCOs:
Next, create a
To query a scalar:
Or, to get a single record:
Paged FetchesPetaPoco can automatically perform paged requests.
In return you'll get a PagedFetch object:
Behind the scenes, PetaPoco does the following:
You now have everything to display a page of data and a pager control all wrapped up in one handy little object! Query vs FetchThe Database class has two methods for retrieving records Non-query CommandsTo execute non-query commands, use the Execute method
Inserts, Updates and DeletesPetaPoco has helpers for insert, update and delete operations. To insert a record, you need to specify the table and its primary key:
Updates are similar:
Or you can pass an anonymous type to update a subset of fields. In this case only the article's title field will be updated.
To delete:
Decorating Your POCOsIn the above examples, it's a pain to have to specify the table name and primary key all over the place, so you can attach this info to your POCO:
Now inserts, updates and deletes get simplified to this:
There are also other overloads for Update and Delete:
You can also tell it to ignore certain fields:
Or, perhaps you'd like to be a little more explicit. Rather than automatically mapping all columns you can use the ExplicitColumns attribute on the class and the Column to indicate just those columns that should be mapped.
This works great with partial classes. Put all your table binding stuff in one .cs file and calculated and other useful properties can be added in a separate file with out thinking about the data layer). Hey! Aren't there already standard attributes for decorating a POCO's database info?Well I could use them but there are so few that PetaPoco supports that I didn't want to cause confusion over what it could do. Hey! Wait a minute... they're not POCO objects!Your right, the attributes really do break the strict concept of POCO, but if you can live with that they really do making working with PetaPoco easy. T4 TemplateWriting all those POCO objects can soon get tedious and error prone... so PetaPoco includes a T4 template that can automatically write classes for all the tables in your your SQL Server, SQL Server CE, MySQL, PostgreSQL or Oracle database. Using the T4 template is pretty simple. The git repository includes three files (The NuGet package adds
these to your project automatically in the folder
A typical Database.tt file looks like this:
To use the template:
All going well Database.cs should be generated with POCO objects representing all the tables in your database. To get the project to build you'll also need to add PetaPoco.cs to your project and ensure it is set to compile (NuGet does this for you) . The template is based on the SubSonic template. If you're familiar with its ActiveRecord templates you'll find PetaPoco's template very similar. Automatic Select clausesWhen using PetaPoco, most queries start with "SELECT * FROM table". Given that we can now grab the table name from the POCO object using the TableName attribute, there's no reason we can't automatically generate this part of the select statement. If you run a query that doesn't start with "SELECT", PetaPoco will automatically put it in. So this:
can be shortened to this:
PetaPoco doesn't actually generate "SELECT *"... rather it picks the column names of the POCO and just queries for those columns. IsNew and Save MethodsSometimes you have a POCO and you want to know if it's already in the database. Since we have the primary key all we need to do is check if that property has been set to something other than the default value. So to test if a record is new:
And related, there's a Save method that will work out whether to Insert or Update
TransactionsTransactions are pretty simple:
Transactions can be nested, so you can call out to other methods with their own nested transaction scopes and the whole lot will be wrapped up in a single transaction. So long as all nested transcaction scopes are completed the entire root level transaction is committed, otherwise everything is rolled back. Note: for transactions to work, all operations need to use the same instance of the PetaPoco database object. So you'll probably want to use a per-http request, or per-thread IOC container to serve up a shared instance of this object. Personally StructureMap is my favourite for this. But where's the LINQ stuff?There isn't any. I've used Linq with Subsonic for a long time now and more and more I find myself descending into CodingHorror for things that:
Now that I've got CodingHorror all over the place it bugs me that half the code is Linq and half is SQL. Also, I've realized that for me the most annoying thing about SQL directly in the code is not the fact that it's SQL but that it's nasty to format nicely and to build up those SQL strings. So... PetaPoco's SQL BuilderThere's been plenty of attempts at building fluent type API's for building SQL. This is my version and it's really basic. The point of this is to make formatting the SQL strings easy and to use proper parameter replacements to protect from SQL injection. This is not an attempt to ensure the SQL is syntactically correct, nor is it trying to hold anyone's hand with intellisense. Here's its most basic form:
Big deal right? Well what's cool about this is that the parameter indicies are specific to each
You can also conditionally build SQL.
Note that each append call uses parameter @0? PetaPoco builds the full list of arguments and updates the parameter indices internally for you. You can also use named parameters and it will look for an appropriately named property on any of the passed arguments
With both numbered and named parameters, if any of the parameters can't be resolved an exception is thrown. There are also methods for building common SQL stuff:
SQL Command TrackingSometime it's useful to be able to see what SQL was just executed. PetaPoco exposes these three properties:
Watching the LastCommand property in the debugger makes it easy to see what just happened! OnException Handler RoutinePetaPoco wraps all SQL command invocations in try/catch statements. Any exceptions are passed to the virtual OnException method. By logging these exceptions (or setting a breakpoint on this method) you can easily track where an when there are problems with your SQL. MoreThe covers most of the basics of working with PetaPoco, but for more please read these blog posts about PetaPoco. |
|