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.

	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;
	using System.Threading.Tasks;

	namespace ProjectNamespace.Code
	{
	public class FileList : Dictionary<string, List<string>>
	{
	new public List<string> this[string key] {
	get
	{
	if (!ContainsKey(key))
	Add(key, new List<string>() { });
	return base[key];
	}
	set
	{
	base[key] = value;
	}
	}

	public FileList Concat(string name, string fileName)
	{
	this[name].Add(fileName);
	return this;
	}
	}
	}

view raw FileList.cs hosted with ❤ by GitHub

	using CsvHelper.Configuration;
	using ProjectNamespace.Code;
	using System;
	using System.Collections.Generic;
	using System.ComponentModel.DataAnnotations;
	using System.Linq;
	using System.Text;
	using System.Threading.Tasks;
	using Umbraco.Web;

	namespace ProjectNamespace.Models.Generated
	{
	public partial class FormChannelProgramSignup : IDataModel
	{
	public FormChannelProgramSignup()
	{
	dateAdded = DateTime.Now;
	}

	#region IDataModel Properties
	public string SourceUrl { get; set; }

	public string destinationUrl
	{
	get
	{
	return redirectPage.HasValue ? UmbracoContext.Current.ContentCache.GetById(redirectPage.Value).Url : redirect;
	}
	set
	{
	}
	}

	private FileList _fileList = new FileList();
	public FileList fileList
	{
	get
	{
	return _fileList;
	}
	set
	{
	_fileList = value;
	}
	}

	// Validate the Model.
	public void Validate()
	{
	Validators.ValidateRequired(this);

	if (!String.IsNullOrWhiteSpace(phoneNumber) && !Validators.IsPhoneValid(phoneNumber))
	throw new Exception("Please enter a valid Phone Number.");

	if (!String.IsNullOrWhiteSpace(emailAddress) && !Validators.IsEmailValid(emailAddress))
	throw new Exception("Please enter a valid Email Address.");
	}
	#endregion

	#region Custom Properties
	public string name { get { return "#" + id; } }

	public string fullName { get { return String.Format("{0} {1}", firstName, lastName); } }

	public string domain
	{
	get
	{
	return String.Format("http://{0}", System.Web.HttpContext.Current.Request.Url.Host);
	}
	}

	public string redirect { get; set; }

	public int? redirectPage { get; set; }
	#endregion
	}

	}

view raw FormChannelProgramSignup.cs hosted with ❤ by GitHub

	using ProjectNamespace.Code;
	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;
	using System.Threading.Tasks;

	namespace ProjectNamespace.Models
	{
	public interface IDataModel : IDataModelOverview
	{
	/// <summary>
	/// URL of the page that submitted the form.
	/// </summary>
	string SourceUrl { get; set; }
	/// <summary>
	/// URL of the page to redirect to on success.
	/// </summary>
	string destinationUrl { get; set; }
	/// <summary>
	/// List of files attached to the form.
	/// </summary>
	FileList fileList { get; set; }
	/// <summary>
	/// Validate that the model is valid.
	/// </summary>
	void Validate();
	}

	public class DataModel
	{
	/// <summary>
	/// Return an instance of a specified model by name.
	/// </summary>
	/// <param name="type">Model name</param>
	/// <returns>model</returns>
	public static IDataModel GetByType(string type)
	{
	return (IDataModel)Activator.CreateInstance(Type.GetType("ProjectNamespace.Models.Generated." + type, true));
	}
	}
	}

view raw IDataModel.cs hosted with ❤ by GitHub

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.

	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Linq.Expressions;
	using System.Text;
	using System.Threading.Tasks;
	using Umbraco.Core.Persistence;
	using Umbraco.Core.Persistence.Querying;
	using Umbraco.Core.Persistence.SqlSyntax;

	namespace ProjectNamespace.Extensions
	{
	public static class SqlExtensions
	{
	public static Sql Select<TColumn>(this Sql sql, params Expression<Func<TColumn, object>>[] columnMember)
	{
	var columns = columnMember.Select(s => s.Body.NodeType == ExpressionType.Convert ?
	((MemberExpression)((UnaryExpression)s.Body).Operand).Member.Name :
	((MemberExpression)s.Body).Member.Name
	).ToArray();

	return sql.Select(columns);
	}

	public static Sql AndWhere(this Sql self, string sql, ref bool hasWhere, params object[] args)
	{
	if (hasWhere)
	return self.Append("AND (" + sql + ")", args);
	hasWhere = true;
	return self.Where(sql, args);
	}

	public static Sql OrWhere(this Sql self, string sql, ref bool hasWhere, params object[] args)
	{
	if (hasWhere)
	return self.Append("OR (" + sql + ")", args);
	hasWhere = true;
	return self.Where(sql, args);
	}
	}
	}

