title: NUnit Integration Tests on Web API - how to create/destroy integration test db
body: <p>I'm implementing <code>NUnit Integration Tests</code> of our controller REST endpoints in a <code>.NET Web API 2</code> project. We use an <code>Entity Framework</code> <code>code-first from database</code> approach to create our controllers and models.</p>
  
  <p>I've got the <code>myProjectIntegrationTests</code> project set up, with <code>NUnit</code> installed and a reference to <code>myProject</code>.</p>
  
  <p>From my research, the next step is to create a <code>TestSetup</code> script which, on each test, creates an <code>Integration Tests Database</code> in a <code>LocalDb</code>. This allows us to integration test our API calls without affecting the master <code>dev database</code>.</p>
  
  <p><strong>This <code>TestSetup</code> script should do several things each time a test runs:</strong><br>
  - check if a connection is currently open in <code>Integration Test Db</code> - if so, close it.<br>
  - check if an existing <code>Integration Test db</code> exists - if so, tear it down.<br>
  - run a migration from my master <code>dev database</code> to my <code>Integration Test Db</code> to load it with real data.<br>
  - create a new instance of <code>Integration Test Db</code><br>
  - integration tests run...<br>
  - close <code>Integration Test Db</code> connections<br>
  - teardown <code>Integration Test Db</code>  </p>
  
  <p>Creating this <code>TestSetup</code> class is what's giving me trouble. I've found tutorials on how to do this for .NET MVC, .NET Core and also Entity Framework - but none of these seem to be utilizing just <code>.Net Web API</code>, so some of the libraries and code being referenced isn't working for me. <strong>Can someone provide an example script or tutorial link that might work in <code>.NET Web API 2</code>?</strong></p>
  
  <p><br/></p>
  
  <p>Here's an example of someone doing this for EntityFramework, using I believe <code>.Net Core</code>. This is part of a great PluralSight tutorial on integration testing in Entity Framework by Michael Perry, found <a href="https://app.pluralsight.com/library/courses/entity-framework-applications-integration-testing/table-of-contents" rel="nofollow noreferrer">here</a>:  </p>
  
  <pre><code>using Globalmantics.DAL;
  using Globalmantics.DAL.Migrations;
  using NUnit.Framework;
  using System;
  using System.Collections.Generic;
  using System.Data.E  ntity;
  using System.Data.SqlClient;
  using System.IO;
  using System.Linq;
  using System.Reflection;
  
  namespace Globalmantics.IntegrationTests
  {
      [SetUpFixture]
      public class TestSetup
      {
          [OneTimeSetUp]
          public void SetUpDatabase()
          {
              DestroyDatabase();
              CreateDatabase();
          }
  
          [OneTimeTearDown]
          public void TearDownDatabase()
          {
              DestroyDatabase();
          }
  
          private static void CreateDatabase()
          {
              ExecuteSqlCommand(Master, $@"
                  CREATE DATABASE [Globalmantics]
                  ON (NAME = 'Globalmantics',
                  FILENAME = '{Filename}')");
  
              var migration = new MigrateDatabaseToLatestVersion&lt;
                  GlobalmanticsContext, GlobalmanticsConfiguration&gt;();
              migration.InitializeDatabase(new GlobalmanticsContext());
          }
  
          private static void DestroyDatabase()
          {
              var fileNames = ExecuteSqlQuery(Master, @"
                  SELECT [physical_name] FROM [sys].[master_files]
                  WHERE [database_id] = DB_ID('Globalmantics')",
                  row =&gt; (string)row["physical_name"]);
  
              if (fileNames.Any())
              {
                  ExecuteSqlCommand(Master, @"
                      ALTER DATABASE [Globalmantics] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
                      EXEC sp_detach_db 'Globalmantics'");
  
                  fileNames.ForEach(File.Delete);
              }
          }
  
          private static void ExecuteSqlCommand(
              SqlConnectionStringBuilder connectionStringBuilder,
              string commandText)
          {
              using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
              {
                  connection.Open();
                  using (var command = connection.CreateCommand())
                  {
                      command.CommandText = commandText;
                      command.ExecuteNonQuery();
                  }
              }
          }
  
          private static List&lt;T&gt; ExecuteSqlQuery&lt;T&gt;(
              SqlConnectionStringBuilder connectionStringBuilder,
              string queryText,
              Func&lt;SqlDataReader, T&gt; read)
          {
              var result = new List&lt;T&gt;();
              using (var connection = new SqlConnection(connectionStringBuilder.ConnectionString))
              {
                  connection.Open();
                  using (var command = connection.CreateCommand())
                  {
                      command.CommandText = queryText;
                      using (var reader = command.ExecuteReader())
                      {
                          while (reader.Read())
                          {
                              result.Add(read(reader));
                          }
                      }
                  }
              }
              return result;
          }
  
          private static SqlConnectionStringBuilder Master =&gt;
              new SqlConnectionStringBuilder
              {
                  DataSource = @"(LocalDB)\MSSQLLocalDB",
                  InitialCatalog = "master",
                  IntegratedSecurity = true
              };
  
          private static string Filename =&gt; Path.Combine(
              Path.GetDirectoryName(
                  Assembly.GetExecutingAssembly().Location),
              "Globalmantics.mdf");
      }
  }
  </code></pre>
  
  <p>And here's an older example of someone doing this for <code>.Net MVC</code>:</p>
  
  <pre><code>using System;
  using System.Data.Entity;
  using NUnit.Framework;
  
  namespace BankingSite.IntegrationTests
  {
      [SetUpFixture]
      public class TestFixtureLifecycle
      {
          public TestFixtureLifecycle()
          {
              EnsureDataDirectoryConnectionStringPlaceholderIsSet();
  
              EnsureNoExistingDatabaseFiles();
          }
  
          private static void EnsureDataDirectoryConnectionStringPlaceholderIsSet()
          {
              // When not running inside MVC application the |DataDirectory| placeholder 
              // is null in a connection string, e.g AttachDBFilename=|DataDirectory|\TestBankingSiteDb.mdf
  
              AppDomain.CurrentDomain.SetData("DataDirectory", NUnit.Framework.TestContext.CurrentContext.TestDirectory);
          }
  
          private void EnsureNoExistingDatabaseFiles()
          {
              const string connectionString = "name=DefaultConnection";
  
              if (Database.Exists(connectionString))
              {                
                  Database.Delete(connectionString);    
              }           
          }
      }
  }
  </code></pre>