Guide to Custom Database Access in Umbraco 7 With Built in PetaPoco

Though there are a lot of ways to write custom database code, and in many place in Umbraco custom tables my no be needed as the CMS can handle most any custom data needs. There are reasons and use cases for using custom database tables and code including:

• Reading data from external systems.
• Large datasets that we don’t want to clog out cache’s and Umbraco tree with.
• Performance sensitive querying where the Document -> Property models setup may not be preferable.
• Complex cross-joins and many-to-many relationships that may not be easy to replicate in Umbraco.
• Customer data, log data, or “write once” style form submissions where the data need not be in Umbraco trees.
• Data that may need to be easily accessed from external systems. 
• They can also use public Web API methods or custom views that read from the Umbraco core tables but writing data this was it less safe.

Though there are a lot of ways to do the database code and everyone has their own preferences, this guide is using the same PetaPoco system that Umbraco uses internal.  This has a few key benefits:

• Very lightweight system yet powerful.
• Fully compatible with Umbraco models, methodologies, and serialization.
• Easy to implement and generate models.
• All code is using existing database code already present in the Umbraco Core to minimize overhead.

The system can be used in one of two ways.  You can create all our database models(POCOs) in .NET and then have Application Startup code that creates or updates any missing database objects or you can create all the database objects yourself in the database using standard management tools and have PetaPoco automatically generate models(POCOs) based off that database.

We are going to be doing the second method in this article as I find it easier to maintain and design with but this post has some example of working the other way.  Either way the second section of this article describes querying and extending models which may still be helpful.

Automatically Generating POCO’s from a Database

• First of you will need to install the PetaPoco package from NuGet into your project or into a separate depended project.
• If you are using a separate project it will need to reference the Umbraco.Core package.
• Once installed you need to locate the Models/PetaPoco.cs file and exclude it from your project.  This is redundant as it is already included in the Umbraco dll’s and would cause confusion.
• You will need to add your Connection String to the web.config file if it’s different then the Umbraco database and update the ConnectionStringName below.
• If using a separate project you will also need to copy the umbracoDbDSN or your custom connection string to that project’s app.config file.
• You will need to open up the Models/Generated/Database.tt file and make some configuration changes.  These settings below should get you started.
• Make sure you change the ProjectNamespace to match what is set in the Default namespace of your projects properties.

    // Settings
    ConnectionStringName = ""; // Uses last connection string in config if not specified
    Namespace = "ProjectNamespace.Models.Generated";
    RepoName = "";
    GenerateOperations = false; // Don’t need these.
    GeneratePocos = true; // We definitely want these.
    GenerateCommon = false; // Don’t need any of this code.
    ClassPrefix = "";
    ClassSuffix = "";
    TrackModifiedColumns = false; // We shouldn’t need change tracking logic for most things as we aren’t creating an entire world here.  You can use it if needed though.
    ExplicitColumns = true;
    ExcludePrefix = new string[] {"cms", "CMS", "umbraco"}; // This is telling the generator to exclude the Umbraco tables from the generated code.
    IncludeViews = true; // You can create custom views to pull data or some CMS data is desired.

    // Read schema
    var tables = LoadTables();
    tables["SomeTable"].Ignore = true;  // This is how we can suppress other tables.

• Now you can save the Database.tt file and Visual Studio will automatically generate the models.  You can expand the Databse.tt file and select the resulting Database.cs file to review the generated code.

Extending Models with extra logic

You can add partial class’s in the Generated folder to extend your models and even attach interfaces and validation code.  Additional properties will not affect the database queries due to the explicit columns option.

This sample shows using an interfaces, adding custom properties, using validation, and setting default values.

Examples of querying and updating the POCO’s in Umbraco

There are many variations on this code but here are some brief examples.  More samples are available here.

using Umbraco.Core.Persistence;
using Umbraco.Core.Persistence.DatabaseModelDefinitions;
using ProjectNamespace.Models.Generated;
using ProjectNamespace.Extensions;

// Fetch a set of rows with sorting, filtering, and pageing.
public IEnumerable<object> GetChildren(string orderBy, Direction orderDirection, string filter, long page, long itemsPerPage)
{
    if (orderBy == "fullName")
        orderBy = "(firstName + ' ' + lastName)";

    var sql = new Sql();

    if (!String.IsNullOrEmpty(filter))
        sql.Where(@"'#' + convert(nvarchar, id) = @0
OR CONVERT(nvarchar(30), dateAdded, 101)  LIKE @1
OR (firstName + ' ' + lastName) LIKE @1
OR companyName LIKE @1, filter, '%' + filter + '%');

    if (orderDirection == Direction.Ascending) sql.OrderBy(orderBy); else sql.OrderByDescending(orderBy);

    try
    {
        if (page > 0 && itemsPerPage > 0)
            return DatabaseContext.Database.Fetch<FormChannelProgramSignupOverview>(page, itemsPerPage, sql);
        else
            return DatabaseContext.Database.Fetch<FormChannelProgramSignupOverview>(sql);
    }
    catch (Exception e)
    {
        throw new Exception(ApplicationContext.DatabaseContext.Database.LastSQL, e);
    }
}

