Integration testing with LocalDB

Question

In order to write integration tests against some data access code for my application, I've written an abstract class to use for the database integration tests.

It creates a fresh instance of the database using LocalDB then tears it down once complete.

However for some reason, the DROP DATABASE command always fails with the following exception:

Cannot drop database "Integration_DataAccess" because it is currently in use.

Why is this? Is there a better approach for using LocalDB for integration database testing?

Below is the code for my abstract database integration testing class:

using System;
using ProjectName.DataAccess.EmailSubscription;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using NUnit.Framework;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;

namespace ProjectName.DataAccess.Tests.IntegrationTests
{
    public abstract class IntegrationTestBase
    {
        private const string DatabaseConnNameEmpty = "DbDSNEmpty";
        private const string DatabaseConnName = "DbDSN";

        private const string DatabaseName = "Integration_DataAccess";

        [TestFixtureSetUp]
        public void SetupTempDatabase()
        {
            // Create database (drop first, just in case)
            var connection =new SqlConnection(ConfigurationManager.ConnectionStrings[DatabaseConnNameEmpty].ConnectionString);
            var server = new Server(new ServerConnection(connection));

            server.ConnectionContext.ExecuteNonQuery( string.Format(  "IF EXISTS(select * from sys.databases where name='{0}') DROP DATABASE [{0}]", DatabaseName));
            server.ConnectionContext.ExecuteNonQuery(string.Format("CREATE DATABASE {0}", DatabaseName));

            // Run database creation script
            using (var sr = new StreamReader(@"IntegrationTests\DatabaseSetup.txt"))
            {
                var sql = sr.ReadToEnd();
                ExecuteNonQuery(sql);
            }
        }

        [TestFixtureTearDown]
        public void RemoveTempDatabase()
        {
            // Drop database
            ExecuteNonQuery(string.Format("DROP DATABASE [{0}]", DatabaseName));
        }

        #region private methods

        protected static void ExecuteNonQuery(string sql)
        {
            // Thanks to http://weblogs.asp.net/jgalloway/archive/2006/11/07/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-_2D00_-the-easy-way.aspx
            var connection = new SqlConnection(ConnectionString());
            var server = new Server(new ServerConnection(connection));
            server.ConnectionContext.ExecuteNonQuery(sql);
            connection.Close();
        }

        protected static string ConnectionString()
        {
            return ConfigurationManager.ConnectionStrings[DatabaseConnName].ConnectionString;
        }

        protected static DataTable Select(string sql)
        {
            using (var conn = new SqlConnection(ConnectionString()))
            {
                var adapter = new SqlDataAdapter(sql, conn);
                var data = new DataTable();
                adapter.Fill(data);
                return data;
            }
        }

        protected static int ExecuteScalar(string sql)
        {
            using (var conn = new SqlConnection(ConnectionString()))
            {
                var cmd = new SqlCommand(sql, conn);
                conn.Open();
                int result = (int)cmd.ExecuteScalar();
                conn.Close();
                return result;
            }
        }

        #endregion
    }
}

Answer 1

That's because the connection you have open is to the database you want to drop.

You need to USE MASTER first.

See this article http://blog.sqlauthority.com/2007/12/07/sql-server-fix-error-3702-cannot-drop-database-because-it-is-currently-in-use/