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..

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:

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!