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 simple idea made life a lot more complicated than I originally thought it would. DXC doesn't provide access to either the create or delete database permissions. The options to clean the database on deployment were somewhat limited. I wanted to clean the database, use the epi importer to import content so we had a clean build that we could run integration tests against. That was the original requirement and it ended up working quite well, however, I did have to write a custom application that the Ci/Cd pipeline called.

An Episerver Custom Deployment Application

To solve the problem I decided to create a little C# app that was published via Octopus in the Ci/Cd build. The tool was run against the in-house staging environment, as well as DXC integration. The app used 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 to create this app is provided below:

Hopefully, the code above is semi-obvious to understand. First, the program deletes the epi database (assuming if it has permissions). If any active SQL connections exist, it tries to drop them. It then runs a SQL script to wipe all data, tables, etc... Using reflection to get the current version of Episerver the site is built against, it uses that version number to copy the core Episerver installation files are installed via NuGet. This install SQL script and the upgrade SQL scripts are found within the Nuget packages directory. If it finds the scripts, the program will then run the install Epi database script. After that has run it calls the Export-EPiUpdates batch file, which calls epideploy.exe and patches the database.

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

The task uses SlowCheetah 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! As getting a script and installing everything manually is complicated, I've uploaded the tool to my Github account here for you to work with easily. Happy Coding 🤘