Knowledgebase

To create high quality LOB applications unit tests and integration tests are mandatory. You want to run these integration tests and unit tests on your continuous integration server. The problem with integration tests or testing things like stored procedures you want your database filled with consistent test data and you don’t want multiple tests ore multiple builds have impact on each other. To prevent this, you can run a SQL script that deploys a new database for each test, creating a unique database with test data and after running the tests delete this database. This is a whole lot of work and it can be simplified and improving performance by using a LocalDb file, instead of a SQL Server test instance.

LocalDb benefits

– No SQL Server installation for running integration tests or testing stored procedures, on your local dev machine or on the continuous build server.

– Improved performance for tests (uses share memory, instead of TCP / IP to connect to database).

– Increase in speed of development.

– No code (T-SQL, EF etc.) changes, because LocalDb contains the same functionality as the full blown SQL Server (same code base).

– No configuration

– Supports AttachDBFileName, this means you can automatically copy the LocalDb (containing you test data) to your test output folder, by using the standard [DeploymentItems] and then let the integration tests use this file to connect to.

– It’s FREE

– Works on >= Visual Studio 2010

LocalDb disadvantages

– Requires .NET 4.0.2

Instructions for VS11

To show the usage of the Microsoft SQL Server 2012 LocalDb file in a Microsoft Visual Studio test project, you can use the following instructions:

Create a Microsoft Visual Studio Solution

Create a resources project, containing the LocalDb file

Add a Microsoft SQL Server 2012 LocalDb file

Add > New Item… > Visual C# Items > Data > Service-based Database:

Just click Next > Next > Finish:

Remove the dataset: LocalDbDemoDataSet.xsd

Add a Unit test project, containing tests, that connect to the LocalDb file

Add a *.testsetting file to you solution

Enable DeploymentItems and add LocalDb to deployment items

This will copy the LocalDbDemo.mdf and ldf file to the Microsoft Visual Studio output folder. So the only code that can interfere with your test data and test database, are your own tests. If you want a clean database not for each test run, but foreach test, then you can use the DeploymentItem attribute on your test or test class. 

Add a unit test class [LocalDbTester] to the test project

By using the [AttachDbFilename] property in your connection string, the test will automatically attach the *.mdf and *.ldf file to the LocalDb engine. Note: without specifying the Initial Catalog or Database property, the name of the database will include the full path to the *.mdf file, in most cases this is not what you want.

Final solution setup, should look like:

Run the test

The output is a succeeded test, demonstrating connecting to the LocalDb *.mdf file, within a Microsoft Visual Studio 11 test project.

using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace LocalDbDemo.Test
{
    [TestClass]
    public class LocalDbTester
    {
        [TestMethod]
        public void ShowLocalDbData()
        {
            string mdfFilename = "LocalDbDemo.mdf";
            string outputFolder = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location);
            string attachDbFilename = Path.Combine(outputFolder, mdfFilename);
            string connectionString = string.Format(@"Data Source=(LocalDB)\v11.0;Initial Catalog=LocalDbDemo;AttachDbFilename=""{0}"";Integrated Security=True", attachDbFilename);
            using (var connection = new SqlConnection(connectionString))
            using (var command = new SqlCommand("select * from Person", connection))
            using (var adapter = new SqlDataAdapter(command))
            {
                var table = new DataTable();
                int count = adapter.Fill(table);
                Assert.IsTrue(count > 0);
            }
        }
    }
}

Instructions for VS2010

The instructions for VS11, also apply for Microsoft Visual Studio 2010, but there are some prerequisites:

– Microsoft Visual Studio 2010 SP1, should be installed (http://www.microsoft.com/download/en/details.aspx?id=23691).

– Microsoft .NET 4.0.2 design-time update (for developer machine http://www.microsoft.com/download/en/details.aspx?id=27759).

– Microsoft .NET 4.0.2 run-time update (for machines that do not contain Microsoft Visual Studio 2010 http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=27756).

– SqlLocalDb.msi (http://msdn.microsoft.com/en-us/evalcenter/hh230763):

Some extra instructions for Visual Studio 2010

Creating a LocalDb file in C:\Users\<UserAccountName>…, use the Server Explorer:

A LocalDbDemo.mdf and LocalDbDemo.ldf file will be created in the folder C:\Users\<UserAccountName>…

These files can now be copied and included in the LocalDbDemo.Resource project.

Adding a new LocalDb file by using the Add New Item > Data > Service-based Database, will not work in VS2010:

Managing the localdb (creating tables, stored procedures etc.), should be done by using Microsoft SQL Server 2012 Management studio. Just connect to (localdb\V11.0) and then attach the created localdb file.

End results in Microsoft Visual 2010

This screen dump shows the use of a LocalDb.mdf file in a Microsoft Visual Studio 2010 unit test.

So using Microsoft SQL Server 2012 LocalDb in a Microsoft Visual Studio 2010 unit test works.

Notes

One of the big disadvantages of using Microsoft Visual Studio 2010 instead of Microsoft Visual Studio 11, is that the LocalDb.mdf file can’t be edited / designed by using Microsoft Visual Studio 2010 directly, you must use Microsoft SQL Server 2012 Management Studio to edit / design the database.

If you encounter attach error, because the localdb is already attached, read my other post on changing the database file paths of an online localdb databaes (http://www.roelvanlisdonk.nl/?p=2636).

More information on LocalDb:

http://channel9.msdn.com/posts/SQL11UPD03-REC-07

http://channel9.msdn.com/posts/SQL11UPD04-REC-02