How to create a custom Database table in Umbraco with PetaPoco

2
422

If you want to store something in a custom table in umbraco you can use PetaPoco.

PetaPoco is a framework that will make it very easy to store whatever model you create in a custom database table. You can change that table via annotations to the model.

I will give you a quick example on how to create your own database tables in umbraco. For my example I will model a list of stores and their employees as depicted in the er-diagramm beneath.

Custom umbraco database planned with an ER-diagramm
ER-diagramm for our custom umbraco database with PetaPoco

Creating the model and adding annotations

Visual Studio File System umbraco add custom tables
Umbraco File System in Visual Studio

First of all we create the models for store and employee and add the annotations PetaPoco needs to create the database tables in the umbraco database.

To create the model we will add a new folder called “pocos” beneath the “models” folder. If you don’t have a folder called “models”, create that one too.

In that folder we create the two models “Store” and “Employee” with the following code:

Store Model

using System;
using Umbraco.Core.Persistence;
using Umbraco.Core.Persistence.DatabaseAnnotations;

namespace UmbracoDatabaseExample.Models.pocos
{
    [TableName("somePrefixStore")]
    [PrimaryKey("Id", autoIncrement = true)]
    public class Store
    {
        [Column("Id")]
        [PrimaryKeyColumn(AutoIncrement = true)]
        public int Id { get; set; }

        [Column("CreatedOn")]
        public DateTime CreatedOn { get; set; }

        [Column("UserId")]
        public int UserId { get; set; }

        [Column("Name")]
        public string FirstName { get; set; }

    }
}

The annotations in this model are important.

  • The TableName annotation allows you the set the name of the database-table. I usually prefix this to avoid conflicts with packages.
  • The Column annonation allows you to name the column which will save the corresponding datapoint to that attribute.
  • The PrimaryKey and PrimaryKeyColumn annotation lets you define the primary key.

Employee Model

using System;
using Umbraco.Core.Persistence;
using Umbraco.Core.Persistence.DatabaseAnnotations;

namespace UmbracoDatabaseExample.Models.pocos
{
    [TableName("somePrefixEmployee")]
    [PrimaryKey("Id", autoIncrement = true)]
    public class Employee
    {
        [Column("Id")]
        [PrimaryKeyColumn(AutoIncrement = true)]
        public int Id { get; set; }

        [Column("CreatedOn")]
        public DateTime CreatedOn { get; set; }

        [Column("UserId")]
        public int UserId { get; set; }

        [Column("FirstName")]
        public string FirstName { get; set; }

        [Column("LastName")]
        public string LastName { get; set; }

        [ForeignKey(typeof(Store), Name = "StoreId_Employee")]
        [IndexAttribute(IndexTypes.NonClustered, Name = "StoreId_Employee")]
        public int StoreId { get; set; }
    }
}

The employees model has the same annotations as the stores model and a ForeignKey annotation in addition. You have to give that foreign key a unique name.

Create the tables in the umbraco database

The next step will be to tell umbraco to create database tables to store instances of these models.

I usually do that on umbraco startup if the database tables have not already been created.

To do that we derrive from the ApplicationEventHandler class. Create a new class in the “controllers” folder and name it “Startup”. Add the following code:

using Umbraco.Core;
using Umbraco.Core.Persistence;
using UmbracoDatabase.Models.pocos;

namespace UmbracoDatabaseExample.Controllers
{
    public class Startup : ApplicationEventHandler
    {
        protected override void ApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
        {
            DatabaseContext ctx = ApplicationContext.Current.DatabaseContext;
            DatabaseSchemaHelper dbSchema = new DatabaseSchemaHelper(ctx.Database, ApplicationContext.Current.ProfilingLogger.Logger, ctx.SqlSyntax);

            //if (dbSchema.TableExist("somePrefixEmployee")) dbSchema.DropTable<Employee>();
            //if (dbSchema.TableExist("somePrefixStore")) dbSchema.DropTable<Store>();

            if (!dbSchema.TableExist("somePrefixStore")) dbSchema.CreateTable<Store>(false);
            if (!dbSchema.TableExist("somePrefixEmployee")) dbSchema.CreateTable<Employee>(false);
        }
    }
}

The last two lines create the database tables. If you change your model you need to drop and recreate the tables. For that you can uncomment the two middle lines and restart umbraco. This will also delete all data in that tables. So only use that during development and never on a life environment.

After starting (restarting) umbraco you could see the tables in SQL Managment Studio. If you want to check, they should look like this:

Umrbaco custom tables inspected
Umbraco custom tables inspected with SQL Server Mangement Studio

Adding something to our custom umbraco database

We have set up our database. Now we want to add some data. I will leave to you where you do that. I usually create an API deriving from UmbracoAuthorizedApiController to handle all operations on the database.

The functions of that API can then be used everywhere else in umbraco. For example in other C# controllers or in angularJS.

This controllers provides some basic fucntionallity to save and retrieve stores from the database:

public class StoreController : UmbracoAuthorizedApiController
{
    public List<Store> GetAll()
    {
        UmbracoDatabase db = ApplicationContext.Current.DatabaseContext.Database;
        return db.Fetch<Store>(new Sql().Select("*").From("somePrefixStore"));
    }

    public Store GetById(int id)
    {
        var query = new Sql().Select("*").From("somePrefixStore").Where<Store>(x => x.Id == id);
        return DatabaseContext.Database.Fetch<Store>(query).FirstOrDefault();
    }

    public Store Save(Store store)
    {
        if (store != null)
        {
            if (store.Id > 0)
            {
                DatabaseContext.Database.Update(store);
            }
            else
            {
                store.CreatedOn = DateTime.Now;
                store.UserId = Services.UserService.GetByUsername(HttpContext.Current.User.Identity.Name).Id;
                DatabaseContext.Database.Save(store);
            }
        }
        return store;
    }
}

Based on that you can build the functions that best serve your need.

The API controller for the Employee could look smiliar. Remember that you can’t save an employee without adding the foreign key StoreId.

If you have any wishes on how to extend this guide please leave me a comment.

2 COMMENTS

  1. Hi Nico,
    great article, thanks! However, we had to figure out many of these things ourselves. But good to read that we did things right 🙂

    We decided to handle our DB creation and migrations using Fluent Migrator [1] in combination with Fake [2] which offers a plugin to Fluent Migrator [3]. This allows us to have a nice DSL for preparing and modifying our Database Tables and embeds the DB process into our deployment chain. If we automatically or on-demand migrate our live database is a point still to be discussed. Would be great to hear from others how they are handling (or planning to handle) their DB migrations.

    [1] https://github.com/schambers/fluentmigrator
    [2] http://fsharp.github.io/FAKE/
    [3] http://fsharp.github.io/FAKE/fluentmigrator.html

LEAVE A REPLY

Please enter your comment!
Please enter your name here