C# App That Will Wipe Your Episerver Database and Re-install It

Recently, I've been working with a client who decided to use DXC for their Episerver hosting option.  For those of you who haven't bumped into DXC yet, you can read a little bit more about it here.  As part of the client's continuous integration and testing process, we wanted the ability to automatically deploy a fresh/clean database into the 'integration' environment.  This made life a little more complicated than originally thought.  

DXC doesn't provide you with create/delete database permissions, so the options to write the database on deployment were somewhat limited.  For those of you wondering, all content etc...  was done in pre-prod.  Integration was just the environment to test new page/blocks/API calls where we used custom C# code to build pages/blocks.  Granted there are a few different options to do this, which is outside the scope of this tutorial but that was the original requirement and it ended up working quite well.

An Episerver Custom Deployment Application

To solve the problem I decided to create a little C# app that was published via Octpus in the CI build and run against the in-house dev and staging environments (as well as DXC integration).  The app uses a few third party packages, namely dbup and Microsoft.SqlServer.SqlManagementObjects.  It also uses a cool SQL script that Sergii Vorushylo told me about to wipe all the tables/SPROC's/keys etc..

The Code

    using System;
    using System.Configuration;
    using System.Diagnostics;
    using System.IO;
    using System.Reflection;

    using DbUp;
    using DbUp.Helpers;

    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo;

    class Program
    {
        static void Main(string[] args)
        {
            var connectionString = ConfigurationManager.ConnectionStrings["EPiServerDB"].ConnectionString;

            try
            {
                var sqlServer = ConfigurationManager.AppSettings["SqlServerConnection"];
                Console.Write(sqlServer);

                var connection = new ServerConnection(sqlServer);
                var server = new Server(connection);

                var databaseName = ConfigurationManager.AppSettings["DatabaseName"];
                TryDeleteDatabase(server, databaseName);
                EnsureDatabase.For.SqlDatabase(connectionString);

                var path = Path.GetFullPath(Path.Combine(Path.GetDirectoryName(Directory.GetCurrentDirectory()), "..\\..\\"));

                // Delete Database
                RunFreshSqlScript(connectionString, $@"{path}\DatabaseSetup\Scripts");

                var assembly = Assembly.LoadFrom("EPiServer.dll");
                var version = $"{assembly.GetName().Version.Major}.{assembly.GetName().Version.Minor}.{assembly.GetName().Version.Build}";

                // Install Epi tables
                RunFreshSqlScript(connectionString, $@"{path}\packages\EPiServer.CMS.Core.{version}\tools\");

                ExecuteCommand($@"{path}\EPiUpdatePackage\update.bat {path}\TSC.Website");
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }

        private static void TryDeleteDatabase(Server server, string databaseName)
        {
            try
            {
                if (server.Databases[databaseName] != null)
                {
                    server.KillDatabase(databaseName);
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }

        private static void ExecuteCommand(string command)
        {
            var processInfo = new ProcessStartInfo("cmd.exe", "/c " + command);
            processInfo.CreateNoWindow = true;
            processInfo.UseShellExecute = false;
            processInfo.RedirectStandardError = true;
            processInfo.RedirectStandardOutput = true;

            var process = Process.Start(processInfo);

            process.OutputDataReceived += (object sender, DataReceivedEventArgs e) => Console.WriteLine("output>>" + e.Data);
            process.BeginOutputReadLine();

            process.ErrorDataReceived += (object sender, DataReceivedEventArgs e) => Console.WriteLine("error>>" + e.Data);
            process.BeginErrorReadLine();

            process.WaitForExit();

            Console.WriteLine("ExitCode: {0}", process.ExitCode);
            process.Close();
        }

        private static void RunFreshSqlScript(string connectionString, string scriptPath)
        {
            try
            {
                var upgrader = DeployChanges.To.SqlDatabase(connectionString)
                    .WithScriptsFromFileSystem(scriptPath)
                    .LogToConsole()
                    .WithTransaction()
                    .JournalTo(new NullJournal())
                    .Build();
                var result = upgrader.PerformUpgrade();
                Console.Write(result.Successful);
            }
            catch (Exception e)
            {
                Console.WriteLine(e);
            }
        }
    }

Hopefully, the code is semi-obvious to understand, The app is semi-simple. First, it deletes the epi database (if it has permissions), it tries to drop all active SQL connections if they exist, it then runs the SQL script to wipe all data, tables, etc..  it then uses reflection to get the current version of Episerver the site uses and then uses that version number to copy the install SQL script and the upgrade SQL scripts needed from the packages directory into the app. The program will then run the install core Epi database script, and then it calls the 'Export-EPiUpdates' batch file to call the epideploy.exe and patch the database.  

The code also uses an after-build task in the csproj file.  I'll include it:

  
  <UsingTask TaskName="TransformXml" AssemblyFile="$(SolutionDir)packages\SlowCheetah.2.5.48\tools\SlowCheetah.dll" />
  <Target Name="AfterBuild">
    <ItemGroup>
        <EPiUpdatePackage Include="..\EPiUpdatePackage\**\*.*" />
    </ItemGroup>
    <Copy SourceFiles="@(EPiUpdatePackage)" Condition="Exists('..\EPiUpdatePackage\')"  DestinationFolder="$(TargetDir)\%(RecursiveDir)" ContinueOnError="true" />
  </Target>

The task uses SlowChetha to copy the files from the packages directory. I'll hold my hand up, there are better ways to do this... but, it's an MSBuild script that no one will ever see, so hey it works!

Download For FREE!!!

As this is a little complicated with a few dependencies etc.. and we all just want to copy and paste stuff, I've uploaded the tool to my Github account here. Enjoy!

submit to reddit

Jon D Jones

Software Architect, Programmer and Technologist Jon Jones is founder and CEO of London-based tech firm Digital Prompt. He has been working in the field for nearly a decade, specializing in new technologies and technical solution research in the web business. A passionate blogger by heart , speaker & consultant from England.. always on the hunt for the next challenge

Back to top