Umbraco & PetaPoco to store blog comments

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 BlogPostUmbracoId=@0", 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 🙂

Advertisements

29 thoughts on “Umbraco & PetaPoco to store blog comments

    1. 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

  1. 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 😉

    1. 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 🙂

  2. 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.

    1. 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

  3. 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

    1. 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

      1. I Had the same issue, I modified [PrimaryKey(“BlogCommentId”, autoIncrement = true)] on the Blog comment Class to [PrimaryKey(“Id”, autoIncrement = true)]

    1. 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 🙂

      1. 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.

  4. Hi Warren,

    Using the register events, how would you go about updated the table schema for example adding additional fields.

    1. 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 🙂

  5. 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

  6. 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;

  7. I would also love to see the view, I am stuck with this issue. I am getting data from a custom, and assemble my model. It’s all fun and games, but when I try to send my model to the View for a display, Umbraco freaks out!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s