Entity Framework Unit Testing with SQLLocalDB

Summary

I’ve published a few blog posts on the usage of SQLLocalDB with NHibernate.  Now I’m going to demonstrate how easy it is to use with EF.  In fact, SQLLocalDB can be used with ADO queries and LINQ-To-SQL.  If you’re dealing with legacy code and your methods use a lot of database access operations, it would be beneficial to get your code in a test harness before converting to your target ORM.


Modifying the EF Context

The first thing I’m going to do is create a helper to deal with the Entity Framework context.  If you generate a new Entity Framework database, you’ll get a context that is configured using the App.config file.  This is OK, if your application is always going to use the same database.  If you need to switch databases, and in this case, we’ll need to use a different database when under unit testing, then you’ll need to control the context parameters.  Here’s the helper class that I wrote to handle multiple data sources:


using System.Data.Entity.Core.EntityClient;
using System.Data.SqlClient;

namespace Helpers
{
    public static class EFContextHelper
    {
        public static string ConnectionString(string connectionName, string databaseName, string modelName, string userName, string password)
        {
            bool integratedSecurity = (userName == “”);

            if (UnitTestHelpers.IsInUnitTest)
            {
                connectionName = “(localdb)\” + 
                    UnitTestHelpers.InstanceName;
                integratedSecurity = true;
            }

            return new EntityConnectionStringBuilder
            {
                Metadata = “res://*/” + modelName + “.csdl|res://*/” + modelName + “.ssdl|res://*/” + modelName + “.msl“,
                Provider = “System.Data.SqlClient“,
                ProviderConnectionString = new  
                     SqlConnectionStringBuilder
                {
                    MultipleActiveResultSets = true,
                    InitialCatalog = databaseName,
                    DataSource = connectionName,
                    IntegratedSecurity =
integratedSecurity,
                    UserID = userName,
                    Password = password
                }.ConnectionString
            }.ConnectionString;
        }
    }
}


The helper class builds the connection string and replaces the one that is defined in the App.config file.  There is a check to see if the calling assembly is a unit test assembly, I will use this to override the server name and point to the SQLLocalDB that is defined in the UnitTestHelpers.InstanceName.  This will ensure that if an EF context is called inside a method of your program, it will connect to the unit test database if a unit test calls your program.

Once you have created the helpers class. you’ll need to use it inside your context code.  To do that, you’ll need to add the code into the Context.tt file (it should be named something like “Model1.Context.tt”).  This file (also known as a T4 file), is used to generate the Model1.Context.cs file.  Modify the code to look something like this:

#>
using System;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
using Helpers;
<#
if (container.FunctionImports.Any())
{
#>
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Linq;
<#
}
#>

