Getting ALL the PreValues

To extract all the values from a data type in umbraco, you will need to use the new Services to correctly extract them


umbraco.library is the previous way of extracting this information and will make it harder to upgrade your applications in the future

Querying data from a custom table inside the umbraco database

Dont use raw SQL

//Get the latest db connection
var ctx = ApplicationContext.DatabaseContext;
var sb = New DatabaseSchemaHelper(ctx.Database, ApplicationContext.ProfilingLogger.Logger, ctx.sqlSyntax);
//Build the query
var query = new Sql().Select(“your cols”).From(“your table”).Where(c => c.SomeProperty).OrderByDescending(c => c.someProperty, ctx.sqlSyntax);
//Run the query
var items – ctx.Database.Fetch(query)


Custom Tables with Umbraco Migration

There may be occasions in which we need to store data externally from the CMS but still consume it within our application without the overhead of creating and maintaining a second database.

Usually, I recommend having a second database for anything that isn’t a core CMS table, as upgrading versions of the application could be a lot trickier. Especially, if the framework changes.

Traditionally developers would create SQL scripts to create and maintain custom tables. However, with the introduction of Migrations in Umbraco 6, it enables developers to put their schema changes directly into source code.

Within your CMS database, there is a table called umbracoMigrations and this tracks all of the migrations that have been made on the application since the beginning. When a new version of the Umbraco assemblies are released into the application, this is the first place that is checked to see what the current version is.

This tutorial doesn’t cover umbraco CMS migrations, but the version data is stored in the same location.

Developer Note: The examples here are using Umbraco 7.4.3, C# and PetaPOCO. I’m telling you this there are examples on the web that use depreciated instantiations. 

If this is  your first time to code migrations I would recommend reading Code First Migrations on the Data Developer Center.  

Getting started

Umbraco uses an ORM called PetaPoco which we will be using to create a basic example. Create your model, which will be used to create the table in the database.

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

namespace MyNameSpace
 [PrimaryKey("MyId", autoIncrement = true)]
 public class MyClass
 [PrimaryKeyColumn(AutoIncrement = true)]
 public int MyId { get; set; }

 public string MyProperty { get; set; }

Notice that we are decorating the class with data annotations that will tell the ORM what to create in the database. This will also help with mapping data when extracting back of the table.


Now we the property mapped we need to create a migration to tell the ORM the order in which to play migrations

using Umbraco.Core;
using Umbraco.Core.Logging;
using Umbraco.Core.Persistence;
using Umbraco.Core.Persistence.Migrations;
using Umbraco.Core.Persistence.SqlSyntax;

namespace MyNameSpace
 [Migration("1.0.0", 1, "MyTable")]
 public class CreateMyClassV1_0_0 : MigrationBase
    private readonly UmbracoDatabase _database = ApplicationContext.Current.DatabaseContext.Database;
    private readonly DatabaseSchemaHelper _schemaHelper;

    public CreateMyClass1_0_0(ISqlSyntaxProvider sqlSyntax, ILogger logger) 
     : base(sqlSyntax, logger)
       _schemaHelper = new DatabaseSchemaHelper(_database, logger, sqlSyntax);
     public override void Up()
       //Pass the class to the schema and do not over write anything that exists
     public override void Down()
     { }

The Migration attribute at the top of the class is the same one that is used within an umbraco upgrade. Its telling the ORM a few things

 [Migration(<Version>, <order>, <tableName>)]

When this is run, it will look in the umbracoMigrations table for the version and the table name, to ensure that it hasn’t already been run. If this were to happen, it could result in data loss. #Backup. The order attribute is used when there are multiple migrations that need to be run on the same table.

The rest of the class it creating a database context and a database schema helper from the current connection string that is in web.config to which we can pass our property.

As part of the migration base class that your migration is extending , there are two methods that need to be overridden. Up and Down.

Up will be all of the changes that will be added or modified to the table and Down will be all those that are being removed.

In the first instance, we are passing out class to the handler, which will read all of the properties in the class and create them using the attributes we used earlier, (not the property names), then we are passing false to ensure that the table is only created once.



To ensure that all changes to the database are readily available, we need to run them on application start up (usually when we deploy our code and the app pool reboots) so that the application doesn’t throw any exceptions. The easiest way is with an application handler. Umbraco has great documentation for this.

This is where my post differs. A lot of the current tutorials use deprecated class instantiation. The compiler should tell you this (or press F12 on the definition to see the interface).

Developer Note: I have removed logic gates and general exception handling for clarity. You should amend this code to ensure that you are throwing the appropriate exceptions and ensure your code is defensive.

using System.Configuration;
using Umbraco.Core;
using Umbraco.Core.Persistence.Migrations;
using Umbraco.Core.Services;
using Umbraco.Core.Logging;
using Umbraco.Core.Persistence.SqlSyntax;
using Semver;
using System;
using System.Linq;