// Get a single item.
DatabaseContext.Database.SingleOrDefault<FormChannelProgramSignup>(id);

// Only load parts of a model.
var sql = new Sql()
    .Select<FormChannelProgramSignup>(s => s.id, s => s.firstName, s => s.lastName);
DatabaseContext.Database.Fetch<FormChannelProgramSignup>(sql);
// Note that instead of doing this it is far similar to create views in the database that select the columns you need.  They will generate their own POCO’s and you can query those instead.  This also means that only the loaded properties will be serialized to JSON code.

// Complex conditionals, this is much cleaner than trying to do the same thing with a single string.
var hasWhere = false;
var sql = new Sql();
if (!String.IsNullOrEmpty(filter["search"]))
    sql.AndWhere("'#' + convert(nvarchar, id) = @0 OR companyName like @1", ref hasWhere, filter["search"], '%' + filter["search"] + '%');
if(!String.IsNullOrEmpty(filter["dateStart"]))
    sql.AndWhere("dateCreated >= @0", ref hasWhere, DateTime.Parse(filter["dateStart"]));
if (!String.IsNullOrEmpty(filter["dateEnd"]))
    sql.AndWhere("dateCreated <= @0", ref hasWhere, DateTime.Parse(filter["dateEnd"]));

// Insert, update, delete, and validation examples.  Again there are many more methods available.
var record = isNew ? new FormChannelProgramSignup() : DatabaseContext.Database.SingleOrDefault<FormChannelProgramSignup>(id);
record.lastName = “test”;
DatabaseContext.Database.Validate((IDataModel)record);
DatabaseContext.Database.Save(record);
DatabaseContext.Database.Delete(record);

These examples use these some extra helpers.

Implementing One to One, One to Many, and Many to Many Joins

This can get a bit complicated to understand, but when set up correctly it works quite well.  In short you can do joins in the SQL code but need to create Relator code and attach those to the fetch code so that the rows are disassembled into associated models in the way you expect.

The following examples are assuming the following Schema.

• ShopMarkets
• MarketId
• Region
• Market
• ShopMarketServices
• MarketId
• ServiceId
• ShopServices
• ServiceId
• ShopZipcodes
• City
• State
• ZipCode

This is a relator that includes all three types of relationships. These should be placed in a Models/Relators folder in your project.

In addition some partial classes are needed in your Models/Generated folder to add properties to store the related models.
To use it you would use the following SQL code.  Note that these are all using the same Map function with different signatures but you could use different function names if you prefer for clarity.

using Umbraco.Core.Persistence;
using ProjectNamespace.Models.Generated;
using ProjectNamespace.Models.Relators;

// One to Many Query
var sql = new Sql().Select("*").From<ShopService>(DatabaseContext.SqlSyntax)
    .LeftJoin<ShopMarketService>(DatabaseContext.SqlSyntax)
    .On<ShopService, ShopMarketService>(DatabaseContext.SqlSyntax, s => s.ServiceId, s => s.ServiceId)
    .Where<ShopMarketService>(s => s.MarketId == Current.MarketId);
DatabaseContext.Database.Fetch<ShopService, ShopMarketService, ShopMarketService>(new ShopMarketRelator().Map, sql);

// One to Many Query
var sql = new Sql().Select("*")..From<ShopMarket>(DatabaseContext.SqlSyntax)
    .LeftJoin<ShopZipcode>(DatabaseContext.SqlSyntax)
    .On("ShopMarkets.Region = ShopZipcodes.State AND ShopMarkets.Market = ShopZipcodes.City") // Can use .On<T1, T2>(left => left.Field, right => right.field) on similar joins.
    .Where<ShopMarket>(s => s.Region.InvariantEquals(CurrentCode) && s.Market.InvariantEquals(CurrentMarket));
DatabaseContext.Database.Fetch<ShopMarket, ShopZipcode, ShopMarket>(new ShopMarketRelator().Map, sql).FirstOrDefault();  // Must use Fetch().FirstOrDefault() here as Database.FirstOrDefault doesn’t have the proper signatures.

// Many to Many Query
var sql = new Sql().Select("*").From<ShopMarket>(DatabaseContext.SqlSyntax)
    .LeftJoin<ShopMarketService>(DatabaseContext.SqlSyntax)
    .On<ShopMarket, ShopMarketService>(DatabaseContext.SqlSyntax, s => s.MarketId, s => s.MarketId)
    .LeftJoin<ShopService>(DatabaseContext.SqlSyntax)
    .On<ShopMarketService, ShopService>(DatabaseContext.SqlSyntax, s => s.ServiceId, s => s.ServiceId)
DatabaseContext.Database.Fetch<ShopMarket, ShopMarketService, ShopService, ShopMarket>(new ShopMarketRelator().Map, sql).FirstOrDefault();

Then you can use the added properties to access associated data. In my tests the Select(“*”) part was required or invalid SQL was generated but that may be fixed at some point.