<#=Accessibility.ForType(container)#> partial class <#=code.Escape(container)#> : DbContext
{
    public <#=code.Escape(container)#>()
        : base(EFContextHelper.ConnectionString(“SQLSERVERNAME”, “sampledata”, “Model1”, “”, “”))
    {

First, you’ll need to add the “using Helpers;” line, then you’ll need to add in the EFContextHelper.ConnectionString method as the base class initialization parameter.
Change the SQLSERVERNAME to match your actual MS SQL Server name.  Change sampledata to match your actual database name.  Change “Model1” to match the name of the edmx file.  Finally, you can use a user name and password if you’re not using integrated security on MS SQL Server.  In my example, I’m using integrated security. 
Once you change the tt file, save it and check the matching cs file to make sure there are no syntax errors.


Using the Mapping Generator

Currently the mapping generator is used to generate NHibernate database mappings.  If you don’t want the extra code, you can download my project and strip out all the Fluent Nhibernate code (or just leave it for now).  I have added a new section that will generate code to create tables that match the tables defined in your database.  But first you must run the generator to create the table definitions.

Go to my GitHub account and download the NHibernateMappingGenerator solution.  You can find it here.  Make sure that the NHibernateMappingGenerator project (which is a Windows Forms project) is set to the startup project.  Then run the program and select your SQL Server and database:


If your server doesn’t use integrated security (i.e. you need a user name and password to access your SQL Server), you’ll have to do some open heart surgery (sorry, I haven’t added that feature to this program yet).  Once you click the “Generate” button, this program  will generate code that can create tables, stored procedures, views and constraints in your SQLLocalDB database instance for unit testing purposes.  You should note that your test database will match your production database from the time that you generated this code.  If you make database changes to your production system, then you’ll need to regenerate the table definitions.


The Unit Test

I’ll demonstrate one simple unit test.  This test isn’t really going to be useful, except it will test the fact that you can insert data into SQLLocalDB and query the data using Entity Framework and it will not corrupt your production data.  Here’s the code:

[TestClass]
public class EntityFrameworkTests
{
  [TestCleanup]
  public void Cleanup()
  {
    UnitTestHelpers.TruncateData();
  }

  [TestMethod]
  public void TestEntityFrameworkContext()
  {
    using (var db = new sampledataEntities())
    {
      Store store = new Store
      {
        Name = “Test Store“,
      };
 
      db.Stores.Add(store);
      db.SaveChanges();

      var resultQuery = (from s in db.Stores 
                         select s).ToList();

      Assert.AreEqual(1, resultQuery.Count());
      Assert.AreEqual(“Test Store“, resultQuery[0].Name);
    }
  }
}

As you can see, using EF from a unit test is just like using it inside your application.  Just open a context, insert data, query data, done.  The magic is occurring inside the context itself.  If you put a break-point inside the EFContextHelper.ConnectionString method, you can see where the connection string is built to point to the SQLLocalDB instance instead of your production database instance (you should always test this to verify that it is functioning correctly).


Generating the Tables

One detail that is buried in this sample code that I haven’t covered yet is how the tables are generated inside the SQLLocalDB instance.  In previous blog posts I always used the Fluent NHibernate database generate feature to generate all the tables of the database in the unit test assembly initialize method.  If you open the AssemblyCommon.cs file (inside the SampleUnitTests project) and look at the ClassStartInitialize method, you’ll see where I call another UnitTestHelpers method called CreateAllTables.  Dig into this method and you’ll see ADO code that creates MS SQL tables for a specified database.  This makes the entire unit test helper project agnostic to the type of database you might want to test.  


Using This Technique for Other Database Types

The unit test helpers can currently handle EF as well as Fluent NHibernate, but technically, any ORM or direct data access can use this set of helpers.  Make sure to create a database connection helper for the database type you will use in your project (or already use in your project).  Then you can apply the same UnitTestHelpers.IsInUnitTest check to set the instance to the SQLLocalDB instance.  Finally, you can add unit tests for each method in your application, no matter which database context it is using.  

As I mentioned in the summary, it would ease your transition to another ORM, assuming that is your goal.  Your first step would be to create unit tests around your current objects and methods, using your current database technique, then convert to your target ORM and make sure the unit tests pass.


Getting the Code

You can download the entire solution at my GitHub account: https://github.com/fdecaire/NHibernateMappingGenerator.




 

Unit Testing with Stored Procedures

Summary

In this post, I’m going to show how to use a stored procedure in a SQLLocalDB MS Test setup.  Technically this is an integration test, so excuse the fact that I might call it a unit test out of habit.


The Technical Details


What I’m going to do is copy the code from a SQL Server stored procedure, and paste it into a class/method string or property.  Next I’m going to write a unit test helper method that will create the stored procedure in my SQLLocalDB test instance.  Finally, I’m going to run a unit test that executes a method containing a call to the stored procedure and verify it worked correctly.

If your familiar with stored procedures and unit tests, you’re probably scratching your head right now and thinking “what would be the purpose.”  My purpose in accomplishing this task is to get legacy code into a test harness so I can refactor my code and replace ugly stored procedures with LINQ code, or possibly clean up any stored procedures I’m forced to keep.

Now you’re thinking that if I change a stored procedure, my unit test will still work, even though my run-time code might not.  That’s a problem.  However, I have created a map class generator for NHibernate.  What I plan to do is enhance this generator to scrape the text out of the stored procedures in a database and put them into my project containing all my ORM mappings.  That way, I can regenerate my class mappings and synchronize the stored procedures all at once.  This will make sure that my unit tests are always using the same stored procedures that are defined in the database.  This will be demonstrated in a future blog post.

The Session Factory

I’m going to start by demonstrating how you can modify your session factory to make it execute in dual mode.  Which mode is used will depend on the calling assembly.  If the calling assembly is from a unit test assembly, then we’ll use the SQLLocalDB context.  If the calling assembly is not a unit test assembly, then we’ll assume that we’re supposed to connect to the real database.  I have commented the session factory code from the code shown below, you can download the demo application to see the real code.

public class MSSQLSessionFactory
{
    private static ISessionFactory _sessionFactory;
    private static ISessionFactory SessionFactory
    {
        get
        {
            if (_sessionFactory == null)
            {
                if (UnitTestHelpers.UnitTestHelpers.IsInUnitTest)
                {
                    // unit test context

                }
                else
                {
                    // production context

                }
            }
            return _sessionFactory;
        }
    }
    public static ISession OpenSession()
    {
        return SessionFactory.OpenSession();
    }
}

The code in my demo could use some folding and refactoring, but this is just to demonstrate the concept.  So I’m keeping it simple for now.


The Stored Procedure

I scraped the stored procedure code from my database and pasted it into a string.  Eventually, I’ll need a method that can do that for me.  The code for this object looks like this:

    public class StoredProcedureObjects
    {
        public string Name { get { return pSetClassForTeacher“; } }
        public string Code {
            get
            {
                return @”USE [facultydata]
GO
/****** Object:  StoredProcedure [dbo].[pSetClassForTeacher]    Script Date: 8/9/2014 6:06:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[pSetClassForTeacher]
@TeacherId int,
@ClassId int
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE class SET teacherid=@TeacherId WHERE id=@ClassId

END“;
            }
        }

        public void CreateStoredProcedure(string databaseName)
        {
            SqlConnection db = new SqlConnection(
                “server=(localdb)\” + databaseName + 
                “testinstance;” +
                “Trusted_Connection=yes;” +
                “database=” + databaseName + “; ” +
                “Integrated Security=true;“);

            // first, drop the stored procedure if it already exists
            db.Open();

            string sp = @”if exists (select * from sys.objects 
               where name = N'” + Name + @”‘ and type = N’P’)
               begin
                   drop procedure ” + Name + @”
               end
“;
            SqlCommand myCommand = new SqlCommand(sp, db);
            myCommand.ExecuteNonQuery();

            // need to read the text file and create the stored 
            // procedure in the test database
            string[] TSQLcommandList = Regex.Split(Code, “GO“);

            foreach (var tsqlCommand in TSQLcommandList)
            {
                myCommand = new SqlCommand(tsqlCommand, db);
                myCommand.ExecuteNonQuery();
            }

            db.Close();
        }
    }

First, you should notice that we need to split the stored procedure query by the “GO” statements.  Technically the sql code above is a collection of separate queries and we need to execute these individually.  

Next, I have taken the liberty of changing the “ALTER PROCEDURE” to “CREATE PROCEDURE”.  Later, I’m going to do a string replace when I generate the stored procedure text while reading it from the database.

Last, I have written code to drop the stored procedure first, if it already exists.  Technically, we would like to create all the stored procedures in the database at assembly start time and just use them for all tests.  There should be no expectation that a unit test will be changing while unit tests are in progress, so there’s no need to delete and create stored procedures for each unit test.


The Unit Test

The unit test method looks like this:

[TestMethod]
public void test_stored_procedure_object()
{
    StoredProcedureObjects sp = new StoredProcedureObjects();
    sp.CreateStoredProcedure(“facultydata“);

    using (var db = MSSQLSessionFactory.OpenSession())
    {
        var teacher = new Teacher
        {
            name = “Smith
        };
        db.Save(teacher);

        teacher = new Teacher
        {
            name = “Johnson
        };
        db.Save(teacher);

        string[] classList = new string[] { “MTH 100“, 
              “CHM 101“, “ENG 200“, “PHY 230“};

        foreach (var aClass in classList)
        {
            var MyClass = new Class
            {
                name = aClass,
                teacherid = 1
            };
            db.Save(MyClass);
        }
        db.Flush();
    }

    TeacherRecordObjects teacherRecordObjects = new 
             TeacherRecordObjects();
    teacherRecordObjects.SetClassForTeacher(2,3);

    using (var db = MSSQLSessionFactory.OpenSession())
    {
        var query = (from c in db.Query<Class>()
                                 where c.id == 3
                                 select c).FirstOrDefault();

        Assert.AreEqual(2, query.teacherid);
    }
}

The first thing this unit test does is create the stored procedure.  Next I have some insert queries to insert two teachers and 4 classes.  This is my test data setup.  Then I call the SetClassForTeacher method with a parameter that I can check for after the method executes.  Last, I open another database context and read the results and test for equality.


Summary

There are a lot of pieces necessary to making this work properly.  You need to be able to check which project called the context and select the correct database context.  You need to be able to create a stored procedure in SQLLocalDB.  You need to be able to truncate data in your SQLLocalDB after your unit test has concluded (so your results don’t mess up the next unit test).  Finally, you need to handle the case where someone will try and create a stored procedure from another unit test without realizing that it already exists.

You can download the sample code here:

UnitTestingStoredProcedures.zip



 

How to detect if your code was called from a unit test

I’ve done a few unit test blog posts, but this time I’m going to demonstrate how to detect if a method is running under a unit test.  This flag can be used to break dependencies in an object or run a test database instance instead of the production instance.  

The first article I stumbled across was this stack overflow article on just such a problem:

determine-if-code-is-running-as-part-of-a-unit-test

Answer #22 had my solution, using reflection to determine if one of the included DLL’s was the UnitTestFramework.


public static class UnitTestDetector
{
    public static bool IsInUnitTest()
    {
        string assemblyName = 
         “Microsoft.VisualStudio.QualityTools.UnitTestFramework“;
        return AppDomain.CurrentDomain.GetAssemblies().Any(a => 
                   a.FullName.StartsWith(assemblyName));
    }
}


So what’s the point?  The purpose of this class/method is to embed this into your context code and force your ORM to use a test database when you are running unit tests, but run your production database when the calling assembly is not a unit test assembly.  By using this code to switch your database at the context level, you no longer have to worry about breaking dependencies of your objects that will be under unit test.

Another advantage is that this can be used in multiple ORM’s (such as EF, NHibernate, etc.).  It can even be used in direct queries if you have a SqlConnection wrapper that feeds the connection string in one place.

In a future post, I’ll demonstrate how to use this class/method in Entity Framework and show how to connect EF to SQLLocalDB for unit testing purposes.

 

SQLLocalDB Fast Unit Tests

Summary

One of my previous posts described how to setup a MS SQL Lite server called LocalDB.  My little demo project worked really good except for one detail.  It was slow.  The primary problem was that I was starting the instance, creating the database in the unit test initialize (called [TestInitialize]) and then I was dropping the database and stopping the instance in the test cleanup method.  That caused a database create and destroy for each unit test.  Now I’m going to show how to setup your unit tests to create the database once and destroy it once.  I’m also going to describe how to clean out your data for the next test.


The Truncate Table Command

The truncate table command has a couple of nice features: It can delete data fast, and it resets the seed value used for the identity column.  So I setup a list of tables to be truncated and a method that will perform the actual truncation.  Here’s how I implemented the list (which should be contained in your unit test module):

public static class SampleDatabase
{
    public static string[] TableList
    {
        get
        {
            // make sure these are in the right order.
            return new string[]
                {
                “product“,”producttype“,”store
                };
        }
    }

}

The code that handles the truncation looks like this:

public static void TruncateData(string databaseName, string[] tableList)
{
    SqlConnection db = new SqlConnection(
        “server=(localdb)\” + databaseName + “testinstance;” +
        “Trusted_Connection=yes;” +
        “database=” + databaseName + “; ” +
        “Integrated Security=true; ” +
        “connection timeout=30“);

    db.Open();

    foreach (var item in tableList)
    {
        SqlCommand myCommand = new SqlCommand(
           @”TRUNCATE TABLE ” + databaseName + “..” + item, db);

        myCommand.ExecuteNonQuery();
    }
    db.Close();

}

You can look at the sample code to see the details of how this fits together.  Basically, I call my TruncateData method inside the TestCleanup method to truncate the tables after every unit test.  When you create your list of tables to truncate, be aware that if you have constraints on your test database, you’ll have to truncate your tables in the right order.

Now, there is an AssemblyInitialize and an AssemblyCleanup attribute that can be used once inside the unit test project.  These are used with static methods and are called before your unit tests start and after your unit tests end.  I created a cs file named “AssemblyUnitTestShared.cs” that contains this code:

[TestClass]
public sealed class AssemblyUnitTestShared
{
    [AssemblyInitialize]
    public static void ClassStartInitialize(TestContext 
       testContext)
    {
        UnitTestStartAndEnd.Start(“sampledata“);
    }

    [AssemblyCleanup]
    public static void ClassEndCleanup()
    {
        UnitTestStartAndEnd.End(“sampledata“);
    }

}

At this point your unit tests would look something like this:

[TestMethod]
public void test_method_2()
{
    using (ISession unitTestSession = 
           SampleDataTestContext.OpenSession())
    {
        Product product = new Product
        {
            Name = “P 1“,
            ProductId = 1,
            store = 1
        };
        unitTestSession.Save(product);
        unitTestSession.Flush();

        ProductObject productObject = new 
              ProductObject(unitTestSession);

        var productList = productObject.ReadAll();

        Assert.AreEqual(“P 1“, productList[0].Name);
    }

}

When executing these unit tests there will be an initial delay while the database is being created, then all the tests will run in a few seconds time, then there is a delay while the database is being torn down.  Adding more tests will not significantly increase your total unit test execution time.

Here’s the source code, which I would recommend downloading and getting it to work.  This can serve as a model for your project unit tests.

SQLLocalDBBlogPost2.zip


Other Considerations

At this point, I have shown how you can use LocalDB to create a temporary database and run unit tests against methods that contain database operations.  There are still some issues here that should be resolved.

First, what about integrity constraints.  One method is to apply all the constraints to the database after the tables are created (which currently occurs on the first call to the nhibernate context).  The problem with this method is that each unit test would need to incorporate all the required seed data that constrains any tables that are needed for the actual test.  This is a lot of unnecessary work.

Another method is to only generate the constraints at the unit test level and destroy all the constraints at the end of the unit tests.  I have not tried this yet, but there will be some sort of performance penalty.  If the number of constraints is restricted to just the tables that are necessary, then the performance loss will be minimized and the unit tests can be contained within the scope of what is being tested.  

So now we would need to come up with code to generate and destroy these constraints.  One of my older methods of doing this is to create a list of all the constraints in the database and write a method that receives a list of tables.  Any foreign constraints would be added only if both tables were present in the list.  Unique constraints added if the table with the constraint was in the list and so on.  Then the tear-down can be accommodated by scanning the master database for foreign and unique constraints.  This code can all be encapsulated in a common project that you would use with your unit tests (except the table list which you have here and the list of actual constraints, because they are database dependent).  

The next issue surrounds the fact that your database will be updated at some time in the future and you’ll need to be aware that any constraints changed in the database have to be changed in your test package.  There is no simple solution for this problem.  In the past I have created software that reads the tables, fields, constraints, indexes, etc from an excel file.  This excel file was the master definition of everything.  Any database changes would be made to this file and a tool I developed would synch changes with the actual database (when the developer was ready to apply changes).  This excel file was then used by my unit tests and out of synch issues were minimized in this fashion.  

Another issue surrounds the fact that you need to seed your data for a unit test, and this can get very tedious in a hurry.  So I have written classes and methods to read data from an xml file (embedded in the unit test project) to pre-populate the data in a unit test before it runs.  Then I name my xml file with the unit test source name and the method name that calls it.  I use individual files for each method because data can be altered in the future (which could mess up multiple tests if they share one xml file).  Copy and paste is my friend.  The layout of such a file would look something like this:

<data>
  <tablename>
    <field1>data in field 1</field1>
    <field2>data in field 2</field2>
    <field3>data in field 3</field3>
  </tablename>
</data>

One last thing to be aware of.  It seems that LocalDB does not use the FILEGROWTH parameter.  I have not dug too deep on this issue (I just increased my starting database size to 8k), but you might run into an insufficient size problem if you insert too much data into your unit test database.  You can either bump up the size or determine if there is a mechanism to auto expand.

If your looking for the SQLLocalDB package to install:

x64/SQLLocalDB.MSI for 64 bit systems (33.0MB)
x86/SQLLocalDB.MSI for 32 bit systems (27.7 MB)







 

Unit Testing Fluent NHibernate

Summary

I’ve covered a little unit testing of NHibernate in the past.  I showed how to use SQLite, which is the most common method of unit testing (technically, integration testing) an NHibernate application.  If you’re using MS SQL server for your application, SQLite has some serious limitations that could hinder your unit tests.  So I’m going to show you how to use Microsoft’s LocalDB.


The Problems with SQLite

One of the first issues I discovered with SQLite is that it doesn’t do multiple databases.  Simulating a multi-database join in a unit test is not workable with SQLite.  The second and most annoying “feature” of SQLite is that it doesn’t behave well with a command line MSTest.  There are work-arounds, including the fact that you have to create a test settings file and point your script to that file to make MSTest work with SQLite unit tests.  Other issues surround the 32bit/64bit dll’s and the difficulty in making it all work on Teamcity.


Microsoft SQL Express and LocalDB

Apparently, Microsoft came up with a solution to SQLite, called LocalDB.  It’s a version of SQL Express that is lightweight and fully-featured.  The 64-bit install is only 33MB in size and there is no special driver for NHibernate.  If you are going to develop for MS SQL server, then the driver is the same for LocalDB.

Here’s the first document I stumbled across when I was looking for this product: Introducing LocalDB, an improved SQL Express.  In order to get things rolling quick, you can go to this article: Getting Started with SQL Server 2012 Express LocalDB.


The Sample Project

So I put together a project based on my previous example with a Student, Teacher and Class table (I renamed “Class” into “SchoolClass” to prevent a clash with the keyword “Class”).  I added a test project onto the solution and created a special context for the purpose of my unit tests.  Here’s how the code looks:

public class TestSessionFactory
{
    private static ISessionFactory SessionFactory
    {
        get
        {
            return Fluently.Configure()
            .Database(MsSqlConfiguration.MsSql2005
            .ConnectionString(“Server=(localdb)\testinstance;Integrated Security=True”))
            .Mappings(m => m.FluentMappings.Add<SchoolClassMap>())
            .Mappings(m => m.FluentMappings.Add<TeacherMap>())
            .Mappings(m => m.FluentMappings.Add<StudentMap>())
            .ExposeConfiguration(cfg => new SchemaExport(cfg).Create(true, true))
            .BuildSessionFactory();
        }
    }
    public static ISession OpenSession()
    {
        return SessionFactory.OpenSession();
    }

}


The first thing you’ll notice is that I was able to use the “Add” for the fluent mappings.  SQLite doesn’t allow this.  This can be handy if you have duplicate table names in separate databases, but create your class and mappings in one project.  You have fine-grain control over which tables will appear in your context.

The second thing to note is that you can tell the session factory to generate a schema when it starts.  If you were to use this session factory in your unit tests as-is, you’ll get nothing.  That’s because Fluent NHibernate will not create the initial databases.  For that, I wrote another method:

public static void Create(string databaseName, string instanceConnection)
{
    string databaseDirectory = Directory.GetCurrentDirectory();

    SqlConnection db;
    db = new SqlConnection(“server=” + instanceConnection + “;” +
                            “Trusted_Connection=yes;” +
                            “database=master; ” +
                            “Integrated Security=true; ” +
                            “connection timeout=30“);

    db.Open();

    SqlCommand myCommand = new SqlCommand(@”CREATE DATABASE [” + databaseName + @”]
        CONTAINMENT = NONE
        ON  PRIMARY 
        ( NAME = N’” + databaseName + @”‘, FILENAME = N’” + databaseDirectory + @”” + databaseName +
                                                @”.mdf’ , SIZE = 3072KB , FILEGROWTH = 1024KB )
        LOG ON 
        ( NAME = N’” + databaseName + @”_log’, FILENAME = N’” + databaseDirectory + @”” + databaseName +
                                                @”_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)
        “, db);

    myCommand.ExecuteNonQuery();
    db.Close();

}

This method is contained in the “TestDatabase” class along with a “Remove” method to undo the database creation.  I generated the database create command from the SQL Server management console.  I made the application execute directory (bin directory) the default location for the database files to be created, using the Directory.GetCurrentDirectory() method.


The Unit Tests

The unit tests have an initialize and a cleanup method.  The Initialize will setup the LocalDB instance and create a database.  The cleanup reverses the operation by dropping the database first, then shutting down the instance and deleting it.  

The ms test initialize method looks like this:

[TestInitialize]
public void Initialize()
{
    // … removed code not discussed

    // create a new localdb sql server instance
    startInfo = new ProcessStartInfo
        {
            WindowStyle = ProcessWindowStyle.Hidden,
            FileName = “cmd.exe“,
            Arguments = “/c sqllocaldb create “testinstance” -s
        };

    process = new Process { StartInfo = startInfo };
    process.Start();
    process.WaitForExit();

    TestDatabase.Create(“facultydata“, “(localdb)\testinstance“);
    TestDatabase.Create(“studentdata“, “(localdb)\testinstance“);

}


I used the Process class to execute a command line prompt to kick-off the LocalDB instance.  I named this instance “testinstance”.  That is used inside the context when Fluent NHibernate connects to the database.  It can also be used by the SQL Server management console to connect and troubleshoot problems.  Use the “(localdb)testinstance” syntax to connect to the database.


The unit tests look like this:

[TestMethod]
public void ThirdTest()
{
    using (ISession db = TestSessionFactory.OpenSession())
    {
        Teacher teacher = new Teacher
        {
            Name = “test teacher
        };

        db.Save(teacher);

        teacher = new Teacher
        {
            Name = “test teacher2
        };

        db.Save(teacher);

        var query = (from t in db.Query<Teacher>() select t).ToList();

        Assert.AreEqual(2, query.Count);
    }

}


You can download the sample code here:

SQLServerLocalDBTest.zip