Add migration step with sql script

Scenario: Wanted to add a new schema, table and stored procedure to the same database where CMS is installed. The script should run only once after deployment to an environment.

Using IMigrationSet can do this job, although is different from a Initialization Module that run every time the site starts up.

Interface IMigrationStep documentation: https://world.episerver.com/csclasslibraries/commerce/EPiServer.Commerce.Internal.Migration.Steps.IMigrationStep?version=13

Step 1. Create a folder in your project that is referenced by Episerver CMS website.

Step 2. Create two files: a class and sql script. For exemple:

Step 3. Define the content for you sql script. For good practices, we must use a different schema from the dbo where all the Episerver objects are defined. So, we need to create the following script :

--beginvalidatingquery
IF OBJECT_ID(N'[myschema].[myTable]', N'U') IS NOT NULL
   SELECT 0, 'Already installed'
else
   SELECT 1, 'Adding tables'
--endvalidatingquery

go

create schema [myschema]
go

create table [ownschema].[myTable] 
(
	[Id] [bigint] IDENTITY(1,1) not null,
	[Text] [nvarchar](200) not null,
	constraint [PK_myTable] primary key ([Id])
)
go

Step 4. Mark you script file as Embedded Resource.

Step 5. Define your migration step on your c# class.

using EPiServer.Commerce.Internal.Migration.Steps;
using EPiServer.Data.Providers.Internal;
using EPiServer.Data.SchemaUpdates;
using EPiServer.Logging;
using EPiServer.ServiceLocation;
using Mediachase.Commerce.Shared;
using System;
using System.IO;
using System.Reflection;

namespace Common.Migrations.CreateTablesStoredProcedures
{
    [ServiceConfiguration(typeof(IMigrationStep))]
    public class CreateTablesAndStoredProceduresMigrationStep : IMigrationStep
    {
        private static readonly ILogger Logger = 
            LogManager.GetLogger(typeof(CreateTablesAndStoredProceduresMigrationStep));
        private readonly IDatabaseConnectionResolver _databaseConnectionResolver;
        private readonly ScriptExecutor _scriptExecutor;

        public CreateTablesAndStoredProceduresMigrationStep(
            IDatabaseConnectionResolver databaseConnectionResolver,
            ScriptExecutor scriptExecutor)
        {
            _databaseConnectionResolver = databaseConnectionResolver;
            _scriptExecutor = scriptExecutor;
        }

        public int Order => 2000;

        public string Name => "Create Tables And StoredProcedures";

        public string Description => "Create Tables and Stored Procedures using Embedded Resource sql file ";

        public bool Execute(IProgressMessenger progressMessenger)
        {
            try
            {
                using (Stream installScript = GetInstallScript())
                {
                    _scriptExecutor.ExecuteScript(
                        _databaseConnectionResolver.Resolve().ConnectionString,
                        installScript);
                }

                return true;
            }
            catch (Exception ex)
            {
                Logger.Error("Error occurred while creating the tables and stored procedures.", ex);

                return false;
            }
        }

        private static Stream GetInstallScript()
        {
            const string scriptFqdn = "Common.Migrations.CreateTablesAndStoredProceduresMigrationStep.script.sql";

            Assembly assembly = typeof(CreateTablesAndStoredProceduresMigrationStep).Assembly;

            return assembly.GetManifestResourceStream(scriptFqdn);
        }
    }
}

Step 6. Deploy.

Note that this migration step will run only once if no exception occurred, this is, if the return value of the Execute method is true. If returned false, this migration step will be executed next time the site started.

An workaround on development mode to repeat this migration is always return false, but ensure that this does not go to production.

I got the case, that need to repeat this step, but already returned success. Used a not documented and not official way of removing the success result of this migration by deleting the row from [dbo].[tblBigTable]. I just found it during my tests, just warning you. So, I advise to this only on you local development database.

SELECT pkId, String01 as [Name]
  FROM [dbo].[tblBigTable] where StoreName = 'EPiServer.Commerce.Internal.Migration.MigrationStepInfo'
	and [String01] = 'Common.Migrations.CreateTablesAndStoredProceduresMigrationStep'


delete [dbo].[tblBigTable] where StoreName = 'EPiServer.Commerce.Internal.Migration.MigrationStepInfo'
	and [String01] = 'Common.Migrations.CreateTablesAndStoredProceduresMigrationStep'

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: