What is PetaPoco?
PetaPoco is defined as tiny micro ORM (Object Relational Mapping) but for the non techy people like myself all this means is that we have an easy way to map objects in our code to custom tables in our database, to be able to do simple CRUD (Create, Read, Update & Delete) operations on that data in a very simple manner.
Read on to find out why you should use it and how you can use it in your Umbraco websites.
Why should I use it?
So you may ask yourself why would I ever use this and why would it benefit me? Well let’s take a scenario to help explain it better. If we have a blog site built on Umbraco and want to store comments for our blog posts. You may store them as child nodes of your blog post node in the content tree, but if your blog grows quickly or you have a lot of blog posts or comments then you will content tree will start to grow at a very rapid pace. Instead we could create a custom database table in our Umbraco website, where we could store our comments in and will keep our Umbraco content tree a lot more tidier. But there are many use cases for storing information in custom database tables as opposed to content nodes directly inside Umbraco.
How do I use it?
Rather than try to explain it without any code, I would much prefer to show you with some heavily commented working examples for you to read over.
So let’s carry on to the next section and show you the code!
Show me the code!
Firstly create a folder in your Umbraco website called pocos and add a class file called BlogComment.cs
using System; using System.Collections.Generic; using System.Linq; using System.Web; //These are the main namespaces we need to use using Umbraco.Core.Persistence; using Umbraco.Core.Persistence.DatabaseAnnotations; namespace MyWebsite.pocos { [TableName("BlogComments")] [PrimaryKey("BlogCommentId", autoIncrement = true)] [ExplicitColumns] public class BlogComment { [Column("id")] [PrimaryKeyColumn(AutoIncrement = true)] public int BlogCommentId { get; set; } [Column("BlogPostUmbracoId")] public int BlogPostUmbracoId { get; set; } [Column("Name")] public string Name { get; set; } [Column("Email")] public string Email { get; set; } [Column("Website")] public string Website { get; set; } [Column("Message")] [SpecialDbType(SpecialDbTypes.NTEXT)] public string Message { get; set; } } }
Next create a class file called RegisterEvents.cs this is used to hook into the Application Startup event of Umbraco, which will allow us to check if the custom database table exists or not and if it doesn’t then to create it for us.
using System; using System.Collections.Generic; using System.Linq; using System.Web; using umbraco.cms.presentation; using Umbraco.Core; using umbraco.BusinessLogic; using umbraco.cms.businesslogic; using umbraco.cms.businesslogic.web; using Umbraco.Core.Persistence; using MyWebsite.pocos; namespace MyWebsite { public class RegisterEvents : ApplicationEventHandler { //This happens everytime the Umbraco Application starts protected override void ApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext) { //Get the Umbraco Database context var db = applicationContext.DatabaseContext.Database; //Check if the DB table does NOT exist if (!db.TableExist("BlogComments")) { //Create DB table - and set overwrite to false db.CreateTable<BlogComment>(false); } //Example of other events (such as before publish) Document.BeforePublish += Document_BeforePublish; } //Example Before Publish Event private void Document_BeforePublish(Document sender, PublishEventArgs e) { //Do what you need to do. In this case logging to the Umbraco log Log.Add(LogTypes.Debug, sender.Id, "the document " + sender.Text + " is about to be published"); //cancel the publishing if you want. e.Cancel = true; } } }
The final part is for us to insert records into our custom database table and retrieve those values as well. There may be a few different approaches to this but normally in your Controllers or in our case with Umbraco it would be SurfaceControllers we would have a few methods in a controller to deal with a blog comment form post and also another for listing out the blog post comments to a partial view.
using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.Linq; using System.Web; using System.Web.Mvc; using Umbraco.Web.Mvc; namespace MyWebsite.controllers.SurfaceControllers { // I don't encourage you to put your viewmodel class in your surface controller, only done for this example for readability public class BlogPostViewModel { [Required] public string Name { get; set; } [Required] [EmailAddress] public string Email { get; set; } [Url] public string Website { get; set; } [Required] public string Message { get; set; } } public class BlogSurfaceController : SurfaceController { [HttpGet] public ActionResult RenderComments() { //Connect to the Umbraco DB var db = ApplicationContext.DatabaseContext.Database; //Get the current Umbraco Node ID var currentNodeId = UmbracoContext.PageId.Value; //Get an IENumberable of BlogComment objects to iterate over var comments = db.Query("SELECT * FROM BlogComments WHERE [email protected]", currentNodeId); //Return the view with our model and comments return PartialView("viewComments", comments); } [HttpPost] public ActionResult HandleCommentPost(BlogPostViewModel model) { //Check if the data on the model is valid if (!ModelState.IsValid) { //There was a validation error with the data return CurrentUmbracoPage(); } //Continue processing the data... //Create new Blog Post object var blogPostToAdd = new BlogComment(); //Set values from view model & grab the current node ID blogPostToAdd.UmbracoNodeId = UmbracoContext.PageId.Value; blogPostToAdd.Name = model.Name; blogPostToAdd.Email = model.Email; blogPostToAdd.Website = model.Website; blogPostToAdd.Message = model.Message; //Get the Umbraco db var db = ApplicationContext.DatabaseContext.Database; //Add the object to the DB db.Insert(blogPostToAdd); //All done - redirect to the page return RedirectToCurrentUmbracoPage(); } } }
Well that’s it apart from a Razor Partial View to loop over the IENumberable of Blog Comments so they can be displayed on the client side of the site.
Would love to hear your thoughts & ideas on this.
Thanks,
Warren 🙂
Nice blogposts, Warran. Thanks for that.
CreateTable is not “native” PetaPoco is it?
LikeLike
Hey Jesper.
Good point the Umbraco core team have extended PetaPoco a little to all make our lives a little easier.
I will update/edit this post when I get in the office today. Detailing some or all of the additions the core team has made.
Cheers
Warren
LikeLike
where does the RegisterEvents.cs class go, also in the pocos folder? Seems trivial, but I want to do this right.
And yes, you should put your model in your controller 😉
LikeLike
Hey Daniel would disagree re the model class to be in the controller class. I have a folder called models to contain all my model classes. A folder for controller classes. And the registerevents class I have in another folder called like custom Umbraco or similar it’s personal preference where you want to put that class.
Cheers
Warren 🙂
LikeLike
I’m just starting with MVC so not sure if I’m understanding things correctly but in your example you have a BlogComment class and a BlogPostViewModel model. Aren’t they the same thing, the model is the class that handles the business logic / database access.
LikeLike
They are indeed very similar however the main differences are the data attributes. So the line above property names in square brackets. So one has [required] used for validating the model in the controller and on the client side in the HTML.
The other is using data attributes specific for PetaPoco so it knows what DB columns to create and map the property to etc.
Hope that makes better sense now Suzy.
Thanks
Warren
LikeLike
Can you not just combine the data attributes.
LikeLike
I have not tried it to be honest, but it may work I suppose. I just prefer to keep my models separate so it’s easier to maintain personally.
LikeLike
Hi Warren,
Quick one regarding the primary key… when I try to insert data I’m returned an error of “Cannot insert explicit value for identity column in table ‘TableName’ when IDENTITY_INSERT is set to OFF.”
Same code as above (i.e. not trying to explicitly pass an Id), however if I remove the identity column definition from the poco class file once the database table has been created it runs fine.
I’ve read about poco being smart enough to not enter the 0 that is defaulted into the Id column on insert it but doesn’t seem to be doing it for me, could that be Umbraco version related?
Thanks
LikeLike
Hello Matt,
Sorry for the delay in my reply.
I have not seen this issue before & I would recommend asking the question on our.umbraco.org to see if it is a specific problem with the PetaPoco version used in Umbraco. Maybe I can recommend you try it with the default PetaPoco class & see if that works for you or not.
Thanks,
Warren
LikeLike
Hi Matt,
Just had the same problem. Worked around it by using the .save() method instead of .insert()
Cheers
Bjørn
LikeLike
I Had the same issue, I modified [PrimaryKey(“BlogCommentId”, autoIncrement = true)] on the Blog comment Class to [PrimaryKey(“Id”, autoIncrement = true)]
LikeLike
Hey Warren
Great practical example, but why? As in why separate comments from the Umbrao tree? uBlogsy for example stores all comments in Umbraco as part of the published blog.
LikeLike
Hey Martin,
Everyone is different. I prefer to have comments not pollute my content tree especially if a blog post was to be popular with comments, so hence I have taken this approach but mainly to give a real world explaining how to use PetaPoco with Umbraco.
Thanks,
Warren 🙂
LikeLike
The listview in Umb7 largely solves the pollution issues as its a much friendlier view type. Also you retain the easily searchable/indexable aspect of Umbraco/Examine out of the box with the comments. But as an example it was very useful, I’ve used Dapper on a non-Umbraco project so i’ve seen the advantages of a MiORM.
LikeLike
Yep exactly!
Different strokes for different folks 🙂
LikeLike
Hi Warren,
Using the register events, how would you go about updated the table schema for example adding additional fields.
LikeLike
Hi Paul,
It has been a while since I have done some PetaPoco stuff, but the way I would approach it as follows:
See if the table exists, if so then query for a new field if that does not exist or returns null, then you know your new fields have not been added and you can run your db query to add these fields to the table as needed.
I am sure PetaPoco guru’s may have a different approach.
However I just had a very quick skim of the Umbraco code base and it seems Umbraco deals with this themselves for any DB changes between versions when doing an Umbraco upgrade.
https://github.com/umbraco/Umbraco-CMS/tree/3af3e054d7da078ea4ad8b936f15a313bea368d3/src/Umbraco.Core/Persistence/Migrations
Cheers,
Warren 🙂
LikeLike
HI Warren
loved the post how would you do the above but with a table outside of umbraco ie in an external database.
I’ve got the connection string in my webconfig but can’t figure out how to connect to it using peta pocas in umbraco
LikeLike
Hi Bal,
I would look at the PetaPoco documentation itself in this case in my opinion.
http://www.toptensoftware.com/petapoco/
LikeLike
Thanks Warren – very helpful and greatly simplified my project.
Line 62 of the surface controller threw an error
blogPostToAdd.UmbracoNodeId = UmbracoContext.PageId.Value;
so I changed it to
blogPostToAdd.BlogPostUmbracoId = UmbracoContext.PageId.Value;
to match the class.
Also in my context Line 69
var db = ApplicationContext.DatabaseContext.Database;
needed
using Umbraco.Core;
LikeLike
Hi John,
Thanks for the feedback, it was most likely a typo on my part, but thanks for the feedback.
Cheers,
Warren
LikeLike
Reblogged this on Passionate coder.
LikeLike