Using PetaPoco with Umbraco is pretty sweet.
PetaPoco is a lightweight ORM for the .Net framework. Thought it’s lightweight it does a hell of a lot. I borrowed this feature list from the PetaPoco website:
- Tiny, no dependencies... a single C# file you can easily add to any project.
- Works with strictly undecorated POCOs, or attributed almost-POCOs.
- Helper methods for Insert/Delete/Update/Save and IsNew
- Paged requests automatically work out total record count and fetch a specific page.
- Easy transaction support.
- Better parameter replacement support, including grabbing named parameters from object properties.
- Great performance by eliminating Linq and fast property assignment with DynamicMethod generation.
- Includes T4 templates to automatically generate POCO classes for you.
- The query language is SQL... no weird fluent or Linq syntaxes (yes, matter of opinion)
- Includes a low friction SQL builder class that makes writing inline SQL much easier.
- Hooks for logging exceptions, installing value converters and mapping columns to properties without attributes.
- Works with SQL Server, SQL Server CE, MySQL, PostgreSQL and Oracle.
- Works under .NET 4.0 or Mono 2.8 and later.
- Experimental support for dynamic under .NET 4.0 and Mono 2.8
- NUnit unit tests.
- OpenSource (Apache License)
- All of this in about 1,500 lines of code
That’s a whole lot of awesome, and it’s already included with the Umbraco CMS.
Also on the PetaPoca product page there are heaps of valuable usage code examples. I definitely recommend reading through it if you get the chance.
Now, hold on to your Dr. Who coffee mug, cause I’m about to show you the money! And by money I mean tutorial. And by tutorial I mean this code I duct taped onto an Umbraco CMS project.
The code
For this example I’m creating two tables in the database for storing competitions and competition entries. To do that, I need a couple of POCOs, and they’re aptly named, Competition and CompetitionEntry.
using System;
using Umbraco.Core.Persistence;
using Umbraco.Core.Persistence.DatabaseAnnotations;
namespace MyDemo.Web.Data.Poco
{
[TableName("wilCompetition")]
[PrimaryKey("Id", autoIncrement = true)]
public class Competition
{
[PrimaryKeyColumn(AutoIncrement = true)]
public int Id { get; set; }
public string Name { get; set; }
[Column("CompetitionDescription")]
[NullSetting(NullSetting = NullSettings.Null)]
[SpecialDbType(SpecialDbTypes.NTEXT)]
public string Description { get; set; }
public DateTime CreatedOn { get; set; }
}
}
using System;
using Umbraco.Core.Persistence;
using Umbraco.Core.Persistence.DatabaseAnnotations;
namespace MyDemo.Web.Data.Poco
{
[TableName("wilCompetitionEntry")]
[PrimaryKey("Id", autoIncrement = true)]
public class CompetitionEntry
{
[PrimaryKeyColumn(AutoIncrement = true)]
public int Id { get; set; }
[ForeignKey(typeof(Competition), Name = "FK_CompetitionEntry_Competition")]
[IndexAttribute(IndexTypes.NonClustered, Name = "IX_CompetitionId")]
public int CompetitionId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
[Length(254)]
public string EmailAddress { get; set; }
public DateTime CreatedOn { get; set; }
}
}
Okay, the attributes do de-POCO my POCOs, but it affords me a bit more control over the way the tables are structured and interpreted. You CAN go without, but we C# developers love the syntactic sugar and that’s how we roll.
Back to the topic at hand. The attributes are going to help us build our tables, relate them, set the keys, all that good stuff. They read pretty easily as well. We’re setting primary keys, foreign keys for relations, table names, indexes, data type lengths, etc...
Now, to get these tables created we’re going to hook into the ApplicationStarted event of the Umbraco CMS startup events. Like so ...
using Umbraco.Core;
using Umbraco.Core.Persistence;
namespace MyDemo.Web.Logic.Extensions
{
public class RegisterEvents : ApplicationEventHandler
{
protected override void ApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
{
var db = applicationContext.DatabaseContext.Database;
if (!db.TableExist("wilCompetition"))
{
db.CreateTable(false);
}
if (!db.TableExist("wilCompetitionEntry"))
{
db.CreateTable(false);
}
}
}
}
Really easy stuff here, on Umbraco’s ApplicationStarted event, we’re checking for the existence of each table, and creating them if they don’t already exist. Now, when the application spins up, the two new tables will be created.
And finally, a bit of code to create and work with the data.
using System;
using Umbraco.Core;
using Umbraco.Core.Persistence;
namespace MyDemo.Web.Logic
{
public class Competitions
{
///
/// Creates the competition.
///
///The name.
///The description.
///
public static Data.Poco.Competition CreateCompetition(string name, string description)
{
var db = ApplicationContext.Current.DatabaseContext.Database;
var competition = new Data.Poco.Competition
{
Name = name,
Description = description,
CreatedOn = DateTime.UtcNow
};
db.Insert(competition);
return competition;
}
///
/// Gets the competition.
///
///The identifier.
///
public static Data.Poco.Competition GetCompetition(int id)
{
var db = ApplicationContext.Current.DatabaseContext.Database;
var competition = db.SingleOrDefault(id);
return competition;
}
///
/// Gets the competitions.
///
///The page.
///The items per page.
///
public static Page GetCompetitions(long page, long itemsPerPage)
{
var db = ApplicationContext.Current.DatabaseContext.Database;
var sql = new Sql()
.Select("*")
.From()
.OrderByDescending("CreatedOn");
var competitions = db.Page(page, itemsPerPage, sql);
return competitions;
}
}
}
using System;
using Umbraco.Core;
using Umbraco.Core.Persistence;
namespace MyDemo.Web.Logic
{
public class CompetitionEntries
{
///
/// Creates the competition entry.
///
///The competition identifier.
///The first name.
///The last name.
///The email address.
///
public static Data.Poco.CompetitionEntry CreateCompetitionEntry(int competitionId, string firstName, string lastName, string emailAddress)
{
var db = ApplicationContext.Current.DatabaseContext.Database;
var competitionEntry = new Data.Poco.CompetitionEntry
{
CompetitionId = competitionId,
FirstName = firstName,
LastName = lastName,
EmailAddress = emailAddress,
CreatedOn = DateTime.UtcNow
};
db.Insert(competitionEntry);
return competitionEntry;
}
///
/// Gets the competition entry.
///
///The identifier.
///
public static Data.Poco.CompetitionEntry GetCompetitionEntry(int id)
{
var db = ApplicationContext.Current.DatabaseContext.Database;
var competitionEntry = db.SingleOrDefault(id);
return competitionEntry;
}
///
/// Gets the competition entries.
///
///The competition identifier.
///The page.
///The items per page.
///
public static Page GetCompetitionEntries(int competitionId, long page, long itemsPerPage)
{
var db = ApplicationContext.Current.DatabaseContext.Database;
var sql = new Sql()
.Select("*")
.From()
.Where("competitionId = @0", competitionId)
.OrderByDescending("CreatedOn");
var competitionEntries = db.Page(page, itemsPerPage, sql);
return competitionEntries;
}
}
}
All easy stuff again, and that’s why I really like PetaPoco. The inserts are easy, I just give it the object and it does the rest. I can easily grab an object by its id using the SingleOrDefault method. I can incredibly easily grab a paged set of objects using the Page method and control the data that comes back with my own conditions like ordering and where statements.
And that’s really about it. It’s powerful, lightweight, easy and totally awesome for extending your Umbraco sites as well as your Umbraco plugins.
Happy coding.