namespace MyNamespace
   public class MyMigrationEvent : ApplicationEventHandler
     protected override void ApplicationStarted(UmbracoApplicationBase umbracoApplication, ApplicationContext applicationContext)
        HandleMySuffFolfderMigration(applicationContext.DatabaseContext.SqlSyntax, applicationContext.Services.MigrationEntryService, applicationContext.ProfilingLogger.Logger);
 private static void MyClassMigration(ISqlSyntaxProvider sqlSyntax, IMigrationEntryService migrationEntryService, ILogger logger)
     string tableName = "MyTable";

     SemVersion currentVersion = GetCurrentVersion(tableName);
     SemVersion targetVersion = ParseVersion("1.0.0");

 var scriptsForMigration = new IMigration[] { new CreateMyClassV1_0_0 (sqlSyntax, logger), 
                                               new CreateMyClassV1_0_0 (sqlSyntax, logger) };

 RunMigration(sqlSyntax, migrationEntryService, logger, 
 scriptsForMigration, currentVersion, targetVersion, tableName);

 private static SemVersion GetCurrentVersion(string tableName)
 var migrations = ApplicationContext.Current.Services.MigrationEntryService.GetAll(tableName);
 var latestMigration = migrations.OrderByDescending(x => x.Version).FirstOrDefault();

 if (latestMigration == null)
 return ParseVersion("0.0.0");

 return ParseVersion(latestMigration.Version.ToString());

 private static SemVersion ParseVersion(string input)
 return SemVersion.Parse(input);

 private static void RunMigration(ISqlSyntaxProvider SqlSyntax, IMigrationEntryService migrationEntryService, ILogger logger, IMigration[] classesForMigration, SemVersion currentVersion, SemVersion targetVersion, string productName)
 MigrationRunner mRunner = new MigrationRunner(migrationEntryService, logger, currentVersion, 
 targetVersion, productName, classesForMigration);
 mRunner.Execute(ApplicationContext.Current.DatabaseContext.Database, true);

I have declared each parameter explicitly, refactoring can take place after.

To start the migration, you will need to pass all of the items from the context into the migration method, ISqlSyntaxtProvider, IMigrationEntryService, ILogger. All of which can be found in the applicationContext.

Next you need to create two versions, your current version and your target version.

You must return these as SemVer and not Version which is built into the .Net framework. 

When you set the current version, its recommended to look this up directly from the umbracoMigrations table using the MigrationEntryService.

Next is creating an array of IMigration. This is all of the migration classes that need to be run against your table. Remember the order attribute that was set? This is the order in which the migration scripts will be run.

[Migration(<Version>, <order>, <tableName>)]

If you do have multiple migration scripts for any changes, ensure that the order and the version number are correctly set for each migration. An entry will be created in the database and they will be run in order.

Then create an array off all the scripts

 var scriptsForMigration =
     new IMigration[] { new CreateMyClassV1_0_0 (sqlSyntax, logger), };

Now you have everything you need to give to the migration runner.

 MigrationRunner mRunner = 
    new MigrationRunner(migrationEntryService, logger, currentVersion, 
                           targetVersion, productName, classesForMigration);
 mRunner.Execute(ApplicationContext.Current.DatabaseContext.Database, true);

Now rebuild your project and run locally. Once the application is back up locally, you need to check that your database table has been created in the Umbraco CMS database and that a new enter has been made in the umbracoMigrations table.

Future changes.

Now that you have the migration infrastructure in place, making further changes is straight forward.

Create a new class that extends migration base. You may need to suffix the class name with the version number if it is in the same name space.

 [Migration("1.0.1", 2, "MyTable")]
 public class CreateMyClassV1_0_1 : MigrationBase

Remember to change the version number and order number.
Go into the migration event and add it to the list

 var scriptsForMigration = new IMigration[] { 
                                  new CreateMyClassV1_0_0 (sqlSyntax, logger), 
                                  new CreateMyClassV1_0_1 (sqlSyntax, logger)};

Then change your target version to the new version

 SemVersion targetVersion = ParseVersion("1.0.1");

Compile the application, deploy locally, check the umbracoMigration and the table for the new changes.

Now you can check your files into source control, so when they are deployed to your next environment, they will automatically run in order and up to the last version that is specified.

Remember to back up before making any database changes

umbraco 7.4.1 and azure blob storage


Azure Blob storage Module

As part of the 7.4.1 upgrade, Umbraco have changed the way in which images are stored in the database. In previous version of the Core, images were served as absolute URLs to the media item, however, these are now stored as a JSON object.

Stored without image focus points

{"src": "", crops: []}

Stored with image focus points

  "focalPoint": {
    "left": 0.5,
    "top": 0.5
  "src": ""

After upgrading there maybe instances where your local image library has lost the image thumbnails when trying to attach an image using the media picker. This is due to the upgrade process not modifying the old data to the new column. In the cmsPropertyData table, DataNvarchar was still populated with a physical URL to the image an

If the image wasn’t in the correct URL format, it would fall back to using

As the image isn’t local to the project, the url could not be used to generate a thumbnail.

Settings > Media Types > Image > Upload Image > change type to Image Cropper

Running the SQL below, should format the urls into the required format to be able to view the images.

UPDATE cmsPropertyData 
SET cmsPropertyData.dataNtext = Formatted.dataNtextCol
	SELECT TOP(1000),cmsPropertyData.propertytypeid, cmsPropertyData.contentNodeId, '{"src": "' + cmsPropertyData.dataNvarchar  + '", crops: []}' as dataNtextCol
	FROM cmsPropertyData
	WHERE (cmsPropertyData.propertytypeid = 135 AND cmsPropertyData.dataNvarchar is not null AND cmsPropertyData.dataNvarchar  '' AND dataNtext is null) 
	   OR (cmsPropertyData.propertytypeid = 6 AND cmsPropertyData.dataNvarchar is not null AND cmsPropertyData.dataNvarchar  '' and dataNtext is null)
	   ORDER BY cmsPropertyData.contentNodeId
) as Formatted

UPDATE cmsPropertyData 
SET cmsPropertyData.dataNvarchar = NULL
	SELECT TOP(1000), cmsPropertyData.propertytypeid, cmsPropertyData.contentNodeId, '{"src": "' + cmsPropertyData.dataNvarchar  + '", crops: []}' as dataNtextCol
	FROM cmsPropertyData
	WHERE (cmsPropertyData.propertytypeid = 135 AND cmsPropertyData.dataNvarchar is not null AND cmsPropertyData.dataNvarchar  '' AND dataNtext is not null) 
	   OR (cmsPropertyData.propertytypeid = 6 AND cmsPropertyData.dataNvarchar is not null AND cmsPropertyData.dataNvarchar  '' and dataNtext is not null)
	    ORDER BY cmsPropertyData.contentNodeId
) as Formatted

Umbraco image library and SVG images

In previous versions of the CMS core, all links to images have been stored as a single text string which would mean that developers wouldn’t have to check for a type of media image that was consumed on an Umbraco page.

With the introduction of the image cropper type, the way in which images are stored in umbraco have changed.

There are two ways in which images are stored in the cmsPropertyData table
With no focus points

{ "src": "", crops: [] }

With focus points

{ "focalPoint": { "left": 0.5, "top": 0.5 }, "src": "" }

If you look in the cmsPropertyType table, this should effect types Upload image (6) and Thumbnail Image (135)

In your umbraco project, you would use the following code to extract the new image.

UmbracoHelper umbracoHelper = new Umbraco.Web.UmbracoHelper(Umbraco.Web.UmbracoContext.Current);
var mediaItem = umbracoHelper.Media(content.GetPropertyValue(fieldName));
Var imageSrc = mediaItem.umbracoFile.src

This works fine as long as you are not consuming any media item that falls under the .jpg, .png, .gif. Umbraco detects the SVG format as a file type and sets the cmsPropertyData Upload file(24) to a physical URL of the file that needs to be used.

This now depending on how you serve your images to your templates. I use an extension method for the IPublishedContent and a fieldName, which I can call in my views.

Here I need to check the type of object that is being brought back via the IPublishedContent. The new image cropper images will return an object of Umbraco.Web.Models.ImageCropDataSet and an SVG will return a string.
Calling the src field name on an mediaItem.umbracoFile when returning an SVG will throw an exception.

I have chosen to check the type of what is being returned. Check out this great post to get a good example of looking up a type in a switch that doesnt use heavy reflection.

We have added the following code to the extension method.

var ts = new TypeSwitch()
    .Case((Umbraco.Web.Models.ImageCropDataSet x) => result = mediaItem.umbracoFile.src)
   .Case((string x) => result = mediaItem.umbracoFile);


We can now pick either an SVG or an image from the media library, it wont throw an exception as you will be returning either the src of the image cropper object, or the URL of the uploaded file type.

Related Links!

If you need to combine internal links and external links in the same Umbraco picker, you will need to use the related links picker.

This will generate a JSON array. This is how you extract the links on the other end.


var obj = JsonConvert.DeserializeObject<JArray>(test);

foreach (var a in obj)
var type = a.Value<string>(“type”);

var newWindow = a.Value<bool>(“newWindow”);
var title = a.Value<string>(“title”);

if (type.IsNullOrWhiteSpace() == false)
if (type == “internal”)
var linkId = a.Value<int>(“internal”);
var link = Umbraco.NiceUrl(linkId);
<li><a href=”@link” @(newWindow ? “target=_blank” : “”)>@title</a></li>

if(type == “external”)
var linkTitle = a.Value<string>(“caption”);
var link = a.Value<string>(“link”);
<li><a href=”@link” @(newWindow ? “target=_blank” : “”)>@title</a></li>


public static MvcHtmlString RelatedLinksGenerator(this JToken source, string urlCSS)
var helper = new UmbracoHelper(UmbracoContext.Current);
var type = source.Value(“type”);
var newWindow = source.Value(“newWindow”);
var title = source.Value(“title”);

var anchor = new TagBuilder(“a”);
string link = string.Empty;

var linkId = source.Value(“internal”);
link = helper.NiceUrlWithDomain(linkId);

var linkTitle = source.Value(“caption”);
link = source.Value(“link”);

anchor.MergeAttribute(“href”, link);
anchor.MergeAttribute(“class”, urlCSS);

return MvcHtmlString.Create(anchor.ToString(TagRenderMode.Normal));