Keeping reliable test data in EPiServer content database for automated UI tests

In my current client’s EPiServer 9 website project we are using Selenium to run automated UI tests on our code. For this we keep an EPiServer content database backup which we restore before each execution of the test suite. This ensures that any alterations to the content made by the tests are reset before each run.

In previous projects we’ve kept a somewhat fresh dump of the production database for this purpose, together with a [Selenium] node in the page tree for specially set up content. Of course there are both pros and cons with this approach; for instance, while we get production like data to run the tests on, the backup may take longer time to restore and if the content is changed between backups it would require us to repair broken tests.

Creds to my collegues (like Christian Wigren and Robin Helly) who was involved setting things up for this client.

NOTE: You may also want to read Foreign key constraint violation for tblPropertyDefintion causing SqlException while automatically restoring EPiServer database for UI tests, as this article relates to an issue that may occur while doing things this way. Also, EPiServer Content Folders ‘For All Sites’, ‘For This Site’, ‘For this Page’ not showing up in edit mode may be useful.

Working with a template EPiServer content database for automated testing

In this project we’ve tried a slightly different approach. We set up a very lightweight EPiServer template database backup file, containing only the most important settings. Then we had our test setup fill the database with code generated EPiServer pages, blocks and other content. While we in this way didn’t get the pros of having production like data for the tests, we got the benefit of being in total control of our test content.

So, we set up a test area in a separate project in our Visual Studio solution; a project that would not be built nor deployed for the Production build type. In this area we have a DatabaseController with actions to manipulate the database. For instance, RestoreDBs which restores the bak file and also gives us some timing output. I’ve replaced constructor injection with the ServiceLocator to make things more clear.

public ActionResult RestoreDB()
{
  var resetTimer = Stopwatch.StartNew();
  ResetEPiDatabase();
  resetTimer.Stop();
			
  return Json(
    new
    {
      ResetEPiDatabaseFromBackupFileIn = resetTimer.ElapsedMilliseconds + " ms"
    }, JsonRequestBehavior.AllowGet);
}

private void ResetEPiDatabase()
{
  const string databaseFullPath = "dbEPiDatabase";
  RestoreFromBackupFile(@"c:\temp\", databaseFullPath);

  ServiceLocator.Current.GetInstance<IContentCacheRemover>().Clear();
}

Important here is apart from the call to our restore method itself, the line containing EPiServer’s IContentCacheRemover. It is vital that we clear EPiServer’s page cache between database restores. Otherwise the Save method will believe that pages already exists while they actually do not (since we just restored a blank database). Without the cache clear, we would get URLs like /somepage2/some-other-page2/ while expecting /somepage/some-other-page/; making the tests go 404.

The actual restore of the backup file is rather simple. It could probably be refactored into something prettier, but here’s what we use.

private void RestoreFromBackupFile(string path, string databaseName)
{
  var backUpPath = string.Format(@"{0}{1}.bak", path, databaseName);
  var connectionString = string.Format("Data Source=.;Initial Catalog={0};User Id=dbEPiDatabaseUser;Password=some_password;MultipleActiveResultSets=True", databaseName);

  using (var con = new SqlConnection(connectionString))
  {
    con.Open();

    const string useMaster = "USE master";
    var useMasterCommand = new SqlCommand(useMaster, con);
    useMasterCommand.ExecuteNonQuery();

    var alter1 = @"ALTER DATABASE [" + databaseName + "] SET Single_User WITH Rollback Immediate";
    var alter1Cmd = new SqlCommand(alter1, con);
    alter1Cmd.ExecuteNonQuery();

    var restore = @"RESTORE DATABASE [" + databaseName + "] FROM DISK = N'" + backUpPath +
                  @"' WITH REPLACE,  NOUNLOAD,  STATS = 10";
    var restoreCmd = new SqlCommand(restore, con);
    restoreCmd.ExecuteNonQuery();

    var alter2 = @"ALTER DATABASE [" + databaseName + "] SET Multi_User";
    var alter2Cmd = new SqlCommand(alter2, con);
    alter2Cmd.ExecuteNonQuery();
  }
}

It’s not that complicated, we just execute the SQL statements to restore a database from a backup file.

Populating template EPiServer database with content for automated UI tests

The next step in our Selenium test setup phase is to generate all of the test data that our tests will need. I won’t go into detail on how to do this since there are already so many articles on how to create EPiServer pages and blocks from code. Just use EPiServer’s ServiceLocator, or even better, constructor injection to retrieve an instance of IContentRepository and start creating your test data. It may be a good idea to put some thought into how you’d like to structure this; keeping everything in the same place may be messy once you start to have more and more content.

var contentRepository = ServiceLocator.Current.GetInstance<IContentRepository>();

var block= _contentRepository.GetDefault<TextBlock>(contentReference, CultureInfo.GetCultureInfoByIetfLanguageTag(Constants.Languages.Swedish));

block.MainBody = new XhtmlString("Some string.");
// ..
_contentRepository.Save(block as IContent, SaveAction.Publish, AccessLevel.NoAccess);

Easy way to get your EPiServer content back locally

It’s often a good thing to keep a somewhat production like EPiServer database with reliable content while developing new features and functionality. If you run your UI tests locally using this template database method, you will quickly find yourself missing lots of valuable data. For this purpose it may be a good idea to keep a content rich EPiServer database backup file as well, and create a different action that restores it to the SQL server. So, while surfing to /Database/RestoreDB/ restores the template database, surfing to /Database/ContentRichDB/ restores your content.