view raw SqlExtensions.cs hosted with ❤ by GitHub

	using ProjectNamespace.Models;
	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;
	using System.Threading.Tasks;
	using Umbraco.Core.Persistence;

	namespace ProjectNamespace.Extensions
	{
	public static class UmbracoDatabaseExtensions
	{
	public static void Validate(this UmbracoDatabase self, IDataModel record)
	{
	record.Validate();
	}
	}
	}

view raw UmbracoDatabaseExtensions.cs hosted with ❤ by GitHub

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.

	using ProjectNamespace.Models.Generated;
	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Text;
	using System.Threading.Tasks;

	namespace ProjectNamespace.Models.Relators
	{
	public class ShopMarketRelator
	{
	internal ShopMarket Current;

	/// <summary>
	/// Many to Many mapping
	/// </summary>
	/// <param name="a">left</param>
	/// <param name="r">man to many</param>
	/// <param name="p">right</param>
	/// <returns></returns>
	public ShopMarket Map(ShopMarket a, ShopMarketService r, ShopService p)
	{
	// Terminating call. Since we can return null from this function
	// we need to be ready for PetaPoco to callback later with null
	// parameters
	if (a == null)
	return Current;

	// Is this the same DictionaryItem as the current one we're processing
	if (Current != null && Current.MarketId == a.MarketId)
	{
	r.ShopService = p;

	// Yes, just add this MacroPropertyDtos to the current item's collection
	Current.ShopMarketService.Add(r);

	// Return null to indicate we're not done with this Macro yet
	return null;
	}

	// This is a different Macro to the current one, or this is the
	// first time through and we don't have one yet

	// Save the current Macro
	var prev = Current;

	// Setup the new current Macro
	Current = a;
	Current.ShopMarketService = new List<ShopMarketService>();
	//this can be null since we are doing a left join
	if (r.MarketId != 0)
	{
	r.ShopService = p;
	Current.ShopMarketService.Add(r);
	}

	// Return the now populated previous Macro (or null if first time through)
	return prev;
	}

	/// <summary>
	/// Ont yo Many Mapping
	/// </summary>
	/// <param name="a"></param>
	/// <param name="p"></param>
	/// <returns></returns>
	internal ShopMarket Map(ShopMarket a, ShopZipcode p)
	{
	// Terminating call. Since we can return null from this function
	// we need to be ready for PetaPoco to callback later with null
	// parameters
	if (a == null)
	return Current;

	// Is this the same DictionaryItem as the current one we're processing
	if (Current != null && Current.MarketId == a.MarketId)
	{
	// Yes, just add this MacroPropertyDtos to the current item's collection
	Current.ShopZipcode.Add(p);

	// Return null to indicate we're not done with this Macro yet
	return null;
	}

	// This is a different Macro to the current one, or this is the
	// first time through and we don't have one yet

	// Save the current Macro
	var prev = Current;

	// Setup the new current Macro
	Current = a;
	Current.ShopZipcode = new List<ShopZipcode>();
	//this can be null since we are doing a left join
	if (p.State != null)
	{
	Current.ShopZipcode.Add(p);
	}

	// Return the now populated previous Macro (or null if first time through)
	return prev;
	}

	/// <summary>
	/// One to One Mapping
	/// </summary>
	/// <param name="p"></param>
	/// <param name="a"></param>
	/// <returns></returns>
	internal ShopMarketService Map(ShopService p, ShopMarketService a)
	{
	// Map the items directly.
	a.ShopService = p;
	return a;
	}
	}
	}

view raw ShopMarketRelator.cs hosted with ❤ by GitHub

In addition some partial classes are needed in your Models/Generated folder to add properties to store the related models.

	using Newtonsoft.Json;
	using System.Collections.Generic;
	using System.Linq;
	using Umbraco.Core.Persistence;

	namespace ProjectNamespace.Models.Generated
	{
	public partial class ShopMarket
	{
	[ResultColumn, JsonIgnore] // JsonIgnore if included means that the child data from this column wont be serialised and send down to Angualr.
	public List<ShopZipcode> ShopZipcode { get; set; }

	[ResultColumn]
	public List<ShopMarketService> ShopMarketService { get; set; }

	// Example helper method using related models.
	public string PrimaryZipCode
	{
	get
	{
	if (ShopZipcode != null && ShopZipcode.Where(s => s.IsPrimary).Count() > 0)
	return ShopZipcode.FirstOrDefault(s => s.IsPrimary).ZipCode;
	else
	return "";
	}
	}
	}
	}

view raw ShopMarket.cs hosted with ❤ by GitHub

	using Newtonsoft.Json;
	using System.Collections.Generic;
	using System.Linq;
	using Umbraco.Core.Persistence;

	namespace ProjectNamespace.Models.Generated
	{
	public partial class ShopMarketService
	{
	public ShopService ShopService { get; set; }
	}
	}

view raw ShopMarketService.cs hosted with ❤ by GitHub

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.

Jeremy Pyne

Tuesday, November 1, 2016