If you need to use LocalDb for integration testing your database with EntityFramework or some other ORM this article can help.

First, you will need to create .NET core class library project to hold your integration tests:

After this you will need to set-up XUnit.net.

Check my previous blog post to do that.

After this create a folder named Data in your test project. In this folder you will need to put SQL script GenerateDb.sql (without create a database statement) which will create all objects in the database.

Put this LocalDb class somewhere in the project.

 public static class LocalDb
    {
        public const string DbDirectory = "Data";

        public static void CreateLocalDb(string databaseName, string scriptName, bool deleteIfExists = false)
        {
            string codeBase = Assembly.GetExecutingAssembly().CodeBase;
            UriBuilder uri = new UriBuilder(codeBase);
            string path = Uri.UnescapeDataString(uri.Path);
           // return Path.GetDirectoryName(path);

            string outputFolder = Path.Combine(Path.GetDirectoryName(path), DbDirectory);
            string mdfFilename = databaseName + ".mdf";
            string databaseFileName = Path.Combine(outputFolder, mdfFilename);

            // Create Data Directory If It Doesn't Already Exist.
            if (!Directory.Exists(outputFolder))
            {
                Directory.CreateDirectory(outputFolder);
            }

            if (CheckDatabaseExists(databaseName) && deleteIfExists)
            {
                DropDatabaseObjects(databaseName);
            }
            else if (!CheckDatabaseExists(databaseName))
            {
                // If the database does not already exist, create it.
                CreateDatabase(databaseName, databaseFileName);
            }
            if (deleteIfExists)
            {
                ExecuteScript(databaseName, scriptName);
            }
        }

        private static void ExecuteScript(string databaseName, string scriptName)
        {
            string connectionString = string.Format(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog={0};Integrated Security=True", databaseName);

            string codeBase = Assembly.GetExecutingAssembly().CodeBase;
            UriBuilder uri = new UriBuilder(codeBase);
            string path = Uri.UnescapeDataString(uri.Path);

            string outputFolder = Path.Combine(Path.GetDirectoryName(path), DbDirectory);
            string scriptPath = Path.Combine(outputFolder, scriptName);

            var file = new FileInfo(scriptPath);
            string script = file.OpenText().ReadToEnd();

            string[] commands = script.Split(new[] { "GO\r\n", "GO ", "GO\t" }, StringSplitOptions.RemoveEmptyEntries);

            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                foreach (string c in commands)
                {
                    var command = new SqlCommand(c, connection);
                    command.ExecuteNonQuery();
                }
            }
        }

        private static bool CheckDatabaseExists(string databaseName)
        {
            string connectionString = string.Format(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=master;Integrated Security=True;Connection Timeout=300");
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();

                cmd.CommandText = string.Format("SELECT name FROM master.dbo.sysdatabases WHERE ('[' + name + ']' = '{0}' OR name = '{1}')", databaseName, databaseName);
                object result = cmd.ExecuteScalar();
                if (result != null)
                {
                    return true;
                }
            }

            return false;
        }

        private static void DropDatabaseObjects(string databaseName)
        {
            try
            {
                string connectionString = string.Format(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog={0};Integrated Security=True;Connection Timeout=300", databaseName);

                using (var connection = new SqlConnection(connectionString))
                {
                    connection.Open();
                    var commandSetSingle = new SqlCommand(@"declare @ord int, @cmd varchar(8000)

                    declare objs cursor for
                    select 0, 'drop trigger [' + name + '] on database' from sys.triggers
                    where parent_class = 0 and is_ms_shipped = 0
                    union
                    select 1, 'drop synonym [' + schema_name(schema_id) + '].[' + name + ']' from sys.objects o
                    where o.type = 'SN'
                    union
                    select 2, 'drop procedure [' + schema_name(schema_id) + '].[' + name + ']' from sys.objects o
                    where o.type = 'P'
                    union
                    select 3, 'drop view [' + schema_name(schema_id) + '].[' + name + ']' from sys.objects o
                    where o.type = 'V'
                    union
                    select 4, 'drop function [' + schema_name(schema_id) + '].[' + name + ']' from sys.objects o
                    where o.type IN ('FN','IF', 'TF')
                    union
                    select 5, 'alter table [' + schema_name(schema_id) + '].[' + object_name(parent_object_id) + '] drop constraint [' + name + ']'
                    from sys.objects
                    where type = 'F'
                    union
                    select 6, 'drop table [' + schema_name(schema_id) + '].[' + name + ']' from sys.objects o
                    where o.type = 'U'
                    union
                    select 7, 'drop type [' + schema_name(schema_id) + '].[' + name + ']' from sys.types
                    where is_user_defined = 1
                    union
                    select 8, 'drop default [' + schema_name(schema_id) + '].[' + name + ']' from sys.objects o
                    where o.type = 'D'
                    order by 1

                    open objs
                    fetch next from objs into @ord, @cmd

                    while @@FETCH_STATUS = 0
                    begin
                      print @cmd
                      execute (@cmd)
                      fetch next from objs into @ord, @cmd
                    end

                    close objs
                    deallocate objs",
                                    connection);
                    commandSetSingle.ExecuteNonQuery();
                }
            }
            catch
            {
            }
        }



        private static void CreateDatabase(string databaseName, string databaseFileName)
        {
            string connectionString = string.Format(@"Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=master;Integrated Security=True");
            using (var connection = new SqlConnection(connectionString))
            {
                connection.Open();
                SqlCommand cmd = connection.CreateCommand();

                // TryDetachDatabase(databaseName);
                cmd.CommandText = string.Format("CREATE DATABASE {0} ON (NAME = N'{0}', FILENAME = '{1}')", databaseName, databaseFileName);
                cmd.ExecuteNonQuery();
            }
        }
}

Please be aware that in ASP.NET 5 LocalDb connection string is changed and version numbering (v11) is removed.

You will also need ItemDeployment to deploy script file into proper locations like in previous version of Visual Studio.

 internal static class ItemDeployment
    {
        public static void DeployItems(IEnumerable items, bool retainDirectories = false)
        {
            var environmentDir = new DirectoryInfo(Directory.GetCurrentDirectory());
            var binFolderPath = GetDeploymentDirectory();

            foreach (var item in items)
            {
                if (string.IsNullOrWhiteSpace(item))
                {
                    continue;
                }

                string dirPath = retainDirectories ? Path.GetDirectoryName(item) : "";
                var filePath = item.Replace("/", @"\");
                if (environmentDir.Parent != null)
                {

                    UriBuilder uri = new UriBuilder(environmentDir.FullName);
                    string path = Uri.UnescapeDataString(uri.Path);

                    var itemPath = new Uri(Path.Combine(Path.GetDirectoryName(path), filePath)).LocalPath;
                    if (!File.Exists(itemPath))
                    {
                        throw new FileNotFoundException(string.Format("Can't find deployment source item '{0}'", itemPath));
                    }

                    if (!Directory.Exists(binFolderPath))
                    {
                        throw new DirectoryNotFoundException(
                            string.Format("Deployment target directory doesn't exist: '{0}'", binFolderPath));
                    }

                    var dirPathInBin = Path.Combine(binFolderPath, dirPath);
                    if (!Directory.Exists(dirPathInBin))
                    {
                        Directory.CreateDirectory(dirPathInBin);
                    }

                    var itemPathInBin =
                        new Uri(Path.Combine(binFolderPath, dirPath, Path.GetFileName(filePath))).LocalPath;
                    if (!File.Exists(itemPathInBin))
                    {
                        File.Copy(itemPath, itemPathInBin);
                        File.SetAttributes(itemPath, FileAttributes.Normal);

                    }
                    else
                    {
                        var hash1 = ComputeFileHash(itemPath);
                        var hash2 = ComputeFileHash(itemPathInBin);
                        string hash1String = BitConverter.ToString(hash1);
                        string hash2String = BitConverter.ToString(hash2);

                        if (!hash1String.Equals(hash2String))
                        {

                            File.Copy(itemPath, itemPathInBin ,true);
                            File.SetAttributes(itemPath, FileAttributes.Normal);
                        }
                    }
                }
            }
        }

        public static byte[] ComputeFileHash(string fileName)
        {
            using (var stream = File.OpenRead(fileName))
                return System.Security.Cryptography.MD5.Create().ComputeHash(stream);
        }

        public static string GetDeploymentDirectory()
        {
            string codeBase = Assembly.GetExecutingAssembly().CodeBase;
            UriBuilder uri = new UriBuilder(codeBase);
            string path = Uri.UnescapeDataString(uri.Path);
            return Path.GetDirectoryName(path);
        }
    }
}

In constructor of your test class you just need to put Database name and script name for generating your LocalDb database.

We have implemented hash checking when copying files to avoid problems with multiple threads trying to copy same file.

public class TestClass
{
public TestClass()
{
     ItemDeployment.DeployItems(
               new[]
                {
                     @"ProjectName\FolderName\GenerateDb.sql"
                }, true);
     LocalDb.CreateLocalDb("Mydatabase", "GenerateDb.sql", true);
}
[Fact]
public void InsertPerson_AcceptCorrectParameters_InsertSuccess()
{
// write your database test
}

}

You will probably need to disable running XUnit.Net tests in multiple threads to avoid dirty checks in database tests.

If you like this article don’t forget to subscribe to this blog and make sure you don’t miss new upcoming blog posts.