Entity Framework and Custom SQL in Optimizely/Episerver CMS

Optimizely CMS is pretty powerful.  It does basically everything you could want from a CMS.  Not gonna lie, it's a little clunky and sometimes finding a solution to a problem is super hard to figure out.  I recently had a need to consume an XML feed daily from a 3rd party source, store it somewhere, then create a search page to mine that data.  Ok, that doesn't sound too hard, except I needed to handle a bunch of mini-projects in order to get this done.

In this article, I'm just going to focus on the storing of data.  The Optimizely CMS comes with its Dynamic Data Store (DDS) to store data, however, it can be slow and working with it is a bit of a challenge at times.  After contemplating that solution, I settled on using the Entity Framework instead.  It's considerably faster and its implementation is familiar since I've used EF for years.  The bonus is that it's already installed with Optimizely.

SETUP

1. Create a new folder anywhere in your solution.  I called mine “DataStore”.
2. Create your models in there.  Here's an example called ZipCode.cs:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.Linq;
using System.Web;

namespace MySite.DataStore
{
    public class ZipCode
    {
        [Key]
        [MaxLength(16)]
        public string Zip { get; set; }
        [MaxLength(30)]
        public string City { get; set; }
        [MaxLength(30)]
        public string State { get; set; }        
        public decimal Latitude { get; set; }
        public decimal Longitude { get; set; }
        public int TimeZone { get; set; }
        [MaxLength(2)]
        public string Country { get; set; }
    }
}

Copy

3. Once you have all your models in place, we need to create the context.  Create a file to handle your context implementation.  I'm using ApplicationDbContext.cs.  Make sure to derive from DbContext and include the DatabaseConnectionName.  It can be a different DB if you want, but I just kept it all in the same DB as the rest of the application.

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;

namespace MySite.DataStore
{
    public class ApplicationDbContext : DbContext
    {
        private const string DatabaseConnectionName = "EPiServerDB";

        public ApplicationDbContext() : base(DatabaseConnectionName)
        {}

        protected override void OnModelCreating(System.Data.Entity.DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<ZipCode>().Property(o => o.Latitude).HasPrecision(10, 6);
            modelBuilder.Entity<ZipCode>().Property(o => o.Longitude).HasPrecision(10, 6);

            base.OnModelCreating(modelBuilder);
        }

        public DbSet<ZipCode> ZipCodes { get; set; }
    }
}

Copy

The EF context is setup, but now we need to create the tables in the DB.  If you have access to the DB directly, you could always just do it directly using SSMS, but if you're in the DXP environment or some other area where accessing the DB is off limits, you can create a migration within Optimizely.

4. Create a SQL file with all your sql logic.  At the top, it is mandatory that you create a query that validates the script.  This is what allows the script to run inside Optimizely.  Without it, you'll get this annoying error message: “Missing validation section in beginning of SQL script”.  Save this file within the same folder as everything else.  I called this: externaltables.sql

--beginvalidatingquery
IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'rates'))
   SELECT 1, 'Updating'
else
   SELECT 0, 'Already Done'
--endvalidatingquery


IF (NOT EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'dbo' 
                 AND  TABLE_NAME = 'rates'))
BEGIN

    BEGIN TRANSACTION

    CREATE TABLE [dbo].[ZipCodes](
        [Zip] [VARCHAR](16) NOT NULL,
        [City] [VARCHAR](30) NULL,
        [State] [VARCHAR](30) NULL,
        [Latitude] [DECIMAL](10, 6) NULL,
        [Longitude] [DECIMAL](10, 6) NULL,
        [TimeZone] [INT] NULL,
        [Country] [VARCHAR](2) NULL,
     CONSTRAINT [PK_ZipCodes] PRIMARY KEY CLUSTERED 
    (
        [Zip] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    -- other stuff here...

    COMMIT

END

Copy

Once you have this file set, open the Properties in Visual Studio.  Change the Build Action to “Embedded Resource” and ensure that the Copy to Output Directory is “Do not copy”.

5. Now we need to create a class that will run this sql script.  Create a new class in the same folder and call it: ExternalTablesMigration.cs.  The secret sauce here is the deriving the class from MigrationStep.  MigrationStep has the abstract method “AddChanges” that you need to override that handles all the work.

using EPiServer.Data.Providers.Internal;
using EPiServer.Data.SchemaUpdates;
using EPiServer.DataAbstraction.Migration;
using EPiServer.Logging;
using EPiServer.ServiceLocation;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Web;

namespace MySite.DataStore
{

    // secret sauce is: MigrationStep
    public class ExternalTablesMigration : MigrationStep
    {
        private static readonly ILogger Logger = LogManager.GetLogger(typeof(ExternalTablesMigration));
        private IDatabaseConnectionResolver _databaseConnectionResolver;
        private ScriptExecutor _scriptExecutor;

        public override void AddChanges()
        {
            try
            {
                _databaseConnectionResolver = ServiceLocator.Current.GetInstance<IDatabaseConnectionResolver>();
                _scriptExecutor = ServiceLocator.Current.GetInstance<ScriptExecutor>();

                using (Stream installScript = GetInstallScript())
                {
                    _scriptExecutor.ExecuteScript(
                        _databaseConnectionResolver.Resolve().ConnectionString,
                        installScript);
                }
            }
            catch (Exception ex)
            {
                Logger.Error("Error occurred while creating external tables.", ex);
            }
        }

        private static Stream GetInstallScript()
        {
            // You need to prefix your filename with the namespace of the folder it's in
            const string scriptFqdn = "MySite.DataStore.externaltables.sql";

            Assembly assembly = typeof(ExternalTablesMigration).Assembly;

            return assembly.GetManifestResourceStream(scriptFqdn);
        }
    }
}

Copy

The other key pieces in this script are the following:
IDatabaseConnectionResolver
ScriptExecutor

These two classes handle the execution of the script.

Once this is in place, the next time the site restarts, this will run.  Incidentally, it'll run each time the site starts up, which is no real problem, since the sql script will only run once, but I like clean code, so once this is run in all your environments, you can safely remove this class for future code pushes.

6. Finally, you can query against your data using Entity Framework

var _db = new ApplicationDbContext();
var z = _db.ZipCodes.FirstOrDefault(o => o.Zip == zipcode.ToString());

Copy

Yes!