Using Umbraco NPOCO Black magic
Today I had a requirement to create 2 new database tables for a new Umbraco 10 project that I'm working on at work.
Creating these two tables was a relatively straight forward job to do, following the Umbraco documentation and also some internal projects that I had available to me. However, I also had to create a stored procedure for these 2 tables.
This became problematic and so I reached out to the Umbraco Community for help.
This project is a migration from a Sitecore site on to Umbraco and so a lot of the functionality and database tables need moved from their current setup to Umbraco. This meant I had the store procedure already created in SQL Server.
To create the script I used SQL Server Management Studio to create the script and save it to my clipboard :
I then pasted the script in to Visual Studio Code and saved it as a .sql file.
If I run this file within SQL Server Management Studio (SSMS) then it would create the stored procedure for me, however, I don't want to have to create a Readme file for everyone and hope that they remember to run the script the first time they run this project. I also didn't want to have to remember to run this script on each environment that this site is on e.g. Dev, UAT, Staging and Live.
That's just not realistic.
Let's get coding!
Umbraco comes with, as a friend explained it to me, some black magic under the bonnet that developers can use.
We can use something called migrations which hook in to NPOCO - this means we can write code to create our database tables AND stored procedures. They are executed when Umbraco loads up which means once they are created, everything is setup automagically.
I have a file called CreateTideStoredProcedure.cs and it my first incarnation of this file looked like this:
using Our.Site.Core.Constants;
using Our.Site.Core.MigrationRunner;
using Our.Site.Core.TideTimesAndDates.Migrations.Models;
using Microsoft.Extensions.Logging;
using Umbraco.Cms.Infrastructure.Migrations;
namespace Our.Site.Core.TideTimesAndDates.Migrations
{
public class TideStoredProcMigrationPlan : MigrationPlan
{
public const string MigrationName = Constants.Constants.Site + "-Migrations-TideStoredProc";
public TideStoredProcMigrationPlan() : base(MigrationName)
{
From(string.Empty)
.To<CreateTideStoredProc>(CreateTideStoredProc.State);
}
public class CreateTideStoredProc : MigrationBase
{
public const string State = "Tide-Stored-Proc-Migrations-initial";
public CreateTideStoredProc(IMigrationContext context)
: base(context)
{
}
protected override void Migrate()
{
Logger.LogDebug("Running migration {MigrationStep}", "AddTideTimeStoredProc");
if (TableExists(TideTimesInitialMigrationModel.TableName) && TableExists(TideDatesInitialMigrationModel.TableName))
{
Database.Execute("SET ANSI_NULLS ON" +
"GO" +
"SET QUOTED_IDENTIFIER ON" +
"GO" +
"CREATE PROC [dbo].[importTideDataRow]" +
"@marinaId int" +
",@year nvarchar(16)" +
",@month nvarchar(16)" +
// Add another 100+ lines of code like this...
}
}
}
}
}
As you can see in the Database.Execute method, this was looking ugly and it felt 'wrong' to me. So at this point I asked the Umbraco Community for some help.
Nik suggested that I add the stored procedure as a resource within my project and just read the entire file in to the Database.Execute method. This sounded doable and with some pointers from him I created an Extension method called GetResourceExtension.cs
namespace Our.Site.Core.Extensions
{
public static class GetResource
{
public static string ReadAsString(string resourceName, string suffix = "txt")
{
using (var resourceStream = typeof(GetResource).Assembly.GetManifestResourceStream($"Our.Site.Core.TideTimesAndDates.Resource.{resourceName}.{suffix}"))
{
if(resourceStream == null)
{
throw new InvalidOperationException($"Couldn't find resource '{resourceName}'.");
}
return new StreamReader(resourceStream, Encoding.UTF8).ReadToEnd();
}
}
}
}
The GetManifestResourceStream is passed a path to the resource file - The path looks like this : Our.Site\Core\TideTimesAndDates\Resource\filename.sql
But, that's not all you need to do, you need to make sure that the resource is set as an Embedded Resource within Visual Studio. 
If you don't do this then it makes no difference to your code, you will not find the resource. I spent a lot of time checking and double checking my path and the spelling of the path!
This is not the end though!
What next?
With the path working, I was then getting error messages.
Parameter '@id' specified but none of the passed arguments have a property with this name
With a quick search I found I needed to double `@` so a quick find and replace, that fixed that issue. Every `@` in the stored procedure was converted to `@@`
The next error was:
'CREATE/ALTER PROCEDURE' must be the first statement in a query batch
This one was a tricky one and took some screen sharing and trial and error. We were almost at the end of trying everything but then Nik found a Stack Overflow post which sounded like it might help.
Wrap the stored procedure in
exec(' ..... ')but, you also need to convert all single quotes, except for the first and last single quotes to double single quotes, so ` to ``.
What needed changed in CreateTideStoredProcedure.cs?
To read in the Stored Procedure file, I used the extension method ReadAsString and then passed that to the Database.Execute method which when I ran the site, like magic, the stored procedure appeared on the database!
protected override void Migrate()
{
Logger.LogDebug("Running migration {MigrationStep}", "AddTideTimeStoredProc");
if (TableExists(TideTimesInitialMigrationModel.TableName) && TableExists(TideDatesInitialMigrationModel.TableName))
{
var sql = GetResource.ReadAsString("ImportTideDataStoredProc", "sql");
Database.Execute(sql, System.Data.CommandType.Text);
}
}
I hope this is useful to someone.
Thanks Nik, Matt and Paul for your comments and help with this. #h5yr
Any questions, please feel free to drop me a message on Twitter or Mastodon
Published on: 08 February 